Excel XIRR Question

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Mike1
Posts: 1
Joined: Fri Nov 10, 2017 5:40 pm

Excel XIRR Question

Post by Mike1 » Fri Nov 10, 2017 6:23 pm

I'm looking to use Excel's XIRR function to give the performance of my wife's and my Roth accounts. I believe I have things set up correctly, with the initial balances as positive values and the final balances as negative values. But I'm confused as to why the XIRR result comes out different for our two accounts. The two accounts had somewhat different balances at the end of 2016, but as of the beginning of 2017, they have been invested in identical funds, with identical cash inflows. Yet the XIRR function applied to the 2017 figures comes out over 3% different for the two accounts.

This may be a naive question (and may speak to a more general misconception about returns on my part), but why wouldn't the internal rate of return come out the same when the two accounts have been invested identically, except for having a different initial (and of course final) balance? I thought the return rate would be same, regardless of the different absolute amounts.

I don't see how I can post an .xlsx file, but I'll put the data below.

Thank you,
~Mike

Acct 1 Acct 2
12/31/2016 37033.48 34014.58
3/3/17 900.00 900.00
3/22/17 450.00 450.00
4/11/17 230.55 230.55
4/26/27 230.55 230.55
5/11/17 230.55 230.55
5/26/17 230.55 230.55
6/12/17 230.55 230.55
6/26/17 230.55 230.55
7/11/17 230.55 230.55
7/26/17 230.55 230.55
8/11/07 230.55 230.55
8/26/17 230.55 230.55
9/11/17 230.55 230.55
9/26/17 230.55 230.55
10/26/17 230.55 230.55
10/31/17 -49960.82 -45326.58

Acct 1 XIRR = 26.16%
Acct 2 XIRR = 22.80%

(I realize these won't be completely valid until I have figures for the rest of 2017, but the issue for me is why they are different.)

User avatar
Ketawa
Posts: 1883
Joined: Mon Aug 22, 2011 1:11 am
Location: DC

Re: Excel XIRR Question

Post by Ketawa » Fri Nov 10, 2017 8:27 pm

Column 1 had more invested at the beginning of the year than Column 2, and both columns invested the same periodic amounts during the year. Therefore, Column 1 had its overall investment more frontloaded in the year. XIRR calculates the return as if all dollars invested during the year earned a flat compound interest rate. Since Column 1 has a higher XIRR value than Column 2, it means that the investment generally had better performance early in the year than later in the year.

For a simple example, imagine an investment that went up 100% smoothly Jan-Jun, then was flat Jul-Dec. Someone who had $1M invested at the beginning of the year and invested $1K more throughout the year would have XIRR = almost 100%. Someone who had $0 invested at the beginning of the year and invested $1K more throughout the year would have XIRR = a much lower value.

User avatar
Doc
Posts: 7684
Joined: Sat Feb 24, 2007 1:10 pm
Location: Two left turns from Larry

Re: Excel XIRR Question

Post by Doc » Sat Nov 11, 2017 7:54 am

I think you have your signs wrong. An IRR calculation is a discounted cash flow analysis. To get your initial balance you have to buy it. The cash flow is negative. Similarly the ending balance is represented as a sale. Positive cash flow.

Also if you are reinvesting dividends the cash flow is zero.

The XIRR function can also give erroneous results for periods less than a year. Add a dummy cash flow of $1 at start and end to make the analysis for a minimum of one year.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.

User avatar
Ketawa
Posts: 1883
Joined: Mon Aug 22, 2011 1:11 am
Location: DC

Re: Excel XIRR Question

Post by Ketawa » Sat Nov 11, 2017 9:42 am

Image

OP, I tried to recreate your values, but couldn't. You had two typos with years 2027 and 2007, but fixing those does not give your values for XIRR either.
Doc wrote:
Sat Nov 11, 2017 7:54 am
I think you have your signs wrong. An IRR calculation is a discounted cash flow analysis. To get your initial balance you have to buy it. The cash flow is negative. Similarly the ending balance is represented as a sale. Positive cash flow.
The sign doesn't matter as long as the convention is consistent throughout the data.

Image

Multiply every term in the summation by -1, and the sum is still equal to zero.
Doc wrote:
Sat Nov 11, 2017 7:54 am
Also if you are reinvesting dividends the cash flow is zero.
The two accounts in OP's example had different starting values then added the same amounts during the year, so this doesn't appear to be the issue in this case.

User avatar
Doc
Posts: 7684
Joined: Sat Feb 24, 2007 1:10 pm
Location: Two left turns from Larry

Re: Excel XIRR Question

Post by Doc » Sat Nov 11, 2017 1:08 pm

Ketawa wrote:
Sat Nov 11, 2017 9:42 am
Doc wrote: ↑Sat Nov 11, 2017 6:54 am
I think you have your signs wrong. An IRR calculation is a discounted cash flow analysis. To get your initial balance you have to buy it. The cash flow is negative. Similarly the ending balance is represented as a sale. Positive cash flow.
The sign doesn't matter as long as the convention is consistent throughout the data.
Correct, but if you were to tell your boss that the final balance was going to be negative he probably wouldn't make the investment or you might just get fired. :D (When I got paid for doing discounted cash flow calcs XIRR wasn't invented yet. We used a slide rule.)
Ketawa wrote:
Sat Nov 11, 2017 9:42 am
Doc wrote: ↑Sat Nov 11, 2017 6:54 am
Also if you are reinvesting dividends the cash flow is zero.
The two accounts in OP's example had different starting values then added the same amounts during the year, so this doesn't appear to be the issue in this case.
I wasn't addressing the specific example. Just trying to point out that reinvested dividend have a zero net cash flow. I didn't know if the OP's interim numbers were new investments or reinvestments. (I actually didn't look when I saw that all the numbers had the same sign.)
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.

