How to calculate principal repayment

Answer: use the CUMPRINC function in Excel

Say you take a 10-year lease for 100,000 at an interest rate (IRR) of 10%, paid annually. The installment for this lease is 16,275. You can calculate this using the PMT function in Excel:

PMT(10%, 10, 100000) = -16275

You’ve made 5 payments over 5 years. At this point, if you decide you want to repay the full lease, how much do you have to repay? In other words, what’s the principal outstanding after 5 years?

This is not trivial calculation. The answer is not 50,000. In fact, it is 61,693. Here’s how it works.

Year Balance Principal Interest EMI
1 100000 6275 10000 16275
2 93725 6902 9373 16275
3 86823 7592 8682 16275
4 79231 8351 7923 16275
5 70880 9187 7088 16275
6 61693 10105 6169 16275
7 51588 11116 5159 16275
8 40472 12227 4047 16275
9 28245 13450 2825 16275
10 14795 14795 1480 16275

The EMI contains an interest component as well as a principal component. The interest component is always 10% of the balance — because the interest rate is 10%. The remaining amount is the principal repayment.

In the first year, you pay an interest of 10% x 100,000 = 10,000, and the remaining 6,275 (from your 16,275 EMI) is the principal repayment. This brings the balance down to 93,725.

The next year, you pay an interest of 10% x 93,725 = 9,373, and the remaining 6,902 (from your 16,275 EMI) is the principal repayment. This brings the balance down to 86,823. And so on..

So after 5 years, you just have to repay 61,693, the balance after 5 payments.

Excel has two functions: PPMT and IPMT that calculate the principal and interest components. For example:

PPMT(10%, 1, 10, -100000) = 6275 (principal payment in year 1)
IPMT(10%, 1, 10, -100000) = 10000 (interest payment in year 1)

Excel also has the cumulative versions of these functions: CUMIPMT and CUMPRINC. You can calculate the balance outstanding using the CUMPRINC function. For example:

CUMPRINC(10%, 10, 100000, 1, 5, 0) = -38307 (principal paid in first 5 years)

The balance outstanding is 100,000 – 38,307 = 61,693

As you saw, the balance you have to repay midway is usually more than half the amount you borrowed. This is because you spend most of the first half paying off the interest. The typical shape of the balance outstanding over time is below.

Balance outstanding in a lease, over time

The typical shape of the principal and interest component of the EMI over time is shown below.

Principal and interest components of an EMI, over time

While this may take customers by surprise, this has confused banks as well, and has an interesting side-effect, thanks to Basel 2. Most banks use the book value of the lease for risk calculations. This is typically based on a straight-line depreciation. So after 5 years, the lease is worth 50,000 in the books, and they would have to provide capital for that 50,000. But Basel 2 now says they need to provide for the principal outstanding, which is 61,693 — meaning banks have to provide more capital than they have been so far. (I wouldn’t be surprised if many banks don’t know this.)

  1. khais says:

    ICICI BANK OFFERS Rs. 200000 personal loan with 19.5% interest, they told emi Rs.3017 for 4 years (48 monthly installment vis). How should I calculate that how much interest rate I am giving? How to cross verify? Please suggest or give calculation. Thanks

  2. pronab halder says:

    I wanted to know how would i calculate the total amount,principle and interest

  3. ganesh says:

    # And how EMI is calulated when intrest rate changes but EMI reamins same (default choice given by all banks when intrest rate changes.) # How to calculate new tenure in above case.

  4. TAMMY says:

    There is no sample by # of months instead of # yrs: Please show me how to calculate monthly principal and interest for the following: Loan Amount $11,000.00 Rate 10% 64 months Thanks

  5. shanti kumar says:

    can i know a bit briefly how emi amount is decided mannual work out required explaining the formulae. i know multiply divide which you have mentioned in the table but what is the 1+r pls explain.

  6. Santhosh says:

    I know the interest rate,EMI,Loan amount. I have to repay the amount by 60 months. how can i calculate the actual interest and principal payment on each months.

  7. S Anand says:

    Santhosh, I suggest you have a look at the help for the IPMT and PPMT functions in Excel. That will do the job for you. (BTW, you’d only need the interest rate, loan amount and number of months. EMI can be calculated using the PMT function, or by adding IPMT + PPMT)

  8. Anand says:

    I know my EMI, the duration of the loan and the interest rate (not the IRR). Can i use any of these functions to determine how much principal i owe at a certain point in time ? if not do I have to determine the IRR and then plug it into these functions to find the value i am looking for ?

  9. S Anand says:

    Unfortunately, all the functions require the IRR. But given the EMI, duration and principal, you can determine the IRR using goal seek on the PMT function.

  10. Frankie says:

    how do i calculate NPV of Lease Payments in Excel given the following scenarios: Payment in arrears/advance(kindly articulate for both options, payment in arrears and due) and on a monthly basis, say current FMV is $100K,Interest rate=6%, Period is 60 months. I find your explanations marvellously simple….I think you should write a book on Excel for Finance or some such…thanks Sir.

  11. S Anand says:

    Frankie, the best way to do this is using the NPV function in Excel. It allows for any arbitrary cash flow patters.

  12. Vivian says:

    If I want to put the IPMT and PPMT function for the following, what would the formula look like?
    Loan $750,000, Annual Rate 7.50%, Years 5, Periods per year 4, Rate per Period 1.88%, Number of Periods 20, Period Payment (PMT) is ($45,316) I need to be able to calculate the Interest Payment. Then I need to find the PPMT function to calculate the principal payment for the current payment period. Please help!

  13. S Anand says:

    For the 11th period, it would be
    =IPMT(7.5%/4, 11, 20, 750000)
    =PPMT(7.5%/4, 11, 20, 750000)

  14. Divya says:

    Is the above process of calculation the same as used by banks in case of education loans?

  15. mythili says:

    this was very useful for me in doing project, thanks for those who done it

  16. G Prajeet says:

    Thank you for the wonderful calculation Sir

  17. K SUBRAMANYAM says:

    Dear sir,
    what is the formula for calculating the interest for a year if i know the loan amount(opening balance) ,rate of interest,emi,loan tenure in monthly rests

  18. Rajesh Jain says:

    Dear sir, i Have install amount, Rate of int and installment left details. How would i calculate the outs tanding principal. The solution is here
    But How? Can you kindly help

  19. n says:

    The formula PMT is correct but the parameter format is incorrect. The correct format for the given example is PMT((10%)/12,120,100000) = 1321.51