Using XIRR in Excel

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
Topic Author
ufeeboy
Posts: 71
Joined: Sat May 10, 2008 2:40 pm

Using XIRR in Excel

Post by ufeeboy »

Hi Bogleheads,

I would like to start computing my annual returns but not sure the best way to do it. Is the XIRR function the best way to do that?

What data do I need to do it?

Thanks!
dbr
Posts: 35324
Joined: Sun Mar 04, 2007 9:50 am

Post by dbr »

XIRR is appropriate to calculating returns in the presence of arbitrary contributions and withdrawals.

You need the starting balance and date, the ending balance and date, and the dates and amounts of all contributions and withdrawals to the account.

The help links in Excel work pretty well to explain the format.
gkaplan
Posts: 7034
Joined: Sat Mar 03, 2007 8:34 pm
Location: Portland, Oregon

Post by gkaplan »

Gordon
MCSquared
Posts: 240
Joined: Sun Aug 02, 2009 7:59 pm

Re: Using XIRR in Excel

Post by MCSquared »

ufeeboy wrote:Hi Bogleheads,

I would like to start computing my annual returns but not sure the best way to do it. Is the XIRR function the best way to do that?

What data do I need to do it?

Thanks!
I use XIRR as I have inflows/outflows that vary. My older version of excel did not have the XIRR function but I found something I found easy to use in Google Docs.
User avatar
kcyahoo
Posts: 434
Joined: Mon Feb 19, 2007 9:59 pm
Location: Venice, FL

Post by kcyahoo »

There are two ways to compute your Rate-of-Return. Both produce the same answer. One is complicated and one is easy. XIRR is the easy way. The Excel HELP tells you how to use it.
Retired @ 57, now 75 | was 50/45/5, then 42/54/04, now 35/60/5 | KC
Deacon Mike
Posts: 136
Joined: Fri Jun 01, 2007 12:54 pm

Post by Deacon Mike »

I use the modified Dietz return, not XIRR
User avatar
House Blend
Posts: 4816
Joined: Fri May 04, 2007 1:02 pm

Post by House Blend »

I never use spreadsheets. When I do want to estimate internal rates of return, there are quick and dirty approximations that are good enough for me.

The only data we lazy people need to gather are:

1. BB = beginning balance
2. FB = final balance
3. C = net contributions (could be positive or negative).

If you made only deposits, or only made withdrawals, then you can easily generate upper and lower bounds: The XIRR must lie somewhere between

Code: Select all

FB/(BB+C) and (FB-C)/BB.
These are the rates you would get if all of your flows took place either at the beginning of the period, or at the end. Which one is the upper bound and which one is the lower bound depends on the sign of C and the sign of (FB-BB-C) (i.e., whether you have a net gain or loss).

Note that there are circumstances where these bounds are unhelpful, such as BB=0 (new investor) or C > FB or C < -BB. Assuming you are not in these troublesome ranges, I like to use their geometric mean as a cheap approximation to XIRR; i.e.,

Code: Select all

SQRT( FB*(FB-C) / BB*(BB+C) ).
A cheaper approximation is just to put half the money up front and half at the back end:

Code: Select all

(FB-C/2)/(BB+C/2).
If C is small compared to FB and BB (as is likely if you are a Boglehead with gray hair), these two approximations will be very close to each other.

Another approximation I like, for those of us who are periodic investors (or withdraw-ers), is the limiting rate that would result if the net contributions were spread uniformly and continuously throughout the time period in question. (Ex. suppose you added a total of $10K irregularly over a one year period. What would the XIRR be if the $10K were deposited at a constant rate of $27.40 per day?)

This "continuous" rate of return is the root R of the equation

Code: Select all

BB*R + C*(R-1)/log(R) = FB
where log(*) denotes the natural (base e) logarithm. Although this requires a numerical root finder, it is a robust formula that gives reasonable answers for all initial data (BB,FB,C), unlike the corner cases that can show up when you try to use the previous approximations.

Example: a beginner Boglehead starts out with nothing (BB=0), adds $10K throughout the year (C=10), and has a final balance of $11K (FB=11). Then the continuous approximation to his XIRR is R=1.206; i.e., a rate of 20.6%.

HB
YDNAL
Posts: 13774
Joined: Tue Apr 10, 2007 4:04 pm
Location: Biscayne Bay

Re: Using XIRR in Excel

Post by YDNAL »

ufeeboy wrote:I would like to start computing my annual returns but not sure the best way to do it. Is the XIRR function the best way to do that?
House Blend wrote:The only data we lazy people need to gather are:

