FV, IRR, XIRR Return Differing Investment Results - Why?

[delunatic]

I need to present to others a proposal on investing in some equities and bond funds and discovered that different functions return different results using the same parameters. FV, XIRR, and IRR, and the differences are not small. Here's a test spreadsheet I made that demonstrates the problem. https://drive.google.com/file/d/16SKB2BRc_1Dq05vHyN4yyKNBNTI_Rn-q/view?usp=sharing
Anyone have some feedback? Background info and questions are in the spreadsheet. Thanks, guys and gals. David

 

[joeu2004]

1. The Excel IRR cash flow model in column D is incorrect, given the dates in column C. Excel IRR assumes that cash flows occur at regular intervals.

So, Excel IRR assumes that the starting balance in D2 is on 6/1/2018, not 5/1/2019; and it assumes that the ending balance in D13 is on 6/1/2029, not 6/1/2028.

The best that we can do using Excel IRR is to assume that the starting balance is on 6/1/2019, not 5/1/2019.

Then we can array-enter (press ctrl+shift+Enter instead of just Enter) the following formula in D14:

=IRR(IF(ROW(D3:D12)=ROW(D3), D2+D3, IF(ROW(D3:D12)=ROW(D12), D12+D13, D3:D12)))

Thus, the first cash flow is the starting balance minus the first withdrawal on the same date (D2+D3). (Note that D3 is negative.)

And the last cash flow is the ending balance plus the last withdrawal on the same date (D12+D13).

Otherwise, the middle cash flows are as your recorded them (D4:D11).

Then, the Excel IRR result is 28.3085105936246%


2. Your Excel XIRR result is about the same as Excel IRR only because you fudged the ending balance.

If the ending balance for the Excel IRR model is -2,805,115, it should be the same for the Excel XIRR model, not -1,842,000.

Thus, the ending balance in E13 should be =D13 .

Then, the Excel XIRR result is 27.9465146064758%


The difference is due to several factors.

a. Excel IRR assumes equal intervals, whereas Excel XIRR uses the exact difference between dates. Note that the annual periods ending in 6/1/2020, 6/1/2024 and 6/1/2028 have 366 days, not 365 days.

b. The starting balance on 5/1/2019 is 31 days before the first withdrawal on 6/1/2019, not on the same date as we had to assume in order to use Excel IRR.

c. Even if we "fix" those differences, the Excel XIRR result is still infinitesimally different from the Excel IRR result. That difference might be due to arithmetic anomalies of the different internal algorithms.


3. The annual rate for the Excel FV function should be exactly the same as Excel IRR result, not an approximation (22.48% [sic]).

So F14 should be =D14 .

Thus, the Excel FV formula in F13 should be:

=FV(F14,10,F3,F2,1) / (1+F14)

The numerator is as you wrote it.

The denominator is needed because Excel FV assumes the ending balance is at the end of the last annual period, effectively 6/1/2029. But you want the ending balance at the start of the last annual period (after the withdrawal).

The division by 1+F14 effectively "backs out" the interest (return) that Excel FV included in its calculation.

Then, Excel FV returns -2,805,115, which is the same as the ending balance in D13.

-----

In your Excel file, you explain: ``the withdrawal amounts are fixed at $1050 a month X 12 mos = $12,600 annually``.

Bear in mind that your simplification alters the expected rate of return, due to the power of compounding.

I have not calculated the difference. But I suspect that it is significant over a period of 10 years (120 months).

-----

In your Excel file, you ask: ``Which is the more accurate Ending Balance for our purposes?``

Excel FV estimates the ending balance, based on the rate of return from Excel IRR.

Excel IRR estimates the rate of return, based on the presumed ending balance.

My point is: the results are tautological.

(IMHO, using Excel XIRR is overkill because it applies more date precision than is present in the original data.)


Why do you use an ending balance of 2,805,115? Is that a goal?

If so, Excel IRR provides a reasonable estimate, especially considering the approximation of the annual withdrawals.


But normally, we are interested in estimating the ending balance based on assumptions about the annual return.

In that case, Excel FV provides a reasonable estimate, even with our fudging the date of the starting balance.


But big question is: what assumptions should we make about the annual return?

The question is rhetorical. But the answer goes far beyond what you ask here and the time that I have available.

In a nutshell, I think it is not realistic to expect the average annual return to be 28%, at least not with any current investments that I can think of.

 

[joeu2004]

