Question

A thirty year monthly payment mortgage loan for 500,000 is offered at a nominal rate of 8.4% convertible monthly. Find the a) monthly payment, b) the total principal and interest that would be paid on the loan over 30 years c) the balance in 5 years and d) the principal and interest paid over the first 5 years.

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

E20 . -NMON Loan Amount Annual interest rate Monthly interest rate Term (years) No. monthly repayment $500,000.00 8.40% 0.70% 30 360 8 a. Monthly payment $3,809.19 10 11 12 b. Total interest paid over 30 years Total principal paid over 30 years Total Amount repaid $871,307.79 $500,000.00 $1,371,307.79 13 14 c. Balance at end of 5th year $477,043.37 16 17 d. Interest paid in first 5 year Principal paid in first 5 year Total Amount paid in first 5 year $205,594.67 $22,956.63 $228,551.30

Cell reference -

B Loan Amount Annual interest rate Monthly interest rate Term (years) No. monthly repayment 500000 0.084 1=C3/12 30 | |=C5*12 | Monthly payment =PMT(C4,C6,-C2,,0) 10 b. Total interest paid over 30 years Total principal paid over 30 years Total Amount repaid --CUMIPMT(C4,C6,C2,1,360,0) =C2 =SUM(C10:C11) 13 C. Balance at end of 5th year =C2+CUMPRINC(C4,C6, C2,1,60,0) 14 15 16 d. 17 Interest paid in first 5 year Principal paid in first 5 year Total Amount paid in first 5 year --CUMIPMT(C4,C6, C2,1,60,0) --CUMPRINC(C4,C6,C2,1,60,0) =SUM(C16:C17) 18 19 20

Hope this will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.