Calculate Your Exact 2006/2007 Portfolio Rate Of Return

My Money Blog has partnered with CardRatings for selected credit cards, and may receive a commission from card issuers. All opinions expressed are the author’s alone, and has not been provided nor approved by any of the companies mentioned. Thank you for your support.

I sensed that people weren’t quite satisfied with my Rate of Return Estimation Calculator. After wasting lots of time trying to program the internal-rate-of-return (IRR) function myself, I realized I could simply embed an online spreadsheet. Ain’t technology grand?

The spreadsheets below will do all the exact calculations for you. I made one for 2006 and one for calculating your ongoing year-to-date and annualized returns in 2007. You will need to supply the date and amount of all deposits and withdrawals in your accounts. If you reinvested dividends then those can be ignored and rolled into the return.

Calculate Your 2006 Portfolio Return


(If you don’t see anything above, that probably means you are using an unsupported browser. You can download the spreadsheets in Excel format here: 2006Return.xls and 2007Return.xls. The instructions are the same.)

How To Use It

Simply enter your initial balance and all subsequent deposits or withdrawals into the appropriate cells. The final balance is negative in order to signify a withdrawal of all your money, which is necessary to calculate your portfolio’s return.

The initial setting is my own data for 2006:

  1. On January 3, 2006, my IRAs + 401k balance total as $40,031.
  2. We had two $4,000 Roth IRA contributions on January 14th, and then $10,500 into a Self-Employed 401k later in October.
  3. On January 5, 2007, our IRA + 401k balance was at $69,457.
  4. My annualized 2006 return for my retirement portfolio is was 21.6%.

You can fiddle around with the $0.00 value to see how the return changes. For example, if I added $1,000 on Febuary 1st then my profit would be less and my return would go down to 19.25%. Returns are all pre-tax.

Calculate Your 2007 Year-to-Date Portfolio Return

How To Use It

Again, simply enter your initial balance and all subsequent deposits or withdrawals into the appropriate cells. When you reach March 31st and want to get your quarterly returns, you will again have to make an imaginary withdrawal of all your money. Then in the very next row add the money back in on the same date.

Cell D8 would be the year-to-date return, or the return so far this year up to the 1st quarter. Cell C8 would be your return for the first quarter of 2007, but annualized. In other words, it shows you the hypothetical return if that quarter’s performance was continued over the entire year. This continues for the second quarter and so on.

These spreadsheets were adapted from the one at the XIRR page at Gummy-Stuff.org.

My Money Blog has partnered with CardRatings for selected credit cards, and may receive a commission from card issuers. All opinions expressed are the author’s alone, and has not been provided nor approved by any of the companies mentioned. MyMoneyBlog.com is also a member of the Amazon Associate Program, and if you click through to Amazon and make a purchase, I may earn a small commission. Thank you for your support.



User Generated Content Disclosure: Comments and/or responses are not provided or commissioned by any advertiser. Comments and/or responses have not been reviewed, approved or otherwise endorsed by any advertiser. It is not any advertiser's responsibility to ensure all posts and/or questions are answered.

Comments

  1. Thanks for the spreadsheets, I will try them out.

    If you wanted a ton more online spreadsheets, go to choosetosave.org, they have a ton.

  2. I use the online MSN Money Portfolio Manager, it has the functionality you are looking for, and its secure and accessible through your MSN passport from any computer as well. I tracked my transactions starting from 1998 and has worked out for me quite well.

  3. Since we’re talking money here, I’ll quick point out that there are some fine free software spreadsheets available if you don’t have Excel. I usually use Gnumeric (http://www.gnome.org/projects/gnumeric/) for my IRR calculations. But I assume the Calc feature of Open Office (http://www.openoffice.org/) can do the calculation as well.

    An advanced user may find the free spreadsheets missing a desired feature or two. Charting in Excel is best-of-class as I understand. But for most “home” work I’d guess that either is perfectly adequate, at just the right price.

  4. It seems very hard to compare CD (which shows APY) or MM fund returns (which shows 7 day rate) to treasury bill or agency notes (which shows the discount rate, or yield to maturity rate).

    Do you know any place to convert APY to yield to maturity rate, or 7 day rate (MM fund) to APY or yield to maturiry rate?

    Thanks

    John

  5. Yes, all the .xls files can be opened by most spreadsheet programs. I use OpenOffice myself.

    7-day rates are annualized but not compounded. I can probably write a quick calculator, but not sure if it’d be worth the effort as the math is very straightforward. Divide the rate by 365/7, add 1, and take it to the 365/7th power.

    Example: 5% 7-day rate.

    (0.05/52.14+1)^52.14 = 1.0512, or 5.12% effective APY.

    Keep in mind you are still assuming the rate to be constant for the entire year, which it rarely is.

    T-Bill Calculator

  6. 2007 year to date spreadsheet doesnt work for me.
    Keeps saying some sort of error when i input data.

  7. Still works for me. I would recommend trying another browser, or downloading the Excel spreadsheet files – they work with OpenOffice.org if you don’t have Microsoft Office.

  8. got it..thanks

  9. Great spreadsheet! I have been using XIRR for a while but I like your trick with the debit amount followed by the same credit amount for doing your subtotaling……

Speak Your Mind

*