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 PurchasePrice SharesPurchased 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!

### 17 responses to How to Calculate Your Personal Rate of Return

1. 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!

2. 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

3. 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.

4. XIRR seems to calculate annualized rate of return.

5. 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!

6. 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.

7. This is a really fast way to see how my overall 401K is performing. Thanks for the help.

8. If I know my XIRR how do I find out my investments? I put in the money at the beginning of the year.

9. Thank you- I really needed this today for a pow-wow with my broker!

1. - April 30, 2006

[…] JLP from AllFinancialMatters has revised and perfected his guide for calculating personal rate of return. The tutorial contains snapshots from Excel that will help you create your own calculation. (595 words.) […]

2. - May 1, 2006

[…] Investing • Investing in Gas Companies • How to Calculate Your Personal Rate of Return • The Correct Way to get Ideas from Business Opportunity Magazines • A few words about Microsoft […]

3. - May 7, 2006

[…] JLP has a great how-to on calculating your rate of return on an investment with the help of Excel (with a particular add-in package). […]

4. - January 2, 2007

[…] The Finance Buff has a very interesting post that details a simple formula that you can use to estimate your personal rate of return. I blogged about this very topic last year but my formula was incredibly detailed and would be a major pain for most people to use. To compute FB’s formula, the only information you need is: […]

5. - January 6, 2007

[…] Kilde: AllFinancialMatters […]

6. - January 24, 2007

[…] This is fairly easy to do in Excel. This particular calculation is called the Personal Rate of Return. I did a tutorial on this last April, which you can read here. Just remember for the formula to work, you must have both negative and positive numbers. So, I would enter any deposits you make into the account as negative numbers and withdrawals (such as the \$20 account fee you mentioned) as positive numbers. […]

7. - July 21, 2008

[…] personal rate of return, you need to add in the purchase dates. The easiest way to do this is with Excel’s XIRR function. I ran the function myself. Here are the […]

8. - August 6, 2008

[…] personal rate of return, you need to add in the purchase dates. The easiest way to do this is with Excel’s XIRR function. I ran the function myself. Here are the […]