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.

excel loan repayment for year

 

You can find similar Excel Questions and Answer hereunder

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

2) Formula converts date to quarter and year in Excel

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 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?

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

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

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

8) How can I get the day number of the year for a date - for example 5 for Jan 5, 33 for Feb 2?

9) Keyboard shortcut for save as in Excel

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

 

Here the previous and next chapter