Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

Excel XIRR Question

Discussions regarding financial software
staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Excel XIRR Question

#314938

Postby staffordian » June 3rd, 2020, 11:09 pm

I use Excel's XIRR function as one measure of performance for both my portfolio overall and of the individual constituents.

I have always used the syntax XIRR(values,dates,estimate) though I understand the estimate is optional.

All was well (as far as I can tell) until Covid 19 came along.

I then noticed several anomalies, where I knew some shares were well under water but their XIRR figures were all showing 0.00%.

I tried removing the estimate, but that had no effect. I changed it from 0.1 to -0.1 and the XIRR figure looked correct; reporting a negative as expected.

So I changed my formulae accordingly, but then the shares with a positive XIRR returned a #NUM error.

So is there a way of constructing an XIRR formula which will work regardless of whether the return is positive or negative, or will I have to keep changing the estimate in each formula depending on how well the share performs?

Any clues would be very welcome.

TIA

Staffordian

tjh290633
Lemon Half
Posts: 8207
Joined: November 4th, 2016, 11:20 am
Has thanked: 913 times
Been thanked: 4096 times

Re: Excel XIRR Question

#314945

Postby tjh290633 » June 3rd, 2020, 11:25 pm

Not a problem that I have experienced recently, although sometimes changing (or adding) an estimate can make things better. I have just checked my spreadsheet for individual shares, where all have an estimate of 0.1, yet negative results show up as expected. I suspect there may be an odd factor either in your dates or data. Text where there should be numerical data is one that causes upset for me.

TJH

Alaric
Lemon Half
Posts: 6031
Joined: November 5th, 2016, 9:05 am
Has thanked: 20 times
Been thanked: 1398 times

Re: Excel XIRR Question

#314956

Postby Alaric » June 4th, 2020, 12:35 am

staffordian wrote:So I changed my formulae accordingly, but then the shares with a positive XIRR returned a #NUM error.


If you have a mixture of positive and negative cash flows, the maths that underpins XIRR can give more than one solution. The initial "guess" can influence the convergence.

It's zeroes of a polynomial, so solutions to the equation 0 = sum of t from 0 to n of A(t) multiplied by x to the t where x = 1/(1+IRR)

A(t) is the cash flow at time t.

The Newton-Raphson method is a method of finding a solution that usually converges rapidly, although brute force works with enough computing power.

https://en.wikipedia.org/wiki/Newton%27s_method

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Excel XIRR Question

#315190

Postby staffordian » June 4th, 2020, 1:56 pm

Thank you for the replies.

I plan to check the formatting and if that's all ok I'm going to copy the data to a new spreadsheet and play about with it to see if I can isolate the issue. It does seem to occur when the negative values are quite large, eg PLI was over 15% negative at one point, though until I changed the estimate from 0.1 to -0.1 it told me the figure was 0%

A bit more detail on how I constructed the spreadsheet might raise possible issues with those more expert than me...

For individual shares, column1 is for the dates of cashflows, column2 for amounts. Purchases are negative, dividends received positive and the current date and current value (a positive figure) of that share are in cells at the bottom of the table, with a big gap above where I add entries for new purchases and dividends. As far as I know, this gap should not be an issue?

I must confess, though, that having tried to read the Wikipedia article, I'm none the wiser :)

Alaric
Lemon Half
Posts: 6031
Joined: November 5th, 2016, 9:05 am
Has thanked: 20 times
Been thanked: 1398 times

Re: Excel XIRR Question

#315195

Postby Alaric » June 4th, 2020, 2:06 pm

staffordian wrote: Purchases are negative, dividends received positive and the current date and current value (a positive figure) of that share are in cells at the bottom of the table, with a big gap above where I add entries for new purchases and dividends. As far as I know, this gap should not be an issue?


Are purchases plural? If you are periodically topping up, you will have a series of negative and positive cash flows which can cause convergence issues. You may need to separately compute the XIRR from one purchase to the next.

Another method of doing XIRR is to write a formula involving a cell containing a rate of return. You then run goal seek on the rate of return cell so that all cash flows accumulate to your portfolio end value. Convergence may still be an issue.

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Excel XIRR Question

#315204

Postby staffordian » June 4th, 2020, 2:33 pm

Alaric wrote:
staffordian wrote: Purchases are negative, dividends received positive and the current date and current value (a positive figure) of that share are in cells at the bottom of the table, with a big gap above where I add entries for new purchases and dividends. As far as I know, this gap should not be an issue?


Are purchases plural? If you are periodically topping up, you will have a series of negative and positive cash flows which can cause convergence issues. You may need to separately compute the XIRR from one purchase to the next.

Another method of doing XIRR is to write a formula involving a cell containing a rate of return. You then run goal seek on the rate of return cell so that all cash flows accumulate to your portfolio end value. Convergence may still be an issue.

Yes, I am purchasing from time to time, so maybe this is the problem.

