XIRR Calculation

[PrefInvestor]

Hi All,

Well since joining LF Ive noticed that everyone tends to include XIRR figures within their portfolios. Now this is a totally new metric for me but I thought Id mess around with my investments spreadsheet to try and include this as a new column. So I have produced a few pivot tables which have extracted a list of all my purchases and dividends form all my investments with dates. Havent completed the exercise yet as Id like to replace my pivot tables with queries so that I can more easily combine these two elements into a single table on which to perform the calculation.

But anyway. Having got some values I thought Id do a test to see what it would produce for one of my stocks. So I a manually constructed a list of purchases and dividends with dates for one of my stocks (BP) and with appropriate negative values for the buys and put it into XIRR. I was somewhat surprised to get a zero XIRR result when I reckon that Im about 20-odd percent up over the time Ive held it ?. But thinking a bit more closely about the data Im putting in I dont see anything which is providing any information regarding the increase in the stock price ?. So perhaps its not surprising.

I'm probably doing something wrong (garbage in, garbage out etc.)

Any ideas appreciated !.

Regards

Pref

[monabri]

Check that any blank cells are indeed blank ( right click, "clear contents")

Assuming that dates are entered for the buy dates and divi dates and that today's [ =today() ] is also included with the current value.


Tricky without seeing the table.

[IanTHughes]

I a manually constructed a list of purchases and dividends with dates for one of my stocks (BP) and with appropriate negative values for the buys and put it into XIRR. I was somewhat surprised to get a zero XIRR result when I reckon that Im about 20-odd percent up over the time Ive held it ?. But thinking a bit more closely about the data Im putting in I dont see anything which is providing any information regarding the increase in the stock price ?. So perhaps its not surprising.

Have I understood you correctly, you have entries with dates for purchases and all dividends but not for the current value?

If that is correct then you should add a single further transaction, a sale of the whole holding, with today's date and today's holding value.

One other thing to bear in mind is that the first transaction in the table must be the earliest date. I do not believe the date order of entries after that first transaction matters, but if the first transaction is not the earliest date, the calculation will not work.


I hope that helps


Ian

[Alaric]

If that is correct then you should add a single further transaction, a sale of the whole holding, with today's date and today's holding value.

This is a skeleton of how it needs to look

Code: Select all

purchase   6 Apr 2018   -5000
dividend   1 Oct 2018   250
market value   6 Mar 2019   5100
XIRR      7.85%

You will always need an input of the current date and market value.

[monabri]

here's an example, with numbers "doctored"



(view using CTRL ¬ to show what is contained in the cells. note - dates are displayed as numbers)




The value of the holding in FCSS is shown at today's date by looking up the value on a separate worksheet called SVS Trading at cell Q63.

The XIRR calculation reads in all the dates entered, including the last date, which is "today" and all the monetary sums, including todays value shown as a positive number.

The X-array contains "the money", the W-array contains the dates.

The Total return (TR) is simply the addition of all the monetary values in the table.

[PrefInvestor]

Ahhh thanks for the help guys. I had NOT included the current valuation as of todays date (as a positive figure) as I had not seen that described anywhere. Including this has had a dramatic effect on the result, now showing me an XIRR of 16.6% which is more like what I was expecting - for a stock that I know has done well.

I shall now buckle down to converting my pivot tables to queries, joining them to get all of the dates/values in one table so that I can call the XIRR function on a contiguous range of cells.

Many Thanks

Pref

[EssDeeAitch]

I have created a table to calculate XIRR and it looks like this (except I have just used some generic stock and values)



Assuming Date is in Cell A1, Event in B1, Value in C1 and PF Valuation date in A20 then =XIRR(A2:A20,C2:C20)
XIRR = 21.1%

Now, I am not sure if this is correct and maybe someone can point out any errors but it seems valid.
Hope this is helpful

[monabri]

Note the 3rd argument, "estimated_irr"

Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an estimated_irr of 0.1 or 10%

[EssDeeAitch]

Note the 3rd argument, "estimated_irr"

Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an estimated_irr of 0.1 or 10%

Just tried this with third argument of 1, 5, 10, 20, 40, 80 & 160 and the result did not change from the 21.1% so I am at a loss to understand the validity of the third argument. Does it make a difference in your calculations?

[IanTHughes]

Note the 3rd argument, "estimated_irr"
Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an estimated_irr of 0.1 or 10%

Just tried this with third argument of 1, 5, 10, 20, 40, 80 & 160 and the result did not change from the 21.1% so I am at a loss to understand the validity of the third argument. Does it make a difference in your calculations?

The third parameter does not affect the correct answer rather it gives the function a starting "guess" as to what the correct answer is.

The function operates by starting out with a "guess" as to the correct answer which it then checks against the dates and values entered. The first answer arrived at will not be correct but will allow a further more accurate "guess" to be made, which in turn will result in a third even more accurate "guess" and so on and so on. The function however will only "guess" a limited number of times and when that number of "guesses" have been used up without getting the correct answer, the function gives up.