In your Excel file, you ask: ``Which is the more accurate Ending Balance for our purposes?``
[....]
IMHO, using Excel XIRR is overkill because it applies more date precision than is present in the original data.

Well, Excel XIRR does provide the more accurate rate of return, given the one-month difference between the starting balance and the first "annual" withdrawal.

 

[joeu2004]

Sorry for the incessant postings, but one last(?) clarification....

The annual rate for the Excel FV function should be exactly the same as Excel IRR result, not an approximation (22.48% [sic]).

Well, Excel XIRR does provide the more accurate rate of return, given the one-month difference between the starting balance and the first "annual" withdrawal.

For Excel FV, my emphasis was on "exactly" v. "an approximation" for the rate of return.

But I also meant to point out that we should use the Excel IRR result for the Excel FV rate of return because both functions make the same assumption about the regularity (equality) of the periodic interval.

Despite the fact that Excel XIRR provides the more accurate rate of return, given that your model violates that assumption.

 

[delunatic]

Sorry for the incessant postings, but one last(?) clarification....

For Excel FV, my emphasis was on "exactly" v. "an approximation" for the rate of return.

But I also meant to point out that we should use the Excel IRR result for the Excel FV rate of return because both functions make the same assumption about the regularity (equality) of the periodic interval.

Despite the fact that Excel XIRR provides the more accurate rate of return, given that your model violates that assumption.

First, than you so much for your help on this. Not sure what exactly you mean by using the IRR result for FV. It may be possible I didn't communicate clearly what the issue is.

Let me explain why, for XIRR, I'm solving for an ending balance to tie back to 22.48%. We know from the Fide4lity research of this equity fund that over 10 years it has paid out an annualized 22.48%. That much is factual (I know it's annualized so the year over year return fluctuates). Given the known historical return of 22.487% and based on that known number, and he fact that we must withdraw a fixed amount each month to provide for our aging parents needs, what will be the ending balance at 1 year? Each function returns a very different number - in the case of IRR vs. XIRR the diff is a million bucks! I'm just baffled at the huge difference between the ending balances. Any clue?

 

[joeu2004]

Not sure what exactly you mean by using the IRR result for FV.

Sorry. The details of my responses might have been overwhelming because they had to cover so much ground.

We know from the Fide4lity research of this equity fund that over 10 years it has paid out an annualized 22.48%. That much is factual

And a critical piece of information that I overlooked in your notes in the Excel file.

So let's apply the "new" information to what I provided in my previous responses.


1. With 22.48% in F14, use the following formula in F13 to estimate the ending balance:

=FV(F14,10,F3,F2,1) / (1+F14)

The result is -1801203.96809131.


Enter the formula =$F$13 into D13 and E13. The ending balance should be exactly the same in all cases, at least for comparison purposes.

(Rounding even to the "cent" introduces additional differences.)


2. Then array-enter (press ctrl+shift+Enter instead of just Enter) the following formula in D14:

=IRR(IF(ROW(D3:D12)=ROW(D3), D2+D3, IF(ROW(D3:D12)=ROW(D12), D12+D13, D3:D12)))

The result is 22.48%, the same as rate of return in F14 that was used for the FV formula.


I explained both of the Excel FV and IRR formulas in my previous posting, as well as the imperfection of the necessary assumptions of those functions with respect to the dates of your cash flows. (See below.)


3. Finally, your XIRR formula in E14 now returns 22.196222782135%.

I explained that difference in my previous posting. In particular:

a. Excel IRR assumes equal intervals, whereas Excel XIRR uses the exact difference between dates. Note that the annual periods ending in 6/1/2020, 6/1/2024 and 6/1/2028 have 366 days, not 365 days.

b. The starting balance on 5/1/2019 is 31 days before the first withdrawal on 6/1/2019, not on the same date as we had to assume in order to use Excel IRR.

c. The ending balance is on 6/1/2028, the beginning of the last period, whereas Excel FV calculates the ending balance at the end of the period, effectively the beginning of the next period (6/1/2029) a year later. The difference is another period of interest for the Excel FV calculation.


In a nutshell, again, the primary difference is that Excel IRR and FV assume equal intervals ("annual").

Your model does not fit that assumption. Specifically, the first cash flow is on 5/1/2019, and the second cash flow is on 6/1/2019, 31 days later, not a year later.


4. For demonstrations purposes only, make the following experimental changes:

a. Change C2 to 6/1/2019, the same as C3.
b. Change C4 to the formula =C3+365, and copy down through C12.
c. Change C13 to the formula =C12.

