In: Finance
You plan to purchase an $100,000 house using a 15-year mortgage obtained from your local bank. The mortgage rate offered to you is 4.5 percent. You will make a down payment of 10 percent of the purchase price. a. Calculate your monthly payments on this mortgage. b. Calculate the amount of interest and, separately, principal paid in the 80th payment. c. Calculate the amount of interest and, separately, principal paid in the 110th payment. d. Calculate the amount of interest paid over the life of this mortgage
A. Monthly payment can be calculated using an excel formula as follows
Monthly Payment = PMT(4.5%/12,15*12,-90000,0,0) = $688.49
Here 4.5% is the interest rate, entered as monthly interest rate
15 is the years to maturity. Entered as months.
90000 is the loan principal. 90% of the House
B. Below is the amortization schedule of this loan calculated in excel.
2nd column is beginning balance, equal to ending balance is previous period.
3rd column is Principle Paid. Which is 688.49 - interest due in that period
4th column is interest due in that period, calculated as beginning balance*4.5%/12
5th column is ending balance = Beginning Balance - Principle Paid
Month | Beginning Balance | Principal Paid | Interest Paid | Ending Balance |
0 | 90,000.00 | |||
1 | 90,000.00 | 350.99 | 337.50 | 89,649.01 |
2 | 89,649.01 | 352.31 | 336.18 | 89,296.70 |
77 | 59,201.13 | 466.49 | 222.00 | 58,734.64 |
78 | 58,734.64 | 468.24 | 220.25 | 58,266.40 |
79 | 58,266.40 | 469.99 | 218.50 | 57,796.41 |
80 | 57,796.41 | 471.76 | 216.74 | 57,324.65 |
81 | 57,324.65 | 473.53 | 214.97 | 56,851.12 |
82 | 56,851.12 | 475.30 | 213.19 | 56,375.82 |
83 | 56,375.82 | 477.08 | 211.41 | 55,898.74 |
84 | 55,898.74 | 478.87 | 209.62 | 55,419.86 |
107 | 44,418.16 | 521.93 | 166.57 | 43,896.23 |
108 | 43,896.23 | 523.88 | 164.61 | 43,372.35 |
109 | 43,372.35 | 525.85 | 162.65 | 42,846.50 |
110 | 42,846.50 | 527.82 | 160.67 | 42,318.68 |
111 | 42,318.68 | 529.80 | 158.70 | 41,788.88 |
112 | 41,788.88 | 531.79 | 156.71 | 41,257.10 |
113 | 41,257.10 | 533.78 | 154.71 | 40,723.32 |
114 | 40,723.32 | 535.78 | 152.71 | 40,187.54 |
115 | 40,187.54 | 537.79 | 150.70 | 39,649.75 |
So from above table in the 80th payment, which is in month 80, Interest paid is $216.74 and principle paid is $417.76
C. From above table in the 110th payment, which is in month 110, Interest paid is $160.67 and principle paid is $527.82
D. Sum of 4th column in the above table is $33,928.91 which is total interest paid in 15 years.