In order to assist the function to start out with a "guess" that will not result in the total number of "guesses" being used up before a solution is found, the optional third parameter allows the introduction of what is a close starting "guess".

If you want to see this in action, use a single purchase on day one, with the value of that purchase, including Stamp Duty and Broker Commission, with the same value being used on day two but without the extra costs involved in the purchase. The 0.5% loss due to Stamp Duty in one day means the correct answer is a huge loss, which would take many more "guesses" to arrive at than are available, because the default starting "guess" is 10%

Hope that helps


Ian

[EssDeeAitch]

Note the 3rd argument, "estimated_irr"
Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an estimated_irr of 0.1 or 10%

Just tried this with third argument of 1, 5, 10, 20, 40, 80 & 160 and the result did not change from the 21.1% so I am at a loss to understand the validity of the third argument. Does it make a difference in your calculations?

The third parameter does not affect the correct answer rather it gives the function a starting "guess" as to what the correct answer is.

The function operates by starting out with a "guess" as to the correct answer which it then checks against the dates and values entered. The first answer arrived at will not be correct but will allow a further more accurate "guess" to be made, which in turn will result in a third even more accurate "guess" and so on and so on. The function however will only "guess" a limited number of times and when that number of "guesses" have been used up without getting the correct answer, the function gives up.

In order to assist the function to start out with a "guess" that will not result in the total number of "guesses" being used up before a solution is found, the optional third parameter allows the introduction of what is a close starting "guess".

If you want to see this in action, use a single purchase on day one, with the value of that purchase, including Stamp Duty and Broker Commission, with the same value being used on day two but without the extra costs involved in the purchase. The 0.5% loss due to Stamp Duty in one day means the correct answer is a huge loss, which would take many more "guesses" to arrive at than are available, because the default starting "guess" is 10%

Hope that helps


Ian

Nope, with two arguments I am getting a value, with three a #NUM error. I have tried using 0.1, 1, 5, 10 to no avail. Is the third argument something to worry about if the formula is giving what seems a reasonable answer with two?

EXAMPLE USED
01-01-2018 | £1015
02-01-2018 | £1000

XIRR = -3.35% with two arguments, #NUM with three

[Alaric]

Optional. It is your guess at the internal rate of return. If this parameter is omitted, it assumes an estimated_irr of 0.1 or 10%

Having once written a FORTRAN routine to calculate IRR (aka yield) for Bonds with unusual cash flows, the routines converge very rapidly when there's both a positive yield and one way cash flow.

Be aware that if the cash flows go in both directions, in other words, both sales and purchases, the underlying mathematics (zeroes of a polynomial) are such that there can be more than one solution. The initial "guess" of 10% tilts convergence towards a positive outcome.

[monabri]

XIRR information from Microsoft support.

https://support.office.com/en-us/articl ... 03ad9adc9d

XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value.

If any number in dates precedes the starting date, XIRR returns the #NUM! error value. ( As mentioned by IanTHughes previously)


EssDeeAitch...I'm not sure what you're doing but you seem to have all positive numbers. Try entering the " buys" ( of shares) as negative numbers, dividends as positive ones and the current value as a positive number ...see the graphic I posted above.

[IanTHughes]

In order to assist the function to start out with a "guess" that will not result in the total number of "guesses" being used up before a solution is found, the optional third parameter allows the introduction of what is a close starting "guess".

If you want to see this in action, use a single purchase on day one, with the value of that purchase, including Stamp Duty and Broker Commission, with the same value being used on day two but without the extra costs involved in the purchase. The 0.5% loss due to Stamp Duty in one day means the correct answer is a huge loss, which would take many more "guesses" to arrive at than are available, because the default starting "guess" is 10%

Nope, with two arguments I am getting a value, with three a #NUM error. I have tried using 0.1, 1, 5, 10 to no avail. Is the third argument something to worry about if the formula is giving what seems a reasonable answer with two?

EXAMPLE USED
01-01-2018 | £1015
02-01-2018 | £1000

XIRR = -3.35% with two arguments, #NUM with three

First of all, the first "buy" transaction should be a negative cash flow, not positive as you have it above. So your answer of -3.35% is not correct.

But, you are right, using a purchase of -1,015 together with a day two value of +1,000 - with no third parameter - gives -99.56%, my apologies. Try it with a value on day 2 of +900 - again with no third parameter - the solution will be #NUM.

However, I believe that my admittedly layman's explanation of "guesses" being made and a limited number of possible "guesses" being available was correct


Ian

[EssDeeAitch]

Nope, with two arguments I am getting a value, with three a #NUM error. I have tried using 0.1, 1, 5, 10 to no avail. Is the third argument something to worry about if the formula is giving what seems a reasonable answer with two?

EXAMPLE USED
01-01-2018 | £1015
02-01-2018 | £1000

XIRR = -3.35% with two arguments, #NUM with three

