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:
- On January 3, 2006, my IRAs + 401k balance total as $40,031.
- We had two $4,000 Roth IRA contributions on January 14th, and then $10,500 into a Self-Employed 401k later in October.
- On January 5, 2007, our IRA + 401k balance was at $69,457.
- 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.