Question

What is the monthly payment for a $800,000 mortgage for the first 119 payments that is due in 10 years, has a 25 year amortization, at 5% interest? What is the amount of the 120th payment?

Please use Excel and explain. Make sure that formulas are set to show in excel sheet.

Answer 1

Mortgage Amount = $ 800000, Interest Rate = 5 % per annum or (5/12) = 0.4167 % per month, Amortization Tenure = 25 years or (25 x 12) =300 months, Repayment Tenure = 10 years or (12 x 10) = 120 months (a different amrotization and repayment tenure implies that all monthly payments except the last one are determined considering repayment equal to amortization. The principal outstanding is paid along with the last monthly payment)

Let the monthly repayments (considering 25 years as the repayment period) be $ K

Therefore, 800000 = K x (1/0.004167) x [1-{1/(1.004167)^(300)}]

800000 = K x 171.0532278

K = 800000 / 171.0532278 = $ 4676.90677

First 119 Payments = $ 4676.90677

Principal Outstanding at the end of Year 10 = PV of Remaining Payments assuming repayment period equals amortization period = 4676.90677 x (1/0.004167) x [1-{1/(1.004167)^(180)}] = $ 591403.7981

Value of 120 Payment = 4676.90677 + 591403.7981 = $ 596080.7049 ~ $ 596080.71