Your success in business thus far h as put you in a position to purchase a home for $500,000 located university you attend. You plan to pay a 20% down payment of $ 100,000 and borrow the remaining $400,000. You need to decide on a mortgage, and realize you can apply the skills you have acquired in the last several chapters to evaluate your choices. To find the available options, go to ww bankrate.com. Select "Mortgages," then "Mortgage Rates." For location, ch city: for mortgage type, choose purchase (not refinance), Consider 30-year fixed rate mortgages with 20% down, and assume your credit score is the highest poss choose the nearest large Consider all the options by selecting loans with "All points." Update rates and sort by "Rate to find the loan with the lowest rate (not APR). Record the rate, points, fees, "APR," and monthly payment for this loan Next consider only loans with "O points" and find the loan with the lowest fees. Again, record the First, use the annuity formula or PMIT function in Excel to verify the monthly payment for cach Next, calculate the actual amount you will receive from each loan after both fees and p rate, points, fees, "APR," and monthly payment for this loan loan. (Note that to convert the "Rate" to a monthly interest rate you must divide by 12. Your may differ slightly due to rounding.) (Note that fees are a fixed dollar amount points are also paid up front and are calculated as a % the loan amount.) Using this net amount as the amount you will receive (rather than $400,000) show that the quoted "APR" of the loan is the effective IRR of the loan once all fees are included (you may use the Rate function in Excel, or calculate the NPV at the quoted "APR") 1. Compute the incremental cash flows of the lower rate loan; that is, determine how much more 2. What is the payback period of the lower rate loan? That is, how many years of lower monthly 3. What is the IRR associated with paying the higher fees for the lower rate loan? (Again, the RATE Compare the loans, assuming you will keep them for 30 years, as follows: you will pay in fees, and how much you will save on your monthly payment. payments will it take to save an amount equal to the higher fees? function can be used Plot the NPV profile of the decision to pay points for the lower rate loan. Do the NPV rule and the IRR rule coincide? Next, compare the loans assuming you expect to keep them for only 5 years: 5. Compute the final payment you will need to make to pay off each loan at the end of 5 years 6. Including the incremental cost to repay the loan after 5 years, what is the IRR and NPV profile different discount rtes (0% to the IRR in (3) above). What can you conclude about whether it is a good receive $400,000 today afer all fees and points are paid. How would this affect your decision to pay points? (Hint: the FV function in Excel can be used). Which loan will be more expensive to repay? associated with paying points no Create a data table showing the NPV of paying points for different horizons (1 to 30 years) and to pay points? Suppose the bank gives you the option to increase either loan amount so that for either loan, you will Note: Updates to this data case may be found at www.berkdemarzo.com Your success in business thus far h as put you in a position to purchase a home for $500,000 located university you attend. You plan to pay a 20% down payment of $ 100,000 and borrow the remaining $400,000. You need to decide on a mortgage, and realize you can apply the skills you have acquired in the last several chapters to evaluate your choices. To find the available options, go to ww bankrate.com. Select "Mortgages," then "Mortgage Rates." For location, ch city: for mortgage type, choose purchase (not refinance), Consider 30-year fixed rate mortgages with 20% down, and assume your credit score is the highest poss choose the nearest large Consider all the options by selecting loans with "All points." Update rates and sort by "Rate to find the loan with the lowest rate (not APR). Record the rate, points, fees, "APR," and monthly payment for this loan Next consider only loans with "O points" and find the loan with the lowest fees. Again, record the First, use the annuity formula or PMIT function in Excel to verify the monthly payment for cach Next, calculate the actual amount you will receive from each loan after both fees and p rate, points, fees, "APR," and monthly payment for this loan loan. (Note that to convert the "Rate" to a monthly interest rate you must divide by 12. Your may differ slightly due to rounding.) (Note that fees are a fixed dollar amount points are also paid up front and are calculated as a % the loan amount.) Using this net amount as the amount you will receive (rather than $400,000) show that the quoted "APR" of the loan is the effective IRR of the loan once all fees are included (you may use the Rate function in Excel, or calculate the NPV at the quoted "APR") 1. Compute the incremental cash flows of the lower rate loan; that is, determine how much more 2. What is the payback period of the lower rate loan? That is, how many years of lower monthly 3. What is the IRR associated with paying the higher fees for the lower rate loan? (Again, the RATE Compare the loans, assuming you will keep them for 30 years, as follows: you will pay in fees, and how much you will save on your monthly payment. payments will it take to save an amount equal to the higher fees? function can be used Plot the NPV profile of the decision to pay points for the lower rate loan. Do the NPV rule and the IRR rule coincide? Next, compare the loans assuming you expect to keep them for only 5 years: 5. Compute the final payment you will need to make to pay off each loan at the end of 5 years 6. Including the incremental cost to repay the loan after 5 years, what is the IRR and NPV profile different discount rtes (0% to the IRR in (3) above). What can you conclude about whether it is a good receive $400,000 today afer all fees and points are paid. How would this affect your decision to pay points? (Hint: the FV function in Excel can be used). Which loan will be more expensive to repay? associated with paying points no Create a data table showing the NPV of paying points for different horizons (1 to 30 years) and to pay points? Suppose the bank gives you the option to increase either loan amount so that for either loan, you will Note: Updates to this data case may be found at www.berkdemarzo.com