Calculate IRR and show me your math

[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!

 

[FIREd]

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).

 

[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

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!

 

[jIMOh]

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!

 

[target2019]

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.

 

[kaneohe]

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.

 

[jIMOh]

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).