Loan repayment for period 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 1st installment has
gone towards the principal.
To do it in Excel, here is the answer:
a) Enter the formula =PPMT(D3/12,D8,D4*12,-D2) where D2 corresponds to "Debt Amount", D3 corresponds to Rate of Interest per annum and D4 corresponds to the loan period in years.
D8 is the period for which principal part of loan payment is to be computed (principal part of loan varies for every repayment period).
As you can see from screenshot, monthly repayment for the loan is $1'035.87. In the first installment (month 1), out of $1'035.87, $660.87 goes towards principal and the rest towards interest for the loan.