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.

excel loan repayment for period


You can find similar Excel Questions and Answer hereunder

1) Keyboard shortcut for save as in Excel

2) How can I have text autocomplete by typing in a short code for the text?

3) I have to determine bonus for my staff based on ratings (for each rating, specific bonus amount). In Excel, how can I set up my Bonus Table?

4) I want to add a trend indicator symbol next to my sales data - how can I do that?

5) How can I find the day of the week given a date?

6) How can I clear cell after activating a routine when there is a change in value of a cell?

7) How do I calculate monthly payment given Loan amount, Rate of Interest and period?

8) I conducted a test and the test scores are available - how can I get Percentile rank of students?

9) Given a raw data Table, how can I find the value of a field for a specific value of another field?

10) I am using Excel for Project Management - how can I find End Date for a task given Start Date and Task duration considering only working days?


Here the previous and next chapter