I might just give up on tracking individual return using XIRR, reserving that function for the portfolio as a whole, where it seems to work ok, or at least it agrees quite closely to the figure produced by Moneydance which I use to track all my finances.

The key difference is that with the portfolio XIRR all the cashflows are one way, i.e. I record new money going in (nothing tends to be withdrawn) and the only negative figure is the last in the table, which is the current value.

Thanks again for your input.

Much appreciated.

monabri
Lemon Half
Posts: 8396
Joined: January 7th, 2017, 9:56 am
Has thanked: 1539 times
Been thanked: 3427 times

Re: Excel XIRR Question

#315240

Postby monabri » June 4th, 2020, 3:58 pm

That's exactly how I calculate XIRR...just check that there are no duff ( and invisible) characters in the blank area. Select all the blanks and , right click and "clear contents".

Are all the dates correct including today's date ?

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Excel XIRR Question

#315245

Postby staffordian » June 4th, 2020, 4:11 pm

monabri wrote:That's exactly how I calculate XIRR...just check that there are no duff ( and invisible) characters in the blank area. Select all the blanks and , right click and "clear contents".

Are all the dates correct including today's date ?

It's good to hear my methods are more or less on track.

I'll be giving all the data a good checking this evening.

Thanks!

monabri
Lemon Half
Posts: 8396
Joined: January 7th, 2017, 9:56 am
Has thanked: 1539 times
Been thanked: 3427 times

Re: Excel XIRR Question

#315249

Postby monabri » June 4th, 2020, 4:17 pm


monabri
Lemon Half
Posts: 8396
Joined: January 7th, 2017, 9:56 am
Has thanked: 1539 times
Been thanked: 3427 times

Re: Excel XIRR Question

#315264

Postby monabri » June 4th, 2020, 4:59 pm

I think it is a numerical issue with Excel (I've been having a play with the calcs in my spreadsheet).

By way of experiment - Try multiplying the "current value" by, say 1.5. This artifically inflates your shareholding value and the XIRR return should be positive.

Then gradually reduce the factor and watch what happens to the XIRR value.

It will go negative and then, as you reduce the multiplier, it will change to a value of 0.00%.

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Excel XIRR Question

#315266

Postby staffordian » June 4th, 2020, 5:03 pm

monabri wrote:I think it is a numerical issue with Excel (I've been having a play with the calcs in my spreadsheet).

By way of experiment - Try multiplying the "current value" by, say 1.5. This artifically inflates your shareholding value and the XIRR return should be positive.

Then gradually reduce the factor and watch what happens to the XIRR value.

It will go negative and then, as you reduce the multiplier, it will change to a value of 0.00%.

Thanks. And reading the links, it looks like there isn't a straightforward answer. I'm inclined to leave the estimate positive on the basis that most of the time the return will (hopefully!) be positive, and take the 0.00% as an error which I will ignore.

monabri
Lemon Half
Posts: 8396
Joined: January 7th, 2017, 9:56 am
Has thanked: 1539 times
Been thanked: 3427 times

Re: Excel XIRR Question

#315269

Postby monabri » June 4th, 2020, 5:24 pm

Playing around with some scenarios for Company X. For cases A to E, I'm using a positive guess value of +0.2.

In case F, I've used the figures from case E but used a negative guess of 0.2.

https://postimg.cc/2qN9VqW9

(the board doesn't allow posting of images - hence the link).

So, it's not you!

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Excel XIRR Question

#315278

Postby staffordian » June 4th, 2020, 5:59 pm

monabri wrote:Playing around with some scenarios for Company X. For cases A to E, I'm using a positive guess value of +0.2.

In case F, I've used the figures from case E but used a negative guess of 0.2.

https://postimg.cc/2qN9VqW9

(the board doesn't allow posting of images - hence the link).

So, it's not you!

That's really helpful.

Thanks again :)

monabri
Lemon Half
Posts: 8396
Joined: January 7th, 2017, 9:56 am
Has thanked: 1539 times
Been thanked: 3427 times

Re: Excel XIRR Question

#315291

Postby monabri » June 4th, 2020, 6:59 pm

Try...

Where EY236:EY245 are the investments (with EY246 being the current value).

=XIRR(EY236:EY246,EX236:EX246,0.2*SIGN(SUM(EY236:EY246)))

staffordian
Lemon Quarter
Posts: 2298
Joined: November 4th, 2016, 4:20 pm
Has thanked: 1887 times
Been thanked: 869 times

Re: Excel XIRR Question

#315317

Postby staffordian » June 4th, 2020, 8:15 pm

monabri wrote:Try...

Where EY236:EY245 are the investments (with EY246 being the current value).

=XIRR(EY236:EY246,EX236:EX246,0.2*SIGN(SUM(EY236:EY246)))

Please refer to my last post :D ;)

EDIT: Just opened the laptop and updated the formulae.

It seems to work a treat. Many thanks for your persistence and help with this!


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 8 guests