Mortgage Analysis You are planning to purchase a house that costs $600,000. You plan to put 20% down and borrow the remainder. Based on your credit score, you believe that you will pay 4.5% on a 30-year mortgage. 1. Use function "PMT" to calculate your mortgage payment. 2. Use function "PV" to calculate the loan amount given a payment of $2,000 per month. What is the most that you can borrow? 3. Use function "RATE" to calculate the interest rate given a payment of $2,200 and a loan amount of $500,000 4. For each scenario, calculate the total interest that you will have paid once the mortgage is paid off. (There is not a function for this, enter the formula into the cell.) 5. For each scenario, calculate the total cost of the home purchase. (Down payment plus principle (loan amount) plus interest.) 6. Assume that you plan to pay an extra $200 per month on top of your mortgage payment, calculate how long it will take you to pay off the loan given the higher payment. (Use corresponding interest rate in questions 1, 2, and 3). Calculate how much interest you will pay in total? Compare this to the value that you calculated for #4.

Cost of the house = $600,000

Down payment = 20% of cost of house or 20% of $600,000 = $120,000

Therefore, loan amount = Cost of the house - Down payment

= $600,000 - $120,000 = $480,000

We can use present value (PV) of an Annuity formula to calculate the equal monthly payment of mortgage loan

PV = PMT * [1-(1+i) ^-n)]/i

Where PV of mortgage loan = $480,000

PMT = Monthly payment =?

n = N = number of payments = 30 years *12 months =360 month

i = I/Y = 4.5% per year, therefore monthly interest rate = 4.5%/12 = 0.375% per month

Therefore,

$480,000 = PMT* [1- (1+0.375%)^-360]/0.375%

= $2,432.09

Monthly payment of principal and interest is $2,432.09 for this mortgage loan

Total interest payment = Total mortgage payment over the course of loan – PV of mortgage loan

= Monthly payment * number of months – PV of mortgage loan

= $2,432.09* 360 - $480,000

= $875,552.22 -$480,000

= $395,552.22

Now $200 of extra payment will make new monthly payment = PMT + $200

= $2,432.09 + $200 = $2,632.09

Now by assuming new PMT, we have to calculate new number of payments (n) in following manner -

PV = PMT * [1-(1+i) ^-n)]/i

Where PV of mortgage loan = $480,000

PMT = Monthly payment = $2,632.09

n = N = number of payments =?

i = I/Y = 4.5% per year, therefore monthly interest rate = 4.5%/12 = 0.375% per month

Therefore,

$480,000 = $2,632.09 * [1- (1+0.375%)^-n]/0.375%

Or $480,000/ $2,632.09 *0.375% = [1- (1+0.375%) ^-n]

Or 1 – ($480,000/ $2,632.09)*0.375% = (1+0.375%) ^-n

Or 0.3161 = 1.00375^-n

Taking natural log from both sides

Ln (0.3161) = -n Ln (1.00375)

n =- Ln (0.3161) / Ln (1.00375)            [use the natural log function either in excel or in calculator]

= 1.1516/0.0037

Or n = 307.67 months or 307.67/12 = 25.64 years

Total interest payment in this case = Total mortgage payment over the course of loan – PV of mortgage loan

= Monthly payment * number of months – PV of mortgage loan

= $2,632.09* 307.67 - $480,000

= $809,807.03 -$480,000

= $329,807.03 (interest payment will decreased by $65,745.18)