Here’s how to use XIRR in Excel.
First off, I want to thank the guys at the Vanguard Diehards Forum (and this forum in particular) for helping me out with this project. The post I put up earlier this week was wrong. I was following an example I had found in this Morningstar Tutorial. I still am not sure what I did wrong. Anyway, here’s another stab at calculating your personal rate of return.
Let’s say that in 2004 (the year for which I already have the data) on the last day of each month you invested $100 in Vanguard’s S&P 500 Index Fund (VFNIX). Here’s the closing prices and the number of shares purchased on each date:
Date |
Purchase |
Shares |
01/31/2004 |
104.54 |
.9566 |
02/29/2004 |
105.98 |
.9436 |
03/31/2004 |
104.01 |
.9614 |
04/30/2004 |
102.37 |
.9768 |
05/31/2004 |
103.76 |
.9638 |
06/30/2004 |
105.41 |
.9487 |
07/31/2004 |
101.92 |
.9812 |
08/31/2004 |
102.31 |
.9774 |
09/30/2004 |
102.99 |
.9710 |
10/31/2004 |
104.55 |
.9565 |
11/30/2004 |
108.78 |
.9193 |
12/31/2004 |
111.64 |
.8957 |
Over the course of the year you would have purchased 11.451934 shares and based on the year-end closing price of 111.64, those shares would be worth $1,278. Some simple math will tell us that VFNIX returned 6.8% (not including dividends) from 1/31 – 12/31 ((111.64 – 104.54) ÷ 104.54). However, the real question is: how did the portfolio perform for you? Or, what was your personal rate of return?
Fortunately, there’s a really cool tool in Microsoft Excel that makes this calculation really easy. The function is called XIRR and it is part of a add-in package for Excel. I use Excel 2003 and I just went to the help section, typed in XIRR and followed the directions on how to install it. What the XIRR function does is give you the internal rate of return for a series of cash flows. The only information Excel needs in order to make the calculation is the date and the amount of the cash flow. It is important to note that in order for the calculation to work, there has to be both negative numbers and positive numbers (why, I do not know. I’m not a math whiz). I usually enter the investments as negative numbers since that is money you are paying out. Anyway, the spreadsheet looks like this:
The 14.46% figure represents the internal rate of return for this portfolio. To use the XIRR function (once you have installed it) go to the Insert menu, choose Function, in the box for category, choose All, and then simply scroll down and choose XIRR. Then, you should see this screen:
There you have it. I have shown you how to do the calculation. Now we need to talk about what it means. When I saw that 14.46% number I thought there was NO WAY that it could be accurate. However, it is correct because it is an annualized figure. Also, if you look at the closing prices for the mutual fund, you will notice that most of the performance came in last two months of the year, which meant that the bulk of the purchases were made at lower prices throughout the year. Ron at the Vanguard Forum put it this way in post #20 in the thread:
The first $100 earns an annualized rate of 14.46% for eleven months. So its value at the end of December is 100*(1.1446)^(11/12)=113.18. The second $100 earns the 14.46% rate for ten months. Its ending value is 100*(1.1446)^(10/12)=111.91. Do that for the rest of the deposits, add up all the ending values and the sum is $1278.
He’s right! I did the math and it works out perfectly!
JLP,
How do you use this information? In other words, in deciding whether to stick with a particular investment, how much weight would you put on IRR vs. the published market return of it? Thanks!
Hi,
Am confused. I bought a life insurance( ULIP) for which i have paid the first modal premium of Rs. 100000.00. How do i calculate the IRR!!? Help please
Deduct the insurance coverage premium from this. It is likely to be around Rs 3000. The remaining sum would have been converted to units. The daily NAV of these units should be available on the site of the Insurer. No. of units X NAV will give you the current value. The invested value is Rs 7000; the difference is the unrealised gains. Use XIRR(Invested value:current value, Invested date:Current Date, 0.1) for the IRR.
XIRR seems to calculate annualized rate of return.
Need ASAP help with calculating a few problems:
1) Company A pays a dividend of $2.40 and its stock price is expected to remain constant at $16. What rate of return will an investor enjoy by owning the stock?
2) Company B pays a dividend of $12. Its stock is expected to grow in price at a rate of 4%. how much should you pay for the stock if your expected rate of retun is 12%?
Help is desperately needed, and greatly appreciated!
Thanks so much for the quick tutorial on this formula. I was just wanting to calculate this for a couple of my funds.
Quicken shows a loss on the fund but only because the reinvestments are counted as ‘new money’ – so I bought the fund in October for $1800 and it is now worth $2000 but quicken says I am down $167 dollars because my ‘cost basis’ is $2167 even though I have only put in $1800.
This is a really fast way to see how my overall 401K is performing. Thanks for the help.
If I know my XIRR how do I find out my investments? I put in the money at the beginning of the year.
Thank you- I really needed this today for a pow-wow with my broker!