question archive Mortgage Analysis You are planning to purchase a house that costs $600,000
Subject:FinancePrice:4.86 Bought33
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)