IRR Question

[cartica]

HI,

I am trying to figure out the best way to calculate the IRR on my Stock Purchases/Sales. I am using the first day of the month and pulling in the cash flows (-) for buys, and (+) for sells. Sometimes my first purchase, and last sale are not the first of the month. I'm trying to figure out if I am doing this right. Also, what is the IRR telling me if it's more Negative, than my actual return (this is not included in excel example)? How do I explain this difference? Please see attached example.

question.xlsx

[joeu2004]

I am trying to figure out the best way to calculate the IRR on my Stock Purchases/Sales. I am using the first day of the month and pulling in the cash flows (-) for buys, and (+) for sells. Sometimes my first purchase, and last sale are not the first of the month.

You are correct to use XIRR, especially in the latter case (variable cash flow frequency).

And you are wise to use SUMPRODUCT to calculate NPV, since Excel XNPV does not accept negative IRR. (A defect.)

You are also wise to calculate the NPV to check any Excel XIRR result. I have seen examples where Excel XIRR returns a completely bogus IRR.

You might wonder why the NPV is about -0.17 and -5.02, seemingly not very close to zero.

First, they are close to zero relative to the cash flow amounts (millions).

But the fact, is: my own XIRR implementation does result in exactly zero for both NPVs.

I could speculate about why there is a difference. I could rant about the unreliability of Excel XIRR. But in this case, the difference is really very small:

Please Login or Register  to view this content.

You could use the Excel IRR for your example, since the cash flows occur on a regular basis. However, that gives you a monthly IRR, whereas XIRR returns an annual IRR.

You might annualize the monthly IRR with the formula =(1+monIRR)^12-1. But the result will differ from the result from Excel XIRR because months are not really equal.

Also, what is the IRR telling me if it's more Negative, than my actual return (this is not included in excel example)? How do I explain this difference?

"Time value of money" is the standard explanation. Of course, that means nothing to most people. To be honest, I cannot explain it any further on a conceptual level. It is what it is.

The important thing about IRR is: it is a fictitious rate(!). What I mean by that is: it does not really reflect either the earning (or loss) rate or the "spending power", which is affected by inflation.

The purpose of the IRR is to compare two or more investment alternatives. For that purpose, what matters is not the actual numbers, but how they compare.

[cartica]

So, by using the first day of every month for the XIRR to go off of, that is the right way to do it, even if my first or last cash flow is not the 1st of the month. I know if I were to change the dates it changes my XIRR.

[cartica]

Please see my revised spreadsheet that has 2 tabs. The Main tab shows the IRR by going monthly, and aggregating all cash flows in tab Underlying Data into the month. The 2nd IRR on main tab is just using each individual cash flow without grouping. It yields a slightly different result. I am not sure which is more accurate. Tab 2 could be potentially much longer, so I thought that grouping by month was the most sensible, but I don't want to have inaccurate numbers. I assume at the end of the day it's directionally accurate, but want the tightest, most accurate numbers as possible.


question.xlsx

[joeu2004]

So, by using the first day of every month for the XIRR to go off of, that is the right way to do it, even if my first or last cash flow is not the 1st of the month.

Oh no, that is not what I meant. I misunderstood your description.

You should use the actual dates of the cash flows when you use Excel XIRR. And yes, that changes the calculated IRR, as it should.

You can also eliminate the dates where there is zero net cash flow when you use Excel XIRR.

Using the same date of every month and including zeros for months with no activity are methods that I use to allow me to use Excel IRR instead of Excel XIRR. Excel IRR is inexplicably more reliable that Excel XIRR, IMHO. But the annualized calculated IRR is different from what Excel XIRR would calculate, even with the modified dates.

[cartica]

For my situation is XIRR the best option of all the different choices since cash flows are not regular?

[joeu2004]

Errata...

You should use the actual dates of the cash flows when you use Excel XIRR.

I should have written: you can use the actual dates.

I presume this is an academic exercise. In academia, it is not unusual to net monthly cash flows and to annualize a calculated monthly IRR by 12*monIRR instead of (1+monIRR)^12-1. In fact, there are real-world situations where 12*monIRR is the correct.

For my situation is XIRR the best option of all the different choices since cash flows are not regular?

Generally, yes. But....

Please see my revised spreadsheet that has 2 tabs. The Main tab shows the IRR by going monthly, and aggregating all cash flows in tab Underlying Data into the month. The 2nd IRR on main tab is just using each individual cash flow without grouping. It yields a slightly different result. I am not sure which is more accurate. Tab 2 could be potentially much longer, so I thought that grouping by month was the most sensible, but I don't want to have inaccurate numbers. I assume at the end of the day it's directionally accurate, but want the tightest, most accurate numbers as possible.

