Mahesh takes a five year car loan of Rs.1,00,000 where he has to pay annual payments at 10%p.a. Calculate his annual payment and also prepare a loan amortization schedule for the loan repayment.
Use PMT function in EXCEL to find the annual payment.
=PMT(rate,nper,pv,fv,type)
rate=10%
nper=5 years
pv=loan amount=100000
fv=0
=PMT(10%,5,-100000,0,0)=$26,379.75
Annual payment=$26,379.75
2. please find the amortization loan with formulas.
| Periods | Opening Balance | Annual fixed payment | Interest amount=(Opening Balance*10%) | Principal amount=Annual payment-Interest | Ending Balance=Opening Balance-Principal |
| 1 | 100000.00 | 26379.75 | 10000.00 | 16379.75 | 83620.25 |
| 2 | 83620.25 | 26379.75 | 8362.03 | 18017.72 | 65602.53 |
| 3 | 65602.53 | 26379.75 | 6560.25 | 19819.50 | 45783.03 |
| 4 | 45783.03 | 26379.75 | 4578.30 | 21801.44 | 23981.59 |
| 5 | 23981.59 | 26379.75 | 2398.16 | 23981.59 | 0.00 |
Get Answers For Free
Most questions answered within 1 hours.