In: Finance
1. Loan amount = $45,000, rate = 11%, term = 120 months
a) Monthly payment can be calculated using PMT function in excel. =-PMT(11%/12, 120, 45000) = $619.88
b) Loan amortization table is below
Payment | Opening balance | Interest payment | Principal repaid | Closing balance |
1 | 45,000 | 413 | 207 | 44,793 |
2 | 44,793 | 411 | 209 | 44,583 |
3 | 44,583 | 409 | 211 | 44,372 |
4 | 44,372 | 407 | 213 | 44,159 |
Opening balance (from month 2) = prior period closing balance
Interest payment = interest rate (11%) * opening balance
Principal repaid = monthly payment ($619.88) - interest payment
Closing balance = opening balance - principal repaid
c) Total interest paid can be calculated using CUMIPMT function in excel = -CUMIPMT(11%/12, 120, 45000, 1, 120, 0) = $29,385
d) Extending the loan amortization table to 27 months we get outstanding balance to be paid off = $38,680
Payment | Opening balance | Interest payment | Principal repaid | Closing balance |
27 | 38,943 | 357 | 263 | 38,680 |