Tuesday, November 21, 2006

Resource Review: Internal Rate of Return (IRR) Spreadsheet

As I have mentioned before George at Fat Pitch Financials has a great site for new investors. If you are like me and are somewhat math challenged when it comes to setting up a spreadsheet to determine IRR for your investments when you have made deposits and withdrawals of various amounts at different times of the year, George solves your problem. A few months back George posted an article on calculating the IRR as mentioned above. The best thing is that George included links to spreadsheets he created on both Google and Excel. I like the Google one best.

Before you can customize it, in the Google spreadsheet you will need to click on file in the upper right, select copy spreadsheet, and rename it. Both boxes are the same except the one on the left is for transactions during a single year and the one on the right uses once a year transactions. If you look at either book you will see three columns "Date" "Payment" and "Note."

What you want to do is change the notes to reflect your own needs. I am using this in my IRA accounts, so there is no withdrawal or taxes going on right now, only deposits. All I care about is tracking deposits and current balance. For example, if this is the first year and I open my IRA on 1/15/06 with $1,500, I would put 1/15/06 in the date field, enter -$1,500 in the amount (NOTE: deposits are entered as a NEGATIVE number using "-" before $ sign). For the note box I would just use "Deposit." Let's also assume that I made another $1,500 deposit on 6/12/06 and $1,000 on 8/23/06. (go ahead and delete George's sample numbers in the "payment" field, but leave the other dates alone for right now unless you want to get into the formula).

Now lets assume that I now have an account balance of $4,400 as of today. I would enter that in as positive number in the last box and change "sell investment" into "current balance." Now if I used today's date it would show that I have an APY of a little over 18%. If I finished the rest of the year at the same pace I would end up with an 18% return. If instead I use the year end date of 12/31/06 I get a APY of a little over 15%, which means that if I sold everything today and went all cash or my stocks stayed flat from this point I would end up the year with a 15% return.

This is a nice little sheet that George has and I recommend it for the new investor looking for a quick way to track their progress.

1 comment:


Theirs some really outstanding investing resources in this blog. Great reviews along with some excellent advice.