1. BB = beginning balance
2. FB = final balance
3. C = net contributions (could be positive or negative).
Coincidentally, that's the same data I use.

Beginning Balance $1
Contrib./(Withdrawal) $1
Ending Balance $1.50 :evil: <- p-od!

Beginning Balance $1
Contrib./(Withdrawal) $1
Ending Balance $2.05 :D <- happy!
Landy | Be yourself, everyone else is already taken -- Oscar Wilde
User avatar
CyberBob
Posts: 3352
Joined: Tue Feb 20, 2007 2:53 pm

Post by CyberBob »

Image

Bob
Topic Author
ufeeboy
Posts: 71
Joined: Sat May 10, 2008 2:40 pm

Post by ufeeboy »

House Blend wrote:I never use spreadsheets. When I do want to estimate internal rates of return, there are quick and dirty approximations that are good enough for me.

The only data we lazy people need to gather are:

1. BB = beginning balance
2. FB = final balance
3. C = net contributions (could be positive or negative).

If you made only deposits, or only made withdrawals, then you can easily generate upper and lower bounds: The XIRR must lie somewhere between

Code: Select all

FB/(BB+C) and (FB-C)/BB.
These are the rates you would get if all of your flows took place either at the beginning of the period, or at the end. Which one is the upper bound and which one is the lower bound depends on the sign of C and the sign of (FB-BB-C) (i.e., whether you have a net gain or loss).

Note that there are circumstances where these bounds are unhelpful, such as BB=0 (new investor) or C > FB or C < -BB. Assuming you are not in these troublesome ranges, I like to use their geometric mean as a cheap approximation to XIRR; i.e.,

Code: Select all

SQRT( FB*(FB-C) / BB*(BB+C) ).
A cheaper approximation is just to put half the money up front and half at the back end:

Code: Select all

(FB-C/2)/(BB+C/2).
If C is small compared to FB and BB (as is likely if you are a Boglehead with gray hair), these two approximations will be very close to each other.

Another approximation I like, for those of us who are periodic investors (or withdraw-ers), is the limiting rate that would result if the net contributions were spread uniformly and continuously throughout the time period in question. (Ex. suppose you added a total of $10K irregularly over a one year period. What would the XIRR be if the $10K were deposited at a constant rate of $27.40 per day?)

This "continuous" rate of return is the root R of the equation

Code: Select all

BB*R + C*(R-1)/log(R) = FB
where log(*) denotes the natural (base e) logarithm. Although this requires a numerical root finder, it is a robust formula that gives reasonable answers for all initial data (BB,FB,C), unlike the corner cases that can show up when you try to use the previous approximations.

Example: a beginner Boglehead starts out with nothing (BB=0), adds $10K throughout the year (C=10), and has a final balance of $11K (FB=11). Then the continuous approximation to his XIRR is R=1.206; i.e., a rate of 20.6%.

HB
hmmm....yours sounds like the hard way...lol!

I'll try both ways and see what comes out...kind of nervous to see what it is though.

Thanks everyone!
User avatar
House Blend
Posts: 4816
Joined: Fri May 04, 2007 1:02 pm

Post by House Blend »

ufeeboy wrote:I'll try both ways and see what comes out...kind of nervous to see what it is though.
If you have gray hair, I predict that Excel and the quick-and-dirty approximations will all agree with each other to two decimal places. :)
Topic Author
ufeeboy
Posts: 71
Joined: Sat May 10, 2008 2:40 pm

Post by ufeeboy »

House Blend wrote:
ufeeboy wrote:I'll try both ways and see what comes out...kind of nervous to see what it is though.
If you have gray hair, I predict that Excel and the quick-and-dirty approximations will all agree with each other to two decimal places. :)
A little gray hair but I'm 33 and have only been contributing for 5 years.
User avatar
BruceM
Posts: 1847
Joined: Fri Aug 08, 2008 1:09 pm
Location: Manzanita, Oregon

Re: Using XIRR in Excel

Post by BruceM »

ufeeboy wrote:Hi Bogleheads,

I would like to start computing my annual returns but not sure the best way to do it. Is the XIRR function the best way to do that?

What data do I need to do it?

Thanks!
XIRR is the best and most accurate method for calculating your rate of return, if you know the dates you made your investment contributions and your withdrawals. This method is far more accurate than annual average returns, as contributions and withdrawals (cash flows) are weighted for both time and value.

