Simple explanations

If you don't know your interest rate (IRR), but only have your APR, there is a way of figuring out the actual interest rate on Excel.

For this, you need to know your EMI (monthly payment), duration of the loan (number of months) and principal (amount you borrowed).

Let's assume your EMI is 2,000 and you are paying over 5 years (60 months) on a loan of 100,000. Use Excel's RATE function. In this example:

=RATE(60, 2000, -100000) * 12 = 7.42%

I multiplied by 12 to convert the monthly interest rate to annual. Since the payment is in months (60 months), Excel returns the interest rate in months as well.

Note that this method does not require the APR. Just the EMI, duration and principal will suffice.

When I moved to the UK, I was surprised to see mortgages advertised for 4.9%. ICICI Bank's HiSAVE account was offering 5.15% interest on savings. So if I borrowed at 4.9% and invested at 5.15%, I can make money for nothing!

The catch, of course, is that the mortgage was 4.9% APR. Annual Percentage Rate is the total interest you pay on the initial amount you borrow, divided by the number of years. This has nothing to do with the Internal Rate of Return, or the regular interest rate we know of.

APR is supposed to make it easy to compare loans by including the upfront fixed costs. Personally, I still prefer the IRR calculation.

Here's an example. Say you take a 10-year loan for 100,000 at an interest rate (IRR) of 10%, paid annually. Say in the first year you repay 10,000 of that 100,000. But you'd also have to repay the interest: 10% of 100,000, which is 10,000. So your first year payment is 10,000 + 10,000 = 20,000.

Next year, you repay another 10,000 of the loan, plus interest. But the interest is now on 90,000, since you already repaid 10,000. So your payment is 19,000. The next year, it goes down to 18,000, and so on until in the last year, you have a balance of 10,000, which you pay back with 1,000 interest.

Year	Principal	You repay	Interest	Total
1	100000	10000	10000	20000
2	90000	10000	9000	19000
3	80000	10000	8000	18000
4	70000	10000	7000	17000
5	60000	10000	6000	16000
6	50000	10000	5000	15000
7	40000	10000	4000	14000
8	30000	10000	3000	13000
9	20000	10000	2000	12000
10	10000	10000	1000	11000
Total	-	-	55000	155000

This means you're paying an interest of 55,000 across 10 years, on a loan of 100,000. So your annual percentage rate (APR) is 5.5%. Get it?

So really, you're not paying an interest of 5.5%. You're paying 10%. But because you're paying back the loan, your interest amount comes down. The APR makes it look like you're paying less.

As a rule of thumb, the real interest rate is a little less than twice the APR.

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

I've seen many proofs that 1=2. Here's a classic.

The (not-so-subtle) error in the above proof is that we're cancelling (a-b) on both sides, when (a-b) equals zero. That is, we're dividing by zero on both sides. That completely invalidates the equality.

Another proof uses the fact that the square root of a number can be both positive or negative.

(Proving -1=1 is the same as proving 1=2. Once you have one wrong proof, you can prove every other falsehood.)

The flaw here is that the square root of 1 is 1 and -1. So right after the square root symbol appears, every equation should have a plus-or-minus symbol on both sides.

The most convincing proof uses absolutely convergent series as the key idea. Here's how the proof goes.

Most people initially think that the flaw is in the re-arrangement of the series. That's not true! The re-arrangement works just fine, and you can prove that every term is correct to infinity.

The flaw is subtler.

When an infinite series is summed, it can be summed in any order. But the total may vary depending on the order you sum it up! You are guaranteed that the total is the same only if the series is absolutely convergent. That is, if the sum of the absolute values of each number is finite. (See the Wikipedia article on the Riemann series theorem.)

For the log 2 series, it's not absolutely convergent. The series diverges, as shown below:

So, by re-arranging the series for log 2, we've invalidated the equality anyway.

This fact once saved an entire class. We had a problem in our first year physics course to which the answer was the series above. (It had to do with calculating the electromagnetic potential created by an array of charges.) Since the series is not absolutely convergent, and every possible answer was correct, the whole class got marks for this question, as long as they attempted it.

Recently, I was helping a bank define Basel 2 requirements.

For every dollar a bank lends, at least 8 cents should come from its own pocket, and the rest from its depositors. But a risky $1 loan may be like a $1.5 loan, whereas a $1 Government loan may be like a $0.5 loan. This is the "risk-weighted asset" (RWA) value. Basel 2 says 8% of risk-weighted assets should come from the bank's pocket.

I was trying to convince the people who were maintaining the leasing software that the RWA of a lease is the NPV of its future cash flows, and they had a whole lot of questions.

"What is this NPV?"

You can put 90 cents in the bank today at 11% and get $1 next year. So $1 next year is worth 90 cents today. When a customer pays $100 over the next 10 months, it's worth less than $1000 today. That's the NPV. The NPV is what you put in the bank today to get that cash flow: $100 over the next 10 months

"So why should we use NPV for leases?"

That's because when a lease is cancelled, the closure payment is the NPV. If you take a lease for 10 months at $100 a month, this includes the interest. If you terminate the lease after 5 months, you won't pay $500 for the remaining 5 months. You'll pay less -- the NPV of those $100 for 5 months. So there is some logic to using NPV as the RWA.

"OK, so how do we calculate the NPV?"

You divide each cash flow by (1 + r)^n, where r is the internal rate of return, and n is the number of years. Then you add them up. You'll get a number less than the sum of cash flows.

"And how do we calculate this IRR?"

(sheepishly) The IRR is that interest rate for which the NPV is zero.

And we got stuck here, because their software didn't have an IRR function, and the definitions for IRR and NPV are circular.

To do this in Excel is simple. Just enter the cash flow values. So, if on a loan of $500, you paid $100 for 6 months, and use the IRR function, as shown below. Your monthly IRR is 5.47%.

But we needed their AS/400 system to do it as well, and it didn't have the IRR function.

After a few weeks of digging around, I found a paper that said you can calculate the IRR iteratively. Let

• npv be the NPV given an IRR and cash flows
• sum be the sum of cash flows
• p be the principal amount

Then irr = irr * log(p/sum) / log(npv/sum) is the iteration you need to successively apply.

We decided to start with 1.85 times the stated interest rate (which was a pretty good guess for most leases), and kept applying this formula until it stays more or less the same. Worked like a charm.

Here's the spreadsheet with the calculation.

| alternate titles: IRR calculation How to calculate IRR How to calculate IRR manually Calculating IRR manually