How to convert APR to interest rate

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.

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

Difference between interest rate and APR

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.

Absolutely convergent series

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

Proof that 1=2 using algebra

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.

Proof that 1=2 using square roots

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

Proof that 1=2 using non-absolutely convergent series

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:

log 2 is not absolutely convergent

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.

Calculating IRR

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.