Help with understand XIRR

[SoReady]

Hi,

Starting next year I will be taking dividends, cap gains and some regular withdrawals from my funds. I would like to figure out my overall portfolio performance using XIRR, but for the life of me I am struggling with it.

I would like to get a YTD performance number. I put together a few lines, but my percentage result is not what I expect!

Here is my sample data...
50000 1/1/17
2000 1/2/17
6000 1/3/17
-48000 11/2/2017

2017 YTD -2028.00%

The last date uses the function of =today()
the XIRR cell (-2028.00%) is =xirr(A1:A4,B1:B4)*100

So with a staring balance of $50k and adding money of $2000 and $6000 in two subsequent days that at todays date with the ending balance of $48k (less than original balance so expecting a negative percent) it gives me a negative 2028%? Doesn't seem right. What am I doing wrong?

Thanks. I searched for XIRR but most of the results were a reference to that in determining someones YTD, but not a discussion on the formula.

Bob D

 

[cathy63]

The amounts you invest should be negative and the income from them should be positive, so -50k, -2k, -6k, +48k. XIRR for this give -.2028, or -20.28%. If you still see that as 2028%, make sure you're not multiplying something that's already formatted as a percentage by 100.

 

[SoReady]

Wow! Thanks so much!

Money invested as negative and money withdrawn as positive seems so counter intuitive.

Bob D

 

[SoReady]

Sorry... need to post another followup.

After playing with the numbers maybe you can help me understand how to interpret this.

Data...

-50,000 1/1/17
60000 11/2/2017

2017 YTD 24.38%

Now starting value of $50k and ending value of $60k is just a $10k increase on $50k. Which, if I think about it correctly, would be a 20% increase.

So why does xirr come up with 24.38%? Does xirr only work if there are dates and actions in between?

Bob D

 

[SecondCor521]

So why does xirr come up with 24.38%?

I believe it's giving you the annualized rate. Try changing the second date from 11/2 to 12/31 and you should get a number much closer to 20%.

XIRR will work with any dated series of inflows and outflows. The only requirement of which I am aware is that there is at least one inflow and at least one outflow.

 

[SoReady]

Thanks. You are spot on!

When you say "annualized rate" is that what it would expect it to be by the end of the year, given the data it has?

Also, for those that may care. Since I had mental issues with deposits being negative and withdrawals being positive I made some changes from the suggestions by cathy63.

I make the start dollars positive. Withdrawals as negative and deposits as positive. The ending amount is negative. Now the downside is the YTD percent is calculated as negative. To account for this I made the formula =abs(xirr(.....))

Thanks for the help!

 

[cathy63]

You have 10 months in your calculation, and Excel is giving you an annualized rate of return. If you change that final date to 1/1/2018, then you should get exactly 20%.

 

[cathy63]

Thanks. You are spot on!

When you say "annualized rate" is that what it would expect it to be by the end of the year, given the data it has?

Also, for those that may care. Since I had mental issues with deposits being negative and withdrawals being positive I made some changes from the suggestions by cathy63.

I make the start dollars positive. Withdrawals as negative and deposits as positive. The ending amount is negative. Now the downside is the YTD percent is calculated as negative. To account for this I made the formula =abs(xirr(.....))

Thanks for the help!

Yes, switching the signs will work too. Usually this kind of analysis is used for a business evaluating an investment in a new product or project where there is an initial cost (negative cash flow out of the company's capital budget) and some more costs (negative) and returns (positive) over time. If you think of it from the point of view of the corporate budget, then the positive and negative signs may make more sense.

 

[SecondCor521]

When you say "annualized rate" is that what it would expect it to be by the end of the year, given the data it has?

Correct. It is giving you the interest rate that a theoretical investment would have to return in order to match the cash flows in your data series.

In other words, if you imagine that a bank has a Theoretical SoReady Savings Account that paid 24.38% APY and you deposited $50,000 into your TSRSA on 1/1/17, your balance, with interest, on 11/2/17, would be exactly $60,000.

 

[GreatLaker]

This link has some good info on how to use XIRR. It illustrates how to use it for multiple years, and also for multiple accounts. It is a Canadian thread but pretty sure it will work with US $ too.

Question to OpenOffice users: how to calculate XIRR?

 

[GreatLaker]

So why does xirr come up with 24.38%?

I believe it's giving you the annualized rate. Try changing the second date from 11/2 to 12/31 and you should get a number much closer to 20%.

This page explains how to convert the output of XIRR to calculate the actual (i.e. not annualized) rate of return for periods of less than one year:
Year-to-Date Return for XIRR

"The first (and earliest) entry should then be January 1 of this year (meaning the portfolio value as of January 1).
Then an XIRR return of 12.3% means that, at the current rate of return (since Jan 1), you'd make 12.3% over the entire 365-day year.
If, however, only N days have passed since Jan 1, you can get a YTD return using: (1+0.123)N/365 - 1."

 

[SoReady]

Thanks. I think I have a pretty good handle on this now.

I appreciate all the responses.

 

[Blueskies123]

Wow! Thanks so much!

Money invested as negative and money withdrawn as positive seems so counter intuitive.

Bob D

Think of it as money out and money in