IRR calculation

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.