In: Finance
Ann obtains a fully amortizing 30 year Fixed Rate Mortgage with monthly payments for $1,250,000 at 4.38%. What will be Ann’s mortgage balance after 20 years of payments (i.e. after 240 months)?
Ann obtains a fully amortizing 30-year Fixed Rate Mortgage with monthly payments for $1,250,000 at 4.38%. What fraction of Ann’s 40th payment goes to interest?
Solutions with financial calculation and excel please.
Step 1 ) Calculate the loan amount
We are given the following information:
Payment | PMT | 1250000.00 |
Rate of interest | r | 4.38% |
Number of years | n | 30.00 |
Monthly Compounding | frequency | 12.00 |
Loan amount | PV | To be calculated |
We need to solve the following equation to arrive at the required PV:
So the loan amount is 250,210,069.69
Excel Input =PV(0.0438/12,30*12,-1250000,0)
PMT is negative as you need to pay the same to pay off the loan and therefore it is an outflow.
Step 2) Develop the amortization schedule
Opening balance = previous year's closing balance
Closing balance = Opening balance-Principal repayment
PMT is calculated as per the above formula
Interest = 0.0438 /12 x opening balance
Principal repayment = PMT - Interest
Step 3) Closing balance or the outstanding balance after 240th
month as per above schedule is $121,287,473.47
Step 4) In the 40th payment interest portion is $861,853.87, this is 861853.87/1250000 = 0.6895 or 68.95% of the payment