bgf
Posts: 77
Joined: Fri Nov 10, 2017 9:35 am

Re: Excel XIRR Question

Post by bgf » Sat Nov 11, 2017 1:26 pm

Doc wrote:
Sat Nov 11, 2017 7:54 am
The XIRR function can also give erroneous results for periods less than a year. Add a dummy cash flow of $1 at start and end to make the analysis for a minimum of one year.
for periods less than one year, or for YTD calculations, I use:

=SUM(((1+XIRR(A3:A34,B3:B34,10%))^(((DATE(2017,11,9)-(DATE(2016,12,31)))/365))-1))

that is giving me the actual dollar weighted return YTD, correct?

User avatar
Doc
Posts: 7684
Joined: Sat Feb 24, 2007 1:10 pm
Location: Two left turns from Larry

Re: Excel XIRR Question

Post by Doc » Sat Nov 11, 2017 1:33 pm

bgf wrote:
Sat Nov 11, 2017 1:26 pm
Doc wrote:
Sat Nov 11, 2017 7:54 am
The XIRR function can also give erroneous results for periods less than a year. Add a dummy cash flow of $1 at start and end to make the analysis for a minimum of one year.
for periods less than one year, or for YTD calculations, I use:

=SUM(((1+XIRR(A3:A34,B3:B34,10%))^(((DATE(2017,11,9)-(DATE(2016,12,31)))/365))-1))

that is giving me the actual dollar weighted return YTD, correct?
I don't know. I haven't tried to "fix" XIRR. My data is in Quicken and I just use their methodology and you have to put in a dummy entry to make it work. I thought Excel had the same "problem". Your approach might be the solution.

I think if I was going to try to compare two investments where I thought XIRR wouldn't work for some reason I think I would revert to the prehistoric method of just using a given "hurdle rate" and just calculate the net present value of the two alternatives. Computers not required.
A scientist looks for THE answer to a problem, an engineer looks for AN answer and lawyers ONLY have opinions. Investing is not a science.

Post Reply