Loan repayment for year in Excel
For example, I had taken a loan for $1,50,000 @ 3% pa ROI with a period of 15 years. The first installment of the loan was paid in Nov 2015. I want to find out how much of the loan payment that I made in 2016 has gone towards the principal.
To do it in Excel, here is the answer:
a) Enter the formula =CUMPRINC(J3/12,J4*12,J2, J8, J9, 0) where J2 corresponds to "Debt Amount", J3 corresponds to Rate of Interest per annum and J4 corresponds to the loan period in years.
J8 and J9 corresponds to the installment period for Jan 2016 and Dec 2016. The first installment (monthly) was in Nov 2015. Hence Jan 2016 and Dec 2016 correspond to 3rd and 14th installments.
The last argument "0" used defines when installments are due: 0 = end of period. 1 = beginning of period.
As you can see from screenshot, Yearly repayment for the loan in 2016 is $1'035.87*12 = $12'430.44. Of this amount, $8'080.68, goes towards principal and the rest towards interest for the loan.