XIRR when periods less than a year

[mws4ua]

I've Googled and I've searched this site and I haven't found an answer that fully explains it to me. I was hoping somebody here could. (I'm waiting for the ah-hah! moment.)

I have a series of cash flows related to tax credits. You can 'buy' tax credits at $0.75 on the dollar, then receive the full credit in the following year's tax return. In my head, if I invest $75 today and you pay me $100 tomorrow, I made a 33% return on my money ($25 / $75).

XIRR does return 33% if I assume I invest $75 today and receive $100 in exactly one year, but the return is much higher if the date of the amount received is less than a year. In my head that almost makes sense, but I can't articulate it fully.

The actual numbers I'm using are below. As you can see, each amount invested is exactly 75% of the amount received. I have calculated the XIRR of each investment and tax credit individually, as well as XIRR of the entire stream. (The variation in the final XIRR is due to the leap year in 2020.)


[TABLE="class: outer_border, width: 350"]
<tbody>[TR]
[TD]Date[/TD]
[TD="align: right"]Amount[/TD]
[TD="align: center"]XIRR[/TD]
[TD="align: right"]XIRR Total[/TD]
[/TR]
[TR]
[TD]6/30/16[/TD]
[TD="align: right"]-1,590,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]57.42%[/TD]
[/TR]
[TR]
[TD]3/15/17[/TD]
[TD="align: right"]2,120,000[/TD]
[TD="align: right"]50.23%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]9/30/17[/TD]
[TD="align: right"]-530,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]3/15/18[/TD]
[TD="align: right"]706,667[/TD]
[TD="align: right"]88.24%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]9/30/18[/TD]
[TD="align: right"]-530,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]3/15/19[/TD]
[TD="align: right"]706,667[/TD]
[TD="align: right"]88.24%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/30/19[/TD]
[TD="align: right"]-530,000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]3/15/20[/TD]
[TD="align: right"]706,667[/TD]
[TD="align: right"]87.53%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

XIRR does return 33% if I assume I invest $75 today and receive $100 in exactly one year, but the return is much higher if the date of the amount received is less than a year. In my head that almost makes sense, but I can't articulate it fully.

Well, in the last investments you are getting the 33% after less than 6 months.

So, my simple question is, if you invest 75 and get 100 after 6 months and you reinvest the money for 6 months more, how much do you expect to have at the end of the year?

 

[joeu2004]

XIRR does return 33% if I assume I invest $75 today and receive $100 in exactly one year, but the return is much higher if the date of the amount received is less than a year. In my head that almost makes sense, but I can't articulate it fully.

It's called annualizing the rate. XIRR always returns an annual rate.

But since you are calculating returns for pairwise events, you do not need to use XIRR at all. And your result is more accurate, if you don't; but the difference is very relatively small.

For example, your first annual return could be calculated as follows:

=(B3/-B2)^(365/(A3-A2)) - 1

With that calculation, we do not need signed cash flows. So B2 could be positive, and the calculation becomes (B3/B2)^... .

Let's break that down. B3/-B2 is the total rate of return (plus 1). (B3/-B2)^(1/(A3-A2)) is the compounded daily rate of return (plus 1). Call that x. So x^365 is the annual(ized) rate of return (plus 1).

-----

I'm not sure I agree with your calculation of total XIRR, namely XIRR(B2:B9,A2:A9). As written, XIRR calculates an annual rate of return based on the date of the first cash flow (B2), effectively penalizing you for the time between pairwise cash flows. But it's unclear what you are trying to calculate, or what you should. I need more time to think this through. But my first inclination is to calculate one of the following.

Simple average annualized rate of return: =AVERAGE(C3:C9)

Compounded annualized rate of return: { =PRODUCT(1+C3:C9)^(1/COUNT(C3:C9)) - 1 }

Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

 

[joeu2004]

PS....

It's called annualizing the rate.

That is, it is the annual rate of return you could expect if your investment continued to gain (or lose) at the sub-annual rate for the remainder of the year.

 

[DRSteele]

Of course the return is much higher if the date of the amount received is less than a year --- that's how the time value of money is defined!