The other way to accurately calculate your IRR is to use the uneven cash flow function on your financial calculator...but you must break your intervals down to the lowest common interval. For examples, if all cash flows occur on the same date each year, then the calculated IRR would be annual. If made on the first of each month, the IRR would be monthly. However, you'd have to include every month. So if there were no cash flows for 6 months, then you'd have to enter zero for a frequency of 6. I believe (although I haven't yet tested) the XIRR function does this automatically.

The other thing to watch out for is what constitutes a cash flow, and make sure your signage is consistent. For example, a dividend that is immediately reinvested thru a DRIP would not be a cash flow. Transaction costs, assuming they are paid from the invested amount, would be a cash flow.

And the convention I use is that if the cash flow is going into the investment account, it is negative (its going away from my household). If it comes out, it is going from the investment account to me, so it is a positive number. So to get the IRR up to a certain date, the sign on the final account value would be positive.

BruceM
User avatar
serbeer
Posts: 1270
Joined: Fri Dec 28, 2007 2:09 pm

Re: Using XIRR in Excel

Post by serbeer »

BruceM wrote:...
For example, a dividend that is immediately reinvested thru a DRIP would not be a cash flow. Transaction costs, assuming they are paid from the invested amount, would be a cash flow.
I don't think dividends have to be re-invested immediately. As long as it is re-invested into the same entity (security, account, portfolio) thay you are calculationg ROR of using XIRR, timing should not matter since it remains internal transaction.

As an example, assuming you are calculation ROR on account level, dividents sitting in account for a month is no different than yet another security in the account that performed badly (no or little growth). In fact, the dividends do not have to be re-invested at all as long as total account value, icluding the dividends, is used for final number in XIRR.

Same goes for individual security level of ROR, just include dividend value into final number and you can sit on it forever without invalidating XIRR.
TheEternalVortex
Posts: 2570
Joined: Tue Feb 27, 2007 9:17 pm
Location: San Jose, CA

Post by TheEternalVortex »

Incidentally, if you want to know what this is actually doing, Wikipedia has a pretty good explanation.
gkaplan
Posts: 7034
Joined: Sat Mar 03, 2007 8:34 pm
Location: Portland, Oregon

Post by gkaplan »

Note the spelling error in the second sentence below in that wiki article.
Calculation of IRR with the help of Trial and error method

IRR can also be calculated by Trail and error method if the data satisfy the following conditions.
Gordon
User avatar
CyberBob
Posts: 3352
Joined: Tue Feb 20, 2007 2:53 pm

Post by CyberBob »

gkaplan wrote:Note the spelling error in the second sentence below in that wiki article.
Calculation of IRR with the help of Trial and error method

IRR can also be calculated by Trail and error method if the data satisfy the following conditions.
Trail and error? Is that when you take the wrong path while hiking and forget all about your investment return because you're lost in the woods? ;)
Fixed. Don't forget, it's a wiki, which welcomes typo corrections like that from anyone.

Bob
Topic Author
ufeeboy
Posts: 71
Joined: Sat May 10, 2008 2:40 pm

Post by ufeeboy »

House Blend wrote:
ufeeboy wrote:I'll try both ways and see what comes out...kind of nervous to see what it is though.
If you have gray hair, I predict that Excel and the quick-and-dirty approximations will all agree with each other to two decimal places. :)
XIRR came out to 6.25%. I've been investing since 2005 and have been 100% equities the whole time. Seems about right since I lost 50% of my money during this recession. My current balance is 10% more than what I've put in so far.

I tried your way too but my beginning balance is 0 so I couldn't come up with a number.
User avatar
House Blend
Posts: 4816
Joined: Fri May 04, 2007 1:02 pm

Post by House Blend »

ufeeboy wrote:
House Blend wrote:
ufeeboy wrote:I'll try both ways and see what comes out...kind of nervous to see what it is though.
If you have gray hair, I predict that Excel and the quick-and-dirty approximations will all agree with each other to two decimal places. :)
XIRR came out to 6.25%. I've been investing since 2005 and have been 100% equities the whole time. Seems about right since I lost 50% of my money during this recession. My current balance is 10% more than what I've put in so far.

I tried your way too but my beginning balance is 0 so I couldn't come up with a number.
Not enough gray hair--starting from 0 is a case where the easy approximations don't work very well. For fun, if you are interested, I can do the continuous approximation (the root finding problem) for you: tell me what the ratio (CONTRIBUTIONS)/(FINAL BALANCE) is.
Post Reply