Is It Appropriate to use Weighted Averages on IRRs?
 It is very common for businesses or investors to approve projects based on IRR. Coming from my Marakon background with a strict belief in NPV and very carefully calculating the proper discount rates, I was confused about why you’d accept whatever “discount rate” made the stream of cash flows result in a zero NPV. Here’s another strange thing about IRR: unlike most functions in Excel, IRR lets you input a guess for the function to work. Isn’t that weird in itself? I mean, when you input =SUM(A1:A5) you don’t have to also give a guess so Excel has a decent starting place. Excel does assume 10% as the guess if an input isn’t provided, but I’ve found that often using -90% (that’s right, negative) results in fewer errors than the default. Excel uses an iterative process to calculate IRRs and gives an error if it hasn’t converged on an answer after twenty iterations.
Still, compared to its cousins in decision-making approvals like Payback Period or Cash on Cash Multiple, IRR is relatively enlightened in its ability to account for highly variable cash flows over time.
Recently I was working with a fund which makes many relatively small investments, each of which naturally has its own cash flows. The managers wanted to report the expected IRR of the fund and wanted to calculate an average of the IRRs by investment, weighted by the size of the investment. I wasn’t sure if this would work, and so quickly threw together the example in Table 1:
| |
|
0 |
1 |
2 |
3 |
4 |
5 |
One Year,
10% IRR |
10% |
(1.00) |
1.10 |
- |
- |
- |
- |
Five Year,
20% IRR |
20% |
(1.00) |
- |
- |
- |
- |
2.49 |
| Total |
18% |
(2.00) |
1.10 |
- |
- |
- |
2.49 |
Here we have two investments. The first earns a 10% IRR over one year; the second 20% over five years. If we assume both invested the same amount of money we can just average them and not worry about the weighting. Obviously the simple average would be 15%, but the actual calculated IRR is 18%.
So what gives? First, it is possible to average IRRs, as Table 2 shows:
| |
|
0 |
1 |
| One Year,
20% IRR |
20% |
(1.00) |
1.20 |
| One Year,
10% IRR |
10% |
(1.00) |
1.10 |
| Total |
15% |
(2.00) |
2.30 |
| Wtd Avg |
15% |
|
|
It is also possible to weight IRRs based on the size of the investment as shown in Table 3:
| |
|
0 |
1 |
| One Year,
20% IRR |
20% |
(2.00) |
2.40 |
| One Year,
10% IRR |
10% |
(1.00) |
1.10 |
| Total |
16.67% |
(3.00) |
3.50 |
| Wtd Avg |
16.67% |
|
|
But the problem comes in as soon as the investments have different holding periods. Table 4 shows an example with different sized investments and different holding periods.
| |
|
0 |
1 |
2 |
3 |
4 |
5 |
One Year,
10% IRR |
10% |
(1.00) |
1.10 |
- |
- |
- |
- |
Five Year,
20% IRR |
20% |
(2.00) |
- |
- |
- |
- |
4.98 |
| Total |
19% |
(3.00) |
1.10 |
- |
- |
- |
4.98 |
Instead of the 15% simple average or the 16.67% weighted average, now we’re at 19%. What’s happening is that the IRR is being “weighted” not just by the investment amount, but also by the time funds are being invested. This is one way in which IRR obscures important facts about reality. For example, if you have two investments that are equally risky, and A returns 15% and B returns 20%, normally you’d expect to prefer B. But what if A lasts for two years and B only lasts for a month? You invest a dollar and get back about $1.02 in the case of B. Holding period matters greatly.
But returning to the question we started with, about the fund with different investment sizes that wanted to calculate a weighted average IRR. In the end we determined that since the holding periods were both long and nearly the same across investments that it was proper to average the IRRs together. That was a counter-intuitive result for me, but a welcome instance of seeing new flexibility from the complex calcs behind IRR.
|
|
|
More on IRR:
» |
Besides the traditional IRR function in Excel, don’t overlook XIRR and MIRR. XIRR allows cash flows to come in on an irregular schedule and MIRR relaxes the assumption that positive cash flows are reinvested at the final IRR.
|
| » |
Problems with the IRR in Excel – discusses both the mathematical conditions under which Excel bombs and certain theoretical issues with using IRR. |
|
