Using XIRR in Excel
Using XIRR in Excel
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 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 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.
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.
Re: Using XIRR in Excel
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.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!

 Posts: 136
 Joined: Fri Jun 01, 2007 12:54 pm
 House Blend
 Posts: 4816
 Joined: Fri May 04, 2007 1:02 pm
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
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 (FBBBC) (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.,
A cheaper approximation is just to put half the money up front and half at the back end:
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 withdrawers), 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
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
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 (FBC)/BB.
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*(FBC) / BB*(BB+C) ).
Code: Select all
(FBC/2)/(BB+C/2).
Another approximation I like, for those of us who are periodic investors (or withdrawers), 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*(R1)/log(R) = FB
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
Re: Using XIRR in Excel
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?
Coincidentally, that's the same data I use.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).
Beginning Balance $1
Contrib./(Withdrawal) $1
Ending Balance $1.50 < pod!
Beginning Balance $1
Contrib./(Withdrawal) $1
Ending Balance $2.05 < happy!
Landy 
Be yourself, everyone else is already taken  Oscar Wilde
hmmm....yours sounds like the hard way...lol!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 betweenThese 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 (FBBBC) (i.e., whether you have a net gain or loss).Code: Select all
FB/(BB+C) and (FBC)/BB.
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.,A cheaper approximation is just to put half the money up front and half at the back end:Code: Select all
SQRT( FB*(FBC) / BB*(BB+C) ).
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.Code: Select all
(FBC/2)/(BB+C/2).
Another approximation I like, for those of us who are periodic investors (or withdrawers), 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 equationwhere 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.Code: Select all
BB*R + C*(R1)/log(R) = FB
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
I'll try both ways and see what comes out...kind of nervous to see what it is though.
Thanks everyone!
 House Blend
 Posts: 4816
 Joined: Fri May 04, 2007 1:02 pm
A little gray hair but I'm 33 and have only been contributing for 5 years.House Blend wrote:If you have gray hair, I predict that Excel and the quickanddirty approximations will all agree with each other to two decimal places.ufeeboy wrote:I'll try both ways and see what comes out...kind of nervous to see what it is though.
Re: Using XIRR in Excel
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.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!
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
Re: Using XIRR in Excel
I don't think dividends have to be reinvested immediately. As long as it is reinvested into the same entity (security, account, portfolio) thay you are calculationg ROR of using XIRR, timing should not matter since it remains internal transaction.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.
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 reinvested 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.

 Posts: 2570
 Joined: Tue Feb 27, 2007 9:17 pm
 Location: San Jose, CA
Incidentally, if you want to know what this is actually doing, Wikipedia has a pretty good explanation.
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?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.
Fixed. Don't forget, it's a wiki, which welcomes typo corrections like that from anyone.
Bob
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.House Blend wrote:If you have gray hair, I predict that Excel and the quickanddirty approximations will all agree with each other to two decimal places.ufeeboy wrote:I'll try both ways and see what comes out...kind of nervous to see what it is though.
I tried your way too but my beginning balance is 0 so I couldn't come up with a number.
 House Blend
 Posts: 4816
 Joined: Fri May 04, 2007 1:02 pm
Not enough gray hairstarting 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.ufeeboy wrote: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.House Blend wrote:If you have gray hair, I predict that Excel and the quickanddirty approximations will all agree with each other to two decimal places.ufeeboy wrote:I'll try both ways and see what comes out...kind of nervous to see what it is though.
I tried your way too but my beginning balance is 0 so I couldn't come up with a number.