Calculate IRR and show me your math
 12-03-2009, 02:08 PM #1 Thinks s/he gets paid by the post   Join Date: Apr 2007 Location: Milford, OH Posts: 2,085 Calculate IRR and show me your math I am going to post a series of numbers, I need to verify I calculate IRR correctly. All this is for my own knowledge and spreadsheet creation. 2006 accounts (in total) had a year end value of \$133,861 2007 accounts had a year end value of \$163,685 2008 accounts had a year end value of \$120,496 2009 accounts have a current value of \$186,769 Accounts listed my 401k my Roth my Rollover wife 401k wife Roth wife Rollover 2007 deposits were \$18,913 \$8,575 \$4,000 \$0 \$4,338 \$2,000 \$0 2008 deposits were \$27,720 \$10,741 \$7,351 -\$2,351 \$6,979 \$5,000 \$0 2009 deposits were \$18,350 (do not have 401k totals as of yet) \$5,850 \$4,500 \$0 \$3,500 \$4,500 \$0 The money taken OUT of the rollover was converted to a Roth in same year. Zero sum on total return, but needed when I do IRR of each account individually. Here was my equation for year over year IRR: [(Current year balance)/(previous year balance+deposits)]-1 here were returns I calculated for overall returns: 2007 7.14% 2008 -37.05% 2009 34.52% I will then extrapolate same equation for each account (6 total accounts) I have some follow up questions once I either see error of my ways or get validation the math I have is correct. THX! __________________ __________________ Light travels faster than sound. That is why some people appear bright until you hear them speak. One person's stupidity is another person's job security.
 Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free! Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE! You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more! Join Early-Retirment.org For Free - Click Here
 12-03-2009, 02:37 PM #2 Moderator Emeritus   Join Date: May 2007 Posts: 11,056 I think that we need more information. IRR is a time weighted formula so we need to know the exact dates on which all the deposits and withdrawals were made. Also the number for 2009 is probably wrong because IRR can only calculate returns accurately for a period of at least 1 year (unless you extrapolated the 2009 end-of-year value for all your accounts). __________________
12-03-2009, 02:47 PM   #3
Recycles dryer sheets

Join Date: Sep 2009
Location: Two Harbors
Posts: 54
Your calculations seem mathematically correct. The one inaccuracy I note is that your calculation is correct ASSUMING that ALL of your new money was invested on day one of each new year. That would tend to understate your return a bit as you likely contributed either throughout the year or near the end of the year

FYI...I do a similar computation but I do it on a quarterly basis and then add the four quarter amount to create a closer annual IRR...still a bit off but quite a bit closer. Also, your method gets closer to being accurate as the beginning accopunt balance grows and is much larger than the annual contribution.

Note that Excel also has an IRR "Function" that would allow you to list 13 numbers...the account balances from beginning of Month one through the end of month 12...and calculate a very accurate IRR...probably overkill...unless you are analytical to a fault...as I have been so accussed

Tom R

Quote:
 Originally Posted by jIMOh I am going to post a series of numbers, I need to verify I calculate IRR correctly. All this is for my own knowledge and spreadsheet creation. 2006 accounts (in total) had a year end value of \$133,861 2007 accounts had a year end value of \$163,685 2008 accounts had a year end value of \$120,496 2009 accounts have a current value of \$186,769 Accounts listed my 401k my Roth my Rollover wife 401k wife Roth wife Rollover 2007 deposits were \$18,913 \$8,575 \$4,000 \$0 \$4,338 \$2,000 \$0 2008 deposits were \$27,720 \$10,741 \$7,351 -\$2,351 \$6,979 \$5,000 \$0 2009 deposits were \$18,350 (do not have 401k totals as of yet) \$5,850 \$4,500 \$0 \$3,500 \$4,500 \$0 The money taken OUT of the rollover was converted to a Roth in same year. Zero sum on total return, but needed when I do IRR of each account individually. Here was my equation for year over year IRR: [(Current year balance)/(previous year balance+deposits)]-1 here were returns I calculated for overall returns: 2007 7.14% 2008 -37.05% 2009 34.52% I will then extrapolate same equation for each account (6 total accounts) I have some follow up questions once I either see error of my ways or get validation the math I have is correct. THX!

12-03-2009, 04:33 PM   #4
Thinks s/he gets paid by the post

Join Date: Apr 2007
Location: Milford, OH
Posts: 2,085
Quote:
 Originally Posted by TomCat Your calculations seem mathematically correct. The one inaccuracy I note is that your calculation is correct ASSUMING that ALL of your new money was invested on day one of each new year. That would tend to understate your return a bit as you likely contributed either throughout the year or near the end of the year FYI...I do a similar computation but I do it on a quarterly basis and then add the four quarter amount to create a closer annual IRR...still a bit off but quite a bit closer. Also, your method gets closer to being accurate as the beginning accopunt balance grows and is much larger than the annual contribution. Note that Excel also has an IRR "Function" that would allow you to list 13 numbers...the account balances from beginning of Month one through the end of month 12...and calculate a very accurate IRR...probably overkill...unless you are analytical to a fault...as I have been so accussed Tom R
Tom (and other responder) thanks for chiming in.

As time passes, the dates/timing of the deposits matters less and less to the real value of IRR

If I calculate a 35% return and its really 37% or 33%, I am still getting the info I wanted.

Some people told me I had incorrect numerators or denominators at one point, was trying to make sure I was close.

THX!
__________________
Light travels faster than sound. That is why some people appear bright until you hear them speak. One person's stupidity is another person's job security.

12-04-2009, 05:20 AM   #5
Thinks s/he gets paid by the post

Join Date: Dec 2008
Posts: 3,860
Quote:
 Originally Posted by jIMOh Tom (and other responder) thanks for chiming in. As time passes, the dates/timing of the deposits matters less and less to the real value of IRR If I calculate a 35% return and its really 37% or 33%, I am still getting the info I wanted. Some people told me I had incorrect numerators or denominators at one point, was trying to make sure I was close. THX!
The function is problematic, so a lot is written about how to use it.

IRR is for periodic investments. XIRR is for non-periodic.

If you don't give us the dates, then to use the function(s) we'd have to guess at the dates.

I suggest you enter all the data in a spreadsheet, then copy and post it. Then it makes the checking very easy for others.

 12-04-2009, 07:16 AM #6 Thinks s/he gets paid by the post   Join Date: Jan 2006 Posts: 3,070 I agree w/ Firedreamer and T2019. What OP is calculating is a guesstimate of investment return. IRR/XIRR are technical terms having a specific timing-related definition and while the guesstimate is probably fine for defining a ballpark figure, using the term IRR seems to give a "accuracy" aura that is not matched by the result.

 Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)