I’ve previously created a spreadsheet for calculating compound annual growth rates (CAGR) as well as an online CAGR calculator, but these aren’t always the tools you need when trying to calculate your returns.
In fact, the two CAGR tools I created really only work when you have one initial amount and one final amount, and if that’s your situation, then they work wonderfully. But how can you calculate annualized returns if you put in money to invest in an account more than once, or if you make withdrawals more than once over a period of time? (This was actually exactly the situation I faced when I calculated my stock portfolio performance recently.)
The answer is actually pretty simple if you use Excel. There’s a built-in function called XIRR() that’s easy to use if you format your data correctly. XIRR() uses Newman’s method (an iterative process) to calculate your returns, so it’s not a method that’s easily done by hand.
To find your return, simply list the dates of your deposits and withdrawals in one column. In the next column, list your deposit amounts as positive numbers and your withdrawals (or the current value of the funds you’d be able to withdraw, if you’re interested in that instead) as negative numbers.
For example, let’s suppose you diligently put in a deposit into your 401(k) account on the first weekday of each month. (Maybe you try to deposit in $100 each month and deposit more in January after getting your bonus, but let’s say you also deposit less in one month due to extenuating circumstances.) Then suppose that today, October 18th, you look up how much your 401(k) is worth and see a balance of $1,247 in your account. If you just looked at the total amount you’ve invested so far ($1,100) and your current value of $1,247, you’d get a return of 13.36%. But remember, some of your money has been invested for a longer time than others, so this amount is really an underestimation.
Instead, suppose you want to find the annualized percentage return you’ve gotten so far taking time into account. To use XIRR, this is how you’d enter in your data:
Note: If you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.
| 1/2/2006 | 200 |
| 2/1/2006 | 100 |
| 3/1/2006 | 100 |
| 4/3/2006 | 100 |
| 5/1/2006 | 50 |
| 6/1/2006 | 100 |
| 7/3/2006 | 100 |
| 8/1/2006 | 100 |
| 9/1/2006 | 150 |
| 10/2/2006 | 100 |
| 10/18/2006 | -1247 |
Ok, now back to the tutorial.
Next, find XIRR under the “Financial” section of the Function section. XIRR has two required inputs and two optional inputs (a guess, and max number of iterations, both of which we’ll ignore here). The two required inputs are simply the list of values and the list of dates. Let’s suppose that the dates above start in column A, row 1 of a spreadsheet and the values start in column B, row 1.
Then in your selected cell, you’d simply type in:
to find your annualized return of 32.3%.
That’s it. Pretty simple, right?
Notice how the annual return calculation using XIRR is differs from some other common ways you might use to measure return: you could calculate your overall return of 13.4% (=1247/1100 – 1) by ignoring the time period over which you invested your money into account. If you were to use CAGR, then you might enter in your initial amount of $1,100, your final amount of $1,247, but what period of time would you use? If you chose to use 0.875 of a year (from the beginning of January through mid-October), your CAGR would come out to be 15.4%, which underestimates your investment return because it assumes you invested all your money at once, in January.
There’s no one “right” way to calculate your returns. What you mainly want to focus on are knowing what you’re calculating (data are dumb, garbage-in-garbage-out) and being consistent about the way you compare your returns. Don’t compare returns calculated using one method for some things and another method for others!
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
Carnival of Personal Finance #71 - Fat Pitch Financials
[…] How to use XIRR in Excel to calculate annualized returns at Experiments in Finance * Editor’s Choice * A great post about using the XIRR function in Excel to calculate annualized returns when you put in money to invest regularly instead of the simple situation where you put something in at the beginning of one period and that’s it. […]
Carnival of Personal Finance » Carnival of Personal Finance #71
[…] How to use XIRR in Excel to calculate annualized returns at Experiments in Finance […]
Send Money Online » Carnival of Personal Finance #71
[…] How to use XIRR in Excel to calculate annualized returns at Experiments in Finance * Editor’s Choice * A great post about using the XIRR function in Excel to calculate annualized returns when you put in money to invest regularly instead of the simple situation where you put something in at the beginning of one period and that’s it. […]
FIRE Finance
We Catch Up With Carnival Of Personal Finance #71…
We are almost always up to date with our readings of Carnivals and Festivals on Personal finance. However due to a movie festival at our place over a weekend we had missed reading Carnival Of Personal Finance #71 hosted at FatPitchFinancials. This wa…..
Watch out when using compounded average returns | Experiments in Finance
[…] Toward the end of December, I received some questions from readers asking about how to calculate average returns on investment portfolios using Excel. In previous posts on this site, you can read about using CAGR, Excel’s XIRR function, and simple averages, but they all come with one big warning, which is that your previous average performance may not necessarily be a good way to set your expectations about your future performance. […]
The Stubborn Capitalist » Blog Archive » 63 Fantastic Personal Finance Posts
[…] How to use XIRR in Excel to calculate annualized returns – Experiments in Finance […]
samar
your website has really valuable information , but can you extend these information regarding XIRR , to have complete amortization schedual using XIRR. i want to calculate interest , principad and balance for the whole period.
thanks
63 Fantastic Personal Finance Posts : SCN - Personal Finance
[…] How to use XIRR in Excel to calculate annualized returns – Experiments in Finance […]
John Ancich
Hi,
This is just the article I’ve been looking for. I knew about xirr, but the examples I saw were never as clear as yours, so now I “get it”. My only question is that I notice the range you specify inside XIRR always has to start with the first row of data. I have data that has several empty rows above it, but when I try to do XIRR that includes blank rows above the data, I get an error (whether I fill those rows with zeros or empty space). Hope my question makes sense… I know the row number of the first row of data and the last row of data, but not sure how to make this a cell range that the xirr funcition can recognize.
Ron Miller
Thanks for a very helpful article!. I do have one comment. If the Rate of Return value is negative, below about -10.02%, leaving out the “guess” results in an incorrect value for XIRR. If you include a guess of a negative number between 0 and -1 (not inclusive), it appears that XIRR will return a correct value.
Kevin
It looks like this function is basically calculating a day-weighted modified dietz return. This is just fine for the purposes that I have in mind, but I’m troubled by the fact that it automatically annualizes periods less than 1 year. I would prefer the function to calculate an unanualized figure- is that a possibility?
excel c#
excel c#
Sensex
hey thanks for the info…. was dying to find out how to use XIRR
Bill Woessner
I’m pretty sure you mean “Newton’s Method”, not “Newman’s Method”. Newton was a mathematician (among other things). Newman was a character on Seinfeld. :-p
Dividend Tree
Hello,
At morningstar (see link below), IRR is calculates monthly rate of return — not annual rate of return. It then provides one additional step to convert to annualized.
http://news.morningstar.com/classroom2/course.asp?docId=3228&page=4&CN=COM
Would that be valid for XIRR too ?
i.e. XIRR gives monthly return and then convert to annualized ?
Regards,
DT
Steve Engelen
Hi,
Can someone help me and say if there is a MIRR function for irregular dates in excel? It is thus a combination of the MIRR function and the XIRR function.
It is also called XMIRR but not a standard function in excel
thanks
Joshua
How does one overcome excels limitation of calculating FV as it requires the monthly contribution to remain constant.
Say I want to factor in annual increase of x% in the monthly contribution – what would the formula then look like?
Tumpa
Excellent…very very useful
Sunil
Thanks for the help in calculating XIRR.
Harry Teder
Tried to use your example, repeatedly, keep getting box with #N/A. Same thing when using my own investment numbers. Suggestions? Thanks
amit singh
pls send the XIRR formula
Financial Guy
Excel XIRR function screw up sometimes when the sum of cash flow is negative or the annualized return result is negative. I found a handful app in Apple App Store called “Universal Investment Return Calculator”. It can calculate like XIRR function does but it is more robust (i.e. it doesn’t screw up with negative results) and it does not need you to feed a guessed result.
Charles
That was incredibly helpful. However, the rate of return i keep getting seems to be abnormally high. the following is the calc and inputs:
Date Amount
1/2/2013 $734.74
1/2/2013 $17.46
2/15/2013 $0.10
3/28/2013 $736.77
3/28/2013 $15.40
5/15/2013 $0.08
5/15/2013 $4.83
5/15/2013 $5.06
6/28/2013 $1,008.77
8/15/2013 $10.75
9/22/2013 -3,134.94
XIRR 58.237%
SUM 2,523.21
Net Gain $611.73
any thought on why the XIRR would show 58.237%? Shouldnt is be somewhere in the low 20s?
Wuming79
Hi, why the last value is negative? Didn’t see you mentioning about withdrawal on the last month.
Paul Emmons
Wuming79, he didn’t actually withdraw everything from the portfolio, but the XIRR function works as if one pretends to do so. It requires at least one positive and one negative value in the list. If all the values were positive, then the function would have no way of distinguishing amounts invested from the final value of the investment.
This example is interesting, however, because he uses positive figures for amounts invested and a negative figure for the final value. My first encounter with XIRR (IRR, actually) was in Michael Edleson’s book “Value Averaging” where he does the opposite: negative figures for contributions to the investment and a positive figure for the end value. (This is from the standpoint of your checking account, as it were: checks written to invest = cash you don’t have anymore. Then what you’d get back if you cashed out of the investment). This is what I’ve been doing in Excel, and it seems to be working as expected.
Tamika
Awesome blog! Do you have any helpful hints for aspiring writers?
I’m hoping to start my own website soon but I’m a little lost on everything.
Would you advise starting with a free platform like WordPress or go for a paid option? There are so
many choices out there that I’m completely confused ..
Any recommendations? Cheers!
Tracy
Remarkable! Its in fact amazing article, I have
got much clear idea about from this post.
Janine
Hey! I just wanted to ask if you ever have any problems with hackers?
My last blog (wordpress) was hacked and I ended up losing months of hard work due to
no backup. Do you have any solutions to prevent hackers?
July
Hi,
All presentation above is for a period of a year only. What if the cash flow will take for about 3 years? I have this data working on a resulted for a 5,203% result. Anyone have the idea.
Data:
Period 1 to 36 is negative 1.8 (-1.8)
Last Period is positive 80
Please assist me to correct my presentation.
Thanks
Joe Russell
Thanks for your clear explanation of the XIRR function. I have recorded my investments since 1st October 2014. For each investment made I record the cost as a negative value along with the date and for each investment sold I record the sale as a positive value along with the date. For dividends received I record as positives along with the date. At the foot I insert my current portfolio valuation and date, calculating the XIRR at that point. Should I be recording the dividends received in this fashion?
www.exchangle.com
Everything is very open with a really clear clarification of the
issues. It was definitely informative. Your website is very useful.
Thanks for sharing! Thanks, Derick
https://vk.com
Hi, аlways i ussed to chek blog posts heｒe in the eаrly
hours in the break of dаy, as i enjoy to fіnd out
more and more.