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.
The typical shape of the principal and interest component of the EMI over time is shown below.
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.)
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
I wanted to know how would i calculate the total amount,principle and interest
# 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.
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
sir,
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.
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.
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)
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 ?
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.
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.
Frankie, the best way to do this is using the NPV function in Excel. It allows for any arbitrary cash flow patters.
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!
For the 11th period, it would be
=IPMT(7.5%/4, 11, 20, 750000)
=PPMT(7.5%/4, 11, 20, 750000)
Is the above process of calculation the same as used by banks in case of education loans?
http://www.hindu.com/pp/2009/05/09/stories/2009050984820600.htm
this was very useful for me in doing project, thanks for those who done it
Thank you for the wonderful calculation Sir
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
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 http://www.calcxml.com/calculators/loan-balance
But How? Can you kindly help
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