What do you mean by "most accurate"? For what purpose?

If this is an academic exercise, you should match the methodology that the instructor or author expects you to use.

If you are authoring a paper or presentation, you should choose a method that matches your presentation of the data.

I like to choose a method that the reader can duplicate. If I net monthly cash flows, I would calculate a monthly IRR and annualize by 12*monIRR or (1+monIRR)^12-1, depending on the purpose. In that case, I would use usually Excel IRR.

Alternatively, I might add a footnote to explain that the calculated IRR is based on actual dates, not the net cash flows presented.

Tab 2 could be potentially much longer, so I thought that grouping by month was the most sensible, but I don't want to have inaccurate numbers.

Conceptually, the number of cash flows should not matter so much as the frequency of cash flows relative to the span of time.

In the extremes, if we have daily cash flows spanning many decades, it should not make much difference if we aggregate cash flows. On the other hand, if we have daily cash flows spanning just a couple months, aggregating cash flows might make a big difference.

However, in actual practice, the number of cash flows might affect the behavior of the implementation. It shouldn't. But I believe I inferred that to be one of many problems with the Excel XIRR implementation. (My recollection might be wrong.)

[cartica]

Thanks. This is actually not academic but for Portfolio Analysis of a firms holdings. I am trying to use the most accurate calculation to show what my IRR is for the securities we own. We may hold securities for 4-5 years with sporatic cash flows (buys sells), or may hold for 1 year, or under. I feel like my calculations are directionally accurate, but wanted them to be as accurate as possible. I haven't found any real definitive guide out there on how to perform this. What is your background in? It seems like you have a very strong grasp on this. If you want to show some examples based on my data that would be great as well. Thanks for all your help/insight so far.

[joeu2004]

This is actually not academic but for Portfolio Analysis of a firms holdings. I am trying to use the most accurate calculation to show what my IRR is for the securities we own.

Right! Any firm that can invest nearly $135M over 2 years can and should hire a qualified financial professional for this analysis.

As for accuracy.... Mathematically, calculating an annual IRR based on actual dates of individual transactions is more accurate than annualizing a monthly IRR based on aggregated cash flows.

However, in terms of the Excel implementation, my experience (helping others) suggests that Excel XIRR is unreliable.

As long as the result causes the NPV (using SUMPRODUCT, as you did) to be "nearly zero" relative to the magnitude of the cash flows, it is more accurate.

But Excel XIRR inexplicably fails (returns #NUM or #DIV/0) more often than Excel IRR. And worse, sometimes it returns a completely bogus numerical result (NPV is not "nearly zero").

So it is prudent to always check the Excel XIRR result by using SUMPRODUCT to calculate the NPV. And in cases where it fails, you might try Excel IRR, aggregating cash flows on a regular basis, as you did, and annualizing the result appropriately.

Mathematically, that is less accurate; but it is probably better than nothing. And the difference (if Excel XIRR had worked) is usually relatively small.

Nevertheless, even Excel IRR -- and any IRR algorithm -- can fail. See the IRR wikipage for an explanation.

-----

Alternatively, you might consider calculating the "time-weighted rate of return" (TWRR).

The term is confusing, if not a misnomer. There is no "weighting". And ironically, the "time-valued" IRR is called a "money-weighted rate of return".

The TWRR is a method of calculating the simple rate of return, excluding the effects of "external" cash flows: additional investments and withdrawals. The TWRR is especially useful for comparing portfolio returns with benchmarks, since the latter is calculated without your external cash flows, of course.

In order to calculate the TWRR, you need to know the sub-period portfolio valuations, ideally on the date of each transaction. If you only have periodic valuations, you can aggregate cash flows during each period. It is less accurate; but it is "as good as it gets".

If there were no external cash flows, the simple return is yN/y0 - 1, where y0 and yN are beginning and ending valuations. If y1, y2, etc are the sub-period valuations (again, without external cash flows), note that:

yN/y0 - 1 = (y1/y0)*(y2/y1)*(y3/y2)*...*(yN/y[N-1]) - 1

(Note that most of the numerators and denominators cancel out.)

In other words, the TWRR is the product of the simple rates of return (plus 1) for each sub-period. And that is how the TWRR is calculated based on sub-period valuations excluding external cash flows.

Food for thought!

With that, I will leave this discussion. I think we have exhausted the Excel questions. Good luck!