Search


Subscribe to AFM


Subscribe to AllFinancialMatters
by Email

All Financial Matters

Promote Your Page Too

The American's Creed

Site Sponsors

Books I Recommend


AFM in the Media


Money Magazine May 2008

Real Simple March 2008

Blogroll (Daily Reads)

« | Main | »

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
Price

Shares
Purchased

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:

Cashflow Calculation

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:

XIRR Function Input 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 »


17 Responses to “How to Calculate Your Personal Rate of Return”

  1. » Carnival of Personal Finance #46 » Consumerism Commentary: A Blog About Personal Finance Says:
    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.) […]

  2. Carnival of the Capitalists 134 » The Small Business Buzz Says:
    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 […]

  3. Vladimir Stojanovski Says:
    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!

  4. » Last Week’s Highlights on Blueprint for Financial Prosperity Says:
    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). […]

  5. Anu Saini Says:
    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

  6. Anand Says:
    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.

  7. XLI Says:
    December 30th, 2006 at 1:04 am

    XIRR seems to calculate annualized rate of return.

  8. AllFinancialMatters » Blog Archive » Figuring Your Personal Rate of Return Says:
    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: […]

  9. Erik’s aktie blog » Blog Archive » Beregning af afkast i % Says:
    January 6th, 2007 at 12:01 pm

    […] Kilde: AllFinancialMatters […]

  10. AllFinancialMatters » Blog Archive » Question From a Reader - Calculating Returns Says:
    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. […]

  11. Marissa Says:
    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!

  12. Dogberry Says:
    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.

  13. Jose Says:
    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.

  14. Zia Says:
    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.

  15. Annie Says:
    February 11th, 2008 at 11:06 am

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

  16. Don’t Give up on Dollar-Cost Averaging! | AllFinancialMatters Says:
    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 […]

  17. FinancialGuruOnline.com » Don’t Give up on Dollar-Cost Averaging! Says:
    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 […]

Comments