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.
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.)