First of all, the first "buy" transaction should be a negative cash flow, not positive as you have it above. So your answer of -3.35% is not correct.

But, you are right, using a purchase of -1,015 together with a day two value of +1,000 - with no third parameter - gives -99.56%, my apologies. Try it with a value on day 2 of +900 - again with no third parameter - the solution will be #NUM.

However, I believe that my admittedly layman's explanation of "guesses" being made and a limited number of possible "guesses" being available was correct


Ian

Sorry Ian, sloppy of me but I did use a negative value for the investment, just didn't say so on my post.

[IanTHughes]

First of all, the first "buy" transaction should be a negative cash flow, not positive as you have it above. So your answer of -3.35% is not correct.

But, you are right, using a purchase of -1,015 together with a day two value of +1,000 - with no third parameter - gives -99.56%, my apologies. Try it with a value on day 2 of +900 - again with no third parameter - the solution will be #NUM.

However, I believe that my admittedly layman's explanation of "guesses" being made and a limited number of possible "guesses" being available was correct

Sorry Ian, sloppy of me but I did use a negative value for the investment, just didn't say so on my post.

Well, you must have something wrong because the solution is most certainly not -3.35%. Think of it, a one-day loss of -1.5% when extrapolated into an annual figure is surely going to be much higher than -3.35%


Ian

[Alaric]

However, I believe that my admittedly layman's explanation of "guesses" being made and a limited number of possible "guesses" being available was correct

I'm using OpenOffice rather than Excel, but testing it on the one day calculation establishes that it won't give a negative rate of XIRR more negative than -100%.

The lowest day 1 value where it will calculate XIRR with a day 2 value of 1000 is -1004. It won't accept a guess lower than -0.99 either. Microsoft's implementation may differ.

Whilst testing, I also established that if you reduced a £ 200,000 investment to £ 1,000 in a year, OpenOffice wouldn't give you an XIRR there either.

[PrefInvestor]

Hi monabri, I was interested in the layout that you showed in your post #205935 which seemed to indicate that including blank cells within the date/value ranges passed to the XIRR function would be ignored. I have done a few tests on this and found that this does seem to work and that formatting the list as:-

a) Purchased dates/values
Any number of blank cells
b) Dividend dates/values
Any number of blank cells
c) Todays/date/stock value

Produces the same XIRR answer.

Part of my problem with implementing the XIRR calculation is that the data required needs to be drawn from three different tables on my spreadsheet, the purchase prices from one table, the dividends from a second table and the current stock price from a third. My original plan was to use queries to combine each of these elements into a single table which could then be sorted by the stock code to produce the necessary dates and values in a contiguous range of cells suitable to pass into the XIRR function. The ability to include blank cells as in your example and the formatting approach outlined above might provide an alternative solution eliminating the need for some sorting.

A simple solution would be to write a VBA function (MyXIRR ?) possibly passing the Epic Code as an argument. This function could then build the date/values in local variables (making them contiguous) and return the XIRR value. I might try this approach but I am worried that it might disrupt the operation of undo which I find essential. In my last major set of updates to my spreadsheet I spent a lot of time removing all my VBA functionality for this very reason.

ATB

Pref

[EssDeeAitch]

First of all, the first "buy" transaction should be a negative cash flow, not positive as you have it above. So your answer of -3.35% is not correct.

But, you are right, using a purchase of -1,015 together with a day two value of +1,000 - with no third parameter - gives -99.56%, my apologies. Try it with a value on day 2 of +900 - again with no third parameter - the solution will be #NUM.

However, I believe that my admittedly layman's explanation of "guesses" being made and a limited number of possible "guesses" being available was correct

Sorry Ian, sloppy of me but I did use a negative value for the investment, just didn't say so on my post.

Well, you must have something wrong because the solution is most certainly not -3.35%. Think of it, a one-day loss of -1.5% when extrapolated into an annual figure is surely going to be much higher than -3.35%


Ian

My attention to detail needs a talking to . The dates I have used are 01.01.2018 and 01.06.2018 so there is six months between events not two days and the return is 3.53% not 3.35%

[monabri]

Hi monabri, I was interested in the layout that you showed in your post #205935 which seemed to indicate that including blank cells within the date/value ranges passed to the XIRR function would be ignored. I have done a few tests on this and found that this does seem to work and that formatting the list .....
ATB
Pref

The reason why I left "gaps" was simply so I could manually enter dividends (My VBA programming skills are not up to doing anything clever as you suggest.)

On the other hand, if one enters data manually there is the possibility of errors creeping in so I can see the value in having just one set of numbers. Indeed, I noticed in my table that I posted earlier on in this thread that I "claimed a dividend" (before I actually bought the shares - nice ..but impossible ) - after some checking, I realised that this was an error - still, it illustrated a possible layout and has contributed to the discussions above.

It's funny though, I don't mind entering dividends into my spreadsheet..quite the opposite!