Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Calculate IRR and show me your math
Old 12-03-2009, 03:08 PM   #1
Thinks s/he gets paid by the post
jIMOh's Avatar
 
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.
jIMOh is offline   Reply With Quote
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!

Old 12-03-2009, 03:37 PM   #2
Moderator Emeritus
 
Join Date: May 2007
Posts: 11,038
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).
__________________

__________________
FIREd is online now   Reply With Quote
Old 12-03-2009, 03: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 View Post
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!
__________________
TomCat is offline   Reply With Quote
Old 12-03-2009, 05:33 PM   #4
Thinks s/he gets paid by the post
jIMOh's Avatar
 
Join Date: Apr 2007
Location: Milford, OH
Posts: 2,085
Quote:
Originally Posted by TomCat View Post
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.
jIMOh is offline   Reply With Quote
Old 12-04-2009, 06:20 AM   #5
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,705
Quote:
Originally Posted by jIMOh View Post
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.
__________________
target2019 is offline   Reply With Quote
Old 12-04-2009, 08:16 AM   #6
Thinks s/he gets paid by the post
 
Join Date: Jan 2006
Posts: 2,925
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.
__________________
kaneohe is offline   Reply With Quote
Old 12-07-2009, 03:15 PM   #7
Thinks s/he gets paid by the post
jIMOh's Avatar
 
Join Date: Apr 2007
Location: Milford, OH
Posts: 2,085
Here is my issue (or further problem definition)

I track my finances once per year, between now and Dec 31 I do 99% of my investment checkups, with a brief look in June (more on this later).
The deposits occur many times thru the year and at different intervals

401k me- 26X per year
same amounts each pay period

Roth IRA me- I own numerous funds, but as part of rebalancing, I am only contributing to one of the funds now.
Rollover me- own same funds as my Roth, more on this later.

**investments in my name account for about 75% of familie's investments, and about 60% of the deposits**

401k wife- 26 deposits per year
Roth wife- deposit 1X per month, into about 6-7 funds
Rollover wife- compliments her Roth


The asset allocation is as follows
75% domestic equity/ 25% foreign equity
45% domestic large cap/ 15% domestic mid cap/ 15% domestic small cap/ 15% foreign large cap/ 10% foreign small cap and/or emerging markets


My 401k has the 75-25 allocation (using cap based mutual funds)
My Roth has the 75-25 allocation (using cap based mutual funds)
My Rollover has the 75-25 allocation (using cap based mutual funds)
Wife's 401k has the 75-25 allocation (using cap based mutual funds)

Wife's Roth and Rollover combined (Roth is less than 2 years old) is using sector funds to attempt to do 2 things:
1) have a 75-25 allocation
2) get better returns than the typical 75-25 allocation above (because funds have higher returns)

Here is what I want to track
I need to get a "feel" for my returns in each account... that is most important. How the return is measured "does not matter" to me, as long as I know its close to real.

Examples- My Roth had a 19.54% return in 2009 (thus far) even though the fund with the most money in it (the large cap) is returning lower than that (16.9% ytd). In addition if I measure my "IRR" of that one single fund, it shows my return is less than the fund's return (my return was about 15% on T Rowe web site when ytd fund returns are 16.9%).

In 2008 nearly all accounts lost 38-40%- the losses regardless of how I sliced and diced, were identical across all accounts. Meaning the allocation is what was giving the returns. I want to verify that each account is invested well because they each have about the same xray, which means they should have about the same return.


If you want raw data, I can post here, tell me what you need
On my spreadsheet I have the accounts and annual deposits and year end values
I do not track individual deposits- too many to measure, and as long as I know the macro structure is good, I am confident enough if the 75-25 allocation is giving me same returns everywhere within 3-4%, that is good. I know the sector allocation will be more volatile, and I do plan on rebalancing that once account hits about 40k.

FYI the sector funds which make up 75/25

Global Tech PRGTX
Africa and Middle East TRAMX
Emerging Markets PRMSX
Financial Services PRISX
Global Real Estate TRGRX
Growth Stock PRGFX
Health Sciences PRHSX
New Era (Natural Resources) PRNEX
Science and Technology PRSCX
Value TRVLX

So my goal is to use the 401k accounts and my IRA to establish a baseline return for a 75-25 allocation

then maintain same 75-25 allocation in wife's IRA, overweighting and underweighting sectors in hopes of getting a higher return than the other accounts.

I am verifying I am measuring return consistently so as I move forward with wife's IRA I know I am measuring returns and comparing them apples to apples.

THX!

In general what I do is set everything in January (which funds get what % of the deposits) based on prior year performance (using deposits to rebalance). In June I will make small adjustments in the sector funds if I think something is out of whack (for example I stopped contributions to financial services in August and increased health sciences based on relative performance).
__________________

__________________
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.
jIMOh is offline   Reply With Quote
Reply


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

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Gender (I'll show you mine, and you show me yours) Martha Forum Admin 34 06-27-2008 10:05 PM
Vanguard SPIA's....IRR=? RockOn FIRE and Money 30 05-03-2008 11:01 AM
IRR retire@40 FIRE and Money 23 10-03-2004 09:15 AM
12.8% IRR over 30 Years haha FIRE and Money 37 07-17-2004 05:37 AM

 

 
All times are GMT -6. The time now is 06:33 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.