XIRR() producing accurate return?

[CutlerCJ]

Hello. I have a series of non-periodic cashflows, beginning with an investment (expressed as a negative), followed by returns (dividends, expressed as positives) and further investments, culminating in a final return. The net of the cashflows is positive.

The XIRR() function tells me that ROI is 1245%, and in trying to verify the accuracy of this figure, I played with the calculation range, shortening it to exclude the final large return -- yet Excel continues to produce a 1245% ROI. I further shortened the range, one transaction at a time, yet the ROI doesn't change.

In the attached sample, note the dates and values in columns B and C, and the XIRR() function at the bottom, displaying 1245%. Also notice that I've run the same formula in column D, using an absolute reference for the top row in order to calculate a separate ROI with each added transaction. Also please notice the mostly unchanged ROI's in D.

Finally, I replicated the series in columns G & H, but for the final 12/31/18 return, I have increased the value 1000-fold, from $248K to $248M, with no change to the ROI produced by the XIRR() function.

Am I doing something wrong? Is there a better function to use for such a series of cashflows?

Many thanks for any ideas!
Chris

[joeu2004]

The reason why the last (X)IRR is nearly the same, despite the difference of a factor of 1000 in the last cash flow, is because the PV of last cash flow is such a small contribution to the NPV.

For the data in B:C, the PV of the last cash flow is 9.36E-08 = C67/(1+C69)^((B67-B2)/365). For the data in G:H, the PV of the last cash flow is 9.36E-05. A difference of a factor of 1000, as expected. But an inifinitesimal number, in either case, relative to the initial investment of 6062.50.

But to answer your question ``Am I doing something wrong``, the short answer is: yes. Lots!!

First, if dividends are reinvested, their net cashflow is zero, namely: +div out, and -div in on the same day (or close enough).

Second and more significantly, the correct structure of an investment model for the IRR calculation is: initial investment or balance as the first "cash flow", signed as an inflow; followed by cash flows in and out of the investment; followed lastly by the current (ending) investment balance as the last "cash flow", signed as an outflow.

If each "cash flow" is truly an inflow or outflow, it cannot be the current balance on that date. Conversely, if a "cash flow" is the current balance on that date, it cannot be an inflow or outflow for the purpose of calculating the IRR on later dates.

For example, consider the first several "cash flows":

Please Login or Register  to view this content.

If the amount for 12/31/08 is the current investment balance (sum of the cash flows on 1/1/08 through 12/29/08 plus interest or change in market value), it is not an inflow or outflow when calculating the IRR on 4/15/09.

Moreover, if the amount for 4/15/09 is an inflow or outflow, it is not the current balance on 4/15/09. So the IRR calculation for 4/15/09 is missing the "cash flow" that represents the current balance.

Consequently, all of your XIRR calculations are incorrect, except perhaps the 12/31/08 XIRR.