In function XIRR, all payments are discounted based on a 365-day year. Your payment of $1.59mm on June 30/16 and receipt of $2.12mm March 15/17 provide a return of 33% in 258 days, or 50.23% annualised. The algebraic expression for compound annualised growth rate is (New/Old)^(1/n)-1 or in our case (2.12/1.59)^(1/(258/365))-1 = 50.23%. XIRR amounts to a simple CAGR when there are only two payments.

For the entire stream, XIRR is correctly calculating the internal rate of return based on the payments, receipts and dates provided.

 

[joeu2004]

Second thoughts....

I have a series of cash flows related to tax credits. You can 'buy' tax credits at $0.75 on the dollar, then receive the full credit in the following year's tax return.

I doubt that you can "buy" tax credits at any time and get the same "return" (4/3). If that were the case, obviously you should "buy" the tax credits on Dec 31, the end of the tax year.

That said....

I'm not sure I agree with your calculation of total XIRR, namely XIRR(B2:B9,A2:A9). As written, XIRR calculates an annual rate of return based on the date of the first cash flow (B2), effectively penalizing you for the time between pairwise cash flows. But it's unclear what you are trying to calculate, or what you should.

Again, I do not believe XIRR(B2:B9,A2:A9) is a useful calculation in this case. That gives you the (average) rate of return for the series of investments over 4 years as if they were connected. Instead, I think you (should) want the average rate of return of the 4 independent investments.

If you want to use a compounded rate, I believe we want to find "r" such that:

Sigma(b*(1+r)^d, i=1,4) = Sigma(c, i=1,4)

where b is the unsigned "buy" amount (e.g. -B2), d is the days "invested" (e.g. A3-A2), c is the "credit" (e.g. B3), and r is the average daily rate.

I believe we must use Solver (or Goal Seek) to find "r" in E2 that causes the following formula in F2 to be zero:

=SUMPRODUCT(--(B2:B8<0), -B2:B8*(1+E2)^(A3:A9-A2:A8)) - SUMIF(B2:B9,">0")

Then the average annual return in D2 is: =(1+E2)^365 - 1. About 63.50%.

But it is much simpler if you use a simple rate, which I think is equally appropriate for this problem. Then we want to find "r" such that:

Sigma(b*(1+r*d), i=1,4) = Sigma(c, i=1,4)

r = Sigma(c-b, i=1,4) / Sigma(d*b, i=1,4)

Thus, E2 is the following formula:

=SUM(B2:B9) / SUMPRODUCT(--(B2:B8<0), -B2:B8*(A3:A9-A2:A8))

Then the average annual return in D2 is: =E2*365. About 57.34%.

The similarity to 57.42% returned from XIRR(B2:B9,A2:A9) is coincidental.

Note: If we use a simple rate for the average annual return, we should use a simple rate for each annualized return in column C. For example, C3 should be:

=365 * ((B3/-B2) - 1) / (A3-A2)

About 47.16%.

 

[mws4ua]

Thanks all for the help!

I doubt that you can "buy" tax credits at any time and get the same "return" (4/3). If that were the case, obviously you should "buy" the tax credits on Dec 31, the end of the tax year.

I don't know the significance of 6/30 or 9/30 for the purchase dates. I agree that 12/31 would be the optimal date for any cash outlay. Thank you for your detailed analysis, too.

 

[joeu2004]

I don't know the significance of 6/30 or 9/30 for the purchase dates. I agree that 12/31 would be the optimal date for any cash outlay. Thank you for your detailed analysis, too.

You're welcome! And thanks for the question. I had never heard of buying and selling tax credits. Now I've done some poking around, and I have a (very) rudimentary understanding.

To address my point about the timing of buying tax credit, I did find this explanation: "They are sold anywhere from 60 cents to 95 cents per credit depending on the time of year and the type of [tax] credit."

In the meantime, I conjectured that the amount of tax credit available might depend on when you buy. So if you waited until Dec 31, perhaps you could not buy as much tax credit as you need?

If so, it is not a simple linear proration, based on your example. I think it is just based on the availability of sellers; and it makes sense that the market would dry up as time goes by, since that shortens the availability of funds for the seller.

Oh well, I digress....

 

[joeu2004]

Errata re: "annualizing" (beating a dead horse)....

That is, it is the annual rate of return you could expect if your investment continued to gain (or lose) at the sub-annual rate for the remainder of the year.

For an entire year, not the "remainder".