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.

YearBalancePrincipalInterestEMI
110000062751000016275
2937256902937316275
3868237592868216275
4792318351792316275
5708809187708816275
66169310105616916275
75158811116515916275
84047212227404716275
92824513450282516275
101479514795148016275

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