Then Excel XIRR returns 22.4800000190735%, which is very close to the same result from Excel IRR and the (input) rate of return for Excel FV.


5. Alternatively, if you want to use Excel XIRR because it is more consistent with your cash flow model (in particular, the uneven intervals):

a. Clear E13, the expected ending balance for Excel XIRR.
b. Use Solver or Goal Seek to derive E13 so that E14 (XIRR result) is 22.48%.

Frankly, I am having trouble making Solver work at all. Probably a mistake of mine that I'm not seeing.

Goal Seek derived an ending balance of -1842208.14078335 in E13, a difference of about 41,004 compared to Excel FV, with an XIRR result of 22.4800000190735%, which I think is the best we can hope for, considering the experiment in #4 .

In order to achieve that Goal Seek result, I set Max Change to 0.0000001 in the Excel Options > Formulas > Calculation Options section. It is not necessary -- and it is undersirable -- to also set Enable Iterative Calculation and to change Max Iterations (default 10).


Again, I explained the need for a different ending balance compared to Excel FV and IRR, to wit: the cash flows are not equal; in particular, the first two cash flows as 31 days apart, not a year.

But the change is relatively small: less than 2.28%. Not the "million bucks" that you determined.

I'm just baffled at the huge difference between the ending balances. Any clue?

Does that answer all of your questions?

 

[joeu2004]

We know from the Fide4lity research of this equity fund that over 10 years it has paid out an annualized 22.48%.

What fund is that?!

I'm not interested in cashing in on the information. Any fund with that kind of compounded(?) annual return is too risky for my blood.

But I'm wondering if there is some misunderstanding of that number.

 

[joeu2004]

5. Alternatively, if you want to use Excel XIRR because it is more consistent with your cash flow model (in particular, the uneven intervals):
a. Clear E13, the expected ending balance for Excel XIRR.
b. Use Solver or Goal Seek to derive E13 so that E14 (XIRR result) is 22.48%.

Please forgive the incessant responses, but I "teach" people all the time how to do this without using Solver or Goal Seek. It's embarrassing!

In order to derive the ending balance in E13 that results in (about) 22.48% in E14, simply enter the following formula into E13:

=-XNPV(F14,E2:E12,C2:C12) * (1+F14)^((C13-C2)/365)

For the derivation of the formula, see the mathematical formula in the Excel XNPV help page.

I might also note that XNPV works here only because F14 (target IRR) is non-negative. Excel XNPV does not allow negative discount rates, a design flaw, IMHO.

I also note that your XIRR formula returns 22.4800000190735%, a rate so close to 22.48% only because you use a "guess" of 1%.

With the default "guess" (10%), XIRR returns 22.4799996614456%. The difference is nearly 18 times greater (in magnitude).

The capricious effect of the "guess" parameter might be due to the necessarily iterative nature of the internal algorithm.

 

[delunatic]

Does that answer all of your questions?

Dude, you are so awesome it's scary. I was not paying enough attention to the intervals which could easily have been equal (and I followed your example and made them so) as it is only a projection and I didn't realize the impact this would have on IRR. Wow. Most excellent. Also, I had never used FV until now. I only know enough Excel to get simple things done. I'm still deciphering the array formula you wrote. Cool stuff. Can't thank you enough!

Go take a look at this and tell me what YOU think of it. I think the long view on this one is among the best.
· In 10 years it has delivered an average of 22.48% annualized.
· It has never dipped below a 15.24% average return over the last five years.
· Morningstar rates it in the top 1% among over a thousand similar equity funds in every year measured – 3YR, 5YR, and 10YR. What they measure is very important: It is calculated based on a risk-adjusted return and accounts for monthly variations, placing more emphasis on downward variations and rewarding consistent performance. You can make more money elsewhere, but this fund pays a combination of the best returns with the smoothest ride.
· All the above detail and much more is at https://fundresearch.fidelity.com/mutual-funds/view-all/61747T106
The above might seem a little salesy but it's an excerpt from my proposal to my siblings. I like it. Tell me you think of it.

 

[delunatic]

(I just clicked thru the above link to the stock fund I sent you and it's gone done a little bit, so the 10 year number is down a little.)
Something just occurred to me to test - when I delete all withdrawals so I only show the initial $350k and the ending balance which is derived with the FV formula, the percentage return remains constant; only the ending balance is affected by deleting all withdrawals. It seems right to me but I wanted to seek your confirmation. This is another way to prove the formula works as desired, right?