Why is combined cashflow IRR less than sum of parts?

[Dave007]

I would like to calculate the combined IRR of the following:

a) $50 million principal indexed by 2% p.a. compounding over 10 years
b) $50 million principal with a coupon return of 8% p.a. no compounding over 10 years. Principal paid back at year 10.

The IRR for each of these is 2% ane 8%, so why isnt the combined IRR 10%? The xirr formula in excel calculates it to be 9.41%. See calculation set out below.

Thanks in advance for your help / explanation.

	0	1	2	3	4	5	6	7	8	9	10
	1/01/2014	31/12/2014	31/12/2015	31/12/2016	31/12/2017	31/12/2018	31/12/2019	31/12/2020	31/12/2021	31/12/2022	31/12/2023
Inflation	2.0%
Principal Inflation	50,000,000	51,000,000	52,020,000	53,060,400	54,121,608	55,204,040	56,308,121	57,434,283	58,582,969	59,754,628	60,949,721
Cashflows	- 50,000,000										60,949,721
	2.00%
Coupon	8.0%	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000
Cashflows	- 50,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	54,000,000
	8.00%
Total Cashflows	- 50,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	4,000,000	64,949,721
Total Return	9.41%

 

[tkhunny]

1) Why on Earth would you expect it to be simply additive? That makes no sense at all. It would be averaged, if anything. The combined result should be between 2% and 8%. Naive: (.02 + .08)/2 = 0.05 or by total cash flow (11(0.02) + 40(0.08))/51 = 6.71. The direct calculation should be TOO far from those.

2) Why are half your larger cashflows missing from the beginning and the end in your combined data?

t = 0	(100,000,000)
t = 1	4,000,000
2	4,000,000
3	4,000,000
4	4,000,000
5	4,000,000
6	4,000,000
7	4,000,000
8	4,000,000
9	4,000,000
10	114,949,721

 

[Dave007]

Thanks for your help!
Sorry I was not clear enough - half of the cash flows are missing because its the same principal investment. The same $50 million will be both indexed and will also receive a coupon.

This is why I expected it to be additive, because 2% plus 8% IRR are both being returned on the same $50 mil investment. And when the IRR is done exclusively, it is 2 and 8, even though the 8 is not compounding.

 

[tkhunny]

I see. Here's a plan -- two things...

1) Timing is part of the problem. Do a little test. Delete the first 9 coupons, leaving all the cash flows in the same periods. This should be closer to simply additiive, but,...

2) You're still using the $50MM twice in the individual calculations but only once in the combined calculation. That just doesn't quite make sense to me.

At the moment, I'm not seeing the problem. My gut says it's your expectation, but I can't quite find a way to talk you out of it.

 

[tkhunny]

I have it. It is the timing AND the mixture of interest methodologies. Make it a one-period investment and it will work like you expect.

Simple and Compound Interest are EXACTLY the same over a period of EXACTLY one (1) Year. ONLY at EXACTLY one (1) year can it be expected to be simply additive.

Anyway, that's my present thinking on it. Something else may hit me, later.

 

[Dave007]

Yes, I did the calculation over a 1 year period and you are right, it is additive.

Now I just need a way of explaining it clearly to my client!

Yes its got to do with timing and combination of the different style of cash-flows. But I am trying to think why is the result an exact 2 and 8 when calculating the methodologies stand alone, compared to combined. Is it because the compounding portion of the cashflow is back-ended, and therefore, diminishes the total return given you have a longer period to wait for it compared to the regular coupons you have in the same calc.

Thanks again for your help so far.