(And even that is suspect because the presumptive "ending balance" on 12/31/08 is negative -- very negative relative to the presumptive "initial balance or investment" on 1/1/08. That's one heckuva "margin" hole.)

[CutlerCJ]

I cannot see how to delete this double post!

[CutlerCJ]

Thank you for your thorough reply. I’m afraid I’m not fully following your explanation, however, perhaps because I am not sufficiently fluent in the use of this function.

I understand the point you’ve made (and your formula) in the second paragraph, but to your subsequent point that “the correct structure of an investment model for the IRR calculation is: initial investment or balance as the first "cash flow", signed as an inflow; followed by cash flows in and out of the investment; followed lastly by the current (ending) investment balance as the last "cash flow", signed as an outflow,” this is indeed what I’ve attempted to produce.

The first figure in my sample -- $6,062.50 on 1/1/08 – is the initial investment, signed as an inflow (-ive), followed by the first dividend -- $6.103.02 on 4/15/08 – signed as an outflow (+ive). Lastly, my sample indicates a final dividend/outflow of $248,219.82 on 12/31/18.

My objective is to calculate a return on these cashflows.

I appreciate that there are “lots” of things I’m doing wrong, and I greatly appreciate the explanation for why my calculation isn’t working, but I’m looking for an answer to how I should calculate such a return. I’m am thankful for any guidance!
Chris

p.s. none of the dividends are re-invested; these are pure cashflows in and out

[joeu2004]

My "subsequent point" refers primarily to the many XIRR calculations before the last one.

Let's break down the example that I gave you step-by-step, and see if that improves your understanding of my comments.

Please Login or Register  to view this content.

You say that 6,062.50 on 1/1/08 is the "initial investment". Does that include any beginning balance? That is, was the balance zero before the investment on 1/1/08?

Let's assume it was.

You say that 6,103.02 on 4/15/08 is the first non-reinvested dividend (outflow).

Fine. But you try to calculate =XIRR(C$2:C3,B$2:B3).

That would suggest that 6,103.02 is also the ending balance on 4/15/08.

After all, you say that you understand that every IRR calculation must have an ending balance as well as an initial balance/investment and any interstitial cash flows.

Okay, let's say it is. Then after that outflow, the account balance is zero. So what is generating the next dividend (outflow) of 4,087.71 on 6/16/08?

I conclude that 6,103.02 must not be the ending balance. Therefore, the XIRR that you calculate in D3 is incorrect because your IRR model for 1/1/08 through 4/15/08 does not include an ending balance.

-----

Think of it this way. Suppose you open a savings account with 6,062.50. The savings account earns 6,103.02 (!) in interest in the first 105 days, which you decide to withdraw, leaving 6,062.50 in the account.

(I'm assuming that might be what you meant to suggest by your 1/1/08 and 4/15/08 cash flows.)

Is the 105-day interest rate 0.6684% = 6103.02/6062.50 - 1? (Annualized to 2.3427% = (6103.02/6062.5)^(365/105) - 1, assuming daily compounded, as XIRR does.)

No. The balance after 105 days is 12,165.52 = 6062.50 + 6103.02. So the 105-day interest rate is 100.6684% = 12165.52/6062.50 - 1, which is annualized to 1025.8340% (!) = (12165.52/6062.50)^(395/105) - 1.

(Note that 6062.50*(1 + 100.6684%) = 12,165.52.)

Indeed, we get the latter result by using the following for the XIRR model:

Please Login or Register  to view this content.

=XIRR(C2:C4,B2:B4) returns 1025.8340%.

-----

Hopefully, now you understand my point about all of the XIRR calculations except perhaps the last one. That is the one that you said demonstrates your understanding of these principles.

In my previous comments, I had assumed that the amounts on 12/31 are year-end balances. That is why I concluded (conjectured) that even your last XIRR calculation was incorrect.

If, instead, you are saying that all of the amounts except the first and last are indeed either withdrawn amounts (non-reinvested dividends) or additional investments, and the first amount is both the initial investment and beginning balance, and the last amount is the ending balance, then yes, your last XIRR for 12/31/18 is correct (!).

[CutlerCJ]

Thank you for your exceptionally detailed and helpful answer! I now fully understand your point about why the calculations that didn't include a final payout/outflow are not accurate.

The conclusion stated in your final sentence is precisely correct, which appears to mean that the 1245% return is accurate.

The purpose of this exercise is to produce annual statements, including ROI reporting, for company shareholders. The inflows are actually principal and interest against a loan from the company in exchange for shares of stock, and the outflows are dividends against the stock. The final outflow is the value of the stock minus the balance of the loan.

Because we're measuring the ROI against cashflows on borrowed money, this is the equivalent of buying on margin, which can obviously produce some crazy looking returns when it goes well.

I want to provide information to the shareholders that is not only accurate but meaningful. To the latter point, I'm not sure how meaningful it is to report a 1245% return when this figure is nearly identical whether my final payout is $248K or $248M. I'm mindful of your explanation for why this is true, but as a practical matter, how valuable is this information to a shareholder if the same ROI can lead to such wildly disparate outcomes?

If you have any input on this latter issue, or if you can suggest an Excel function that would produce an ROI that might be more meaningful to an investor, I'd certainly welcome it. Thanks again for your help!
Chris

[joeu2004]

(Errata in red and blue.)

I would use TWR -- time-weighted (rate of) return. You need the ending balance for each day that a cash flow occurs, just as you would in order to show the IRR after each cash flow. Then the TWR before each cash flow is: (currBal + cf) / prevBal - 1, formatted as Percentage, where cf is -inflow and +outflow, and currBal and prevBal are normally positive. The TWR for the total term (1/1/08 through 12/31/18) is PRODUCT(1 + perTWR) - 1, which must be array-entered as written. perTWR is the range of periodic TWRs for each cash flow. The annualized TWR is: totalTWR^(365/("12/31/18" - "1/1/08")) - 1.

(The term TWR is a misnomer, IMHO, despite attempts to justify it in the wikipage. BTW, IRR is called a money-weighted rate of return -- another misnomer, IMHO.)



-----

PS.... I would not use MIRR, another Excel function that someone might mention. It is even less meaningful than (X)IRR, IMHO. However, it has the benefit of always being computable. Sometimes, (X)IRR is not, due to both mathematical and internal implementation considerations.

[CutlerCJ]

I've been out of town, but I wanted to follow up and thank you again for taking the time to provide some really helpful feedback.

[joeu2004]

You're welcome!