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.
The Best Credit Card Bonus Offers – November 2024
Big List of Free Stocks from Brokerage Apps
Best Interest Rates on Cash - November 2024
Free Credit Scores x 3 + Free Credit Monitoring
Best No Fee 0% APR Balance Transfer Offers
Little-Known Cellular Data Plans That Can Save Big Money
How To Haggle Your Cable or Direct TV Bill
Big List of Free Consumer Data Reports (Credit, Rent, Work)
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.
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.
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.
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
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
2007 year to date spreadsheet doesnt work for me.
Keeps saying some sort of error when i input data.
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.
got it..thanks
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……