« Tools for the Do-It-Yourself Financial Planning Crowd | Main | Save the Internet »
How to Calculate Your Personal Rate of Return
By JLP | April 28, 2006
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!
Topics: Financial Math Basics, Personal Rate of Return, Time-Weighted Rate of Return | 17 Comments »
April 30th, 2006 at 10:53 pm
[...] 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.) [...]
May 1st, 2006 at 12:54 pm
[...] 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 [...]
May 2nd, 2006 at 10:39 am
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!
May 7th, 2006 at 8:11 pm
[...] 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). [...]
September 4th, 2006 at 10:15 am
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
September 26th, 2006 at 7:18 am
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.
December 30th, 2006 at 1:04 am
XIRR seems to calculate annualized rate of return.
January 2nd, 2007 at 11:05 pm
[...] 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: [...]
January 6th, 2007 at 12:01 pm
[...] Kilde: AllFinancialMatters [...]
January 24th, 2007 at 1:37 pm
[...] 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. [...]
January 29th, 2007 at 6:39 pm
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!
January 30th, 2007 at 1:06 am
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.
April 4th, 2007 at 10:12 am
This is a really fast way to see how my overall 401K is performing. Thanks for the help.
December 19th, 2007 at 3:58 pm
If I know my XIRR how do I find out my investments? I put in the money at the beginning of the year.
February 11th, 2008 at 11:06 am
Thank you- I really needed this today for a pow-wow with my broker!
July 21st, 2008 at 1:28 pm
[...] 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 [...]
August 6th, 2008 at 7:41 pm
[...] 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 [...]