Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Rates of return calculation
Old 01-03-2008, 11:49 AM   #1
Thinks s/he gets paid by the post
jIMOh's Avatar
 
Join Date: Apr 2007
Location: Milford, OH
Posts: 2,085
Rates of return calculation

I am working on 2007 IRR.

The equation I have (from other forums, similar to this) is

([starting value-.5*deposits]/[ending value+.5*desposits])-1

is this the formula you use?
__________________

__________________
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 01-03-2008, 11:53 AM   #2
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jun 2005
Posts: 8,616
You gotta use the XIRR() function of excel.
XIRR stuff
__________________

__________________
LOL! is offline   Reply With Quote
Old 01-03-2008, 11:57 AM   #3
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 LOL! View Post
You gotta use the XIRR() function of excel.
XIRR stuff
my function wizard does not have XIRR

running excel 2003.

the link does not show the math behind the calculation, can you display it here? 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 01-03-2008, 11:59 AM   #4
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jun 2005
Posts: 8,616
I don't know the math. I'm sure you can find out how to download/install the XIRR() function with a little typing
__________________
LOL! is offline   Reply With Quote
Old 01-03-2008, 12:01 PM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
audreyh1's Avatar
 
Join Date: Jan 2006
Location: Rio Grande Valley
Posts: 16,464
For ROI (that's not the same as IRR, but it's what I use when looking at my YTD performance), Quicken does the calculation like this:

[Ending Value + all withdrawals]/[Starting Value + all deposits] - 1

This is a somewhat conservative approach, as if you made some deposits late in the year, the remainder of the portfolio would have supplied most of the return (assuming a linearly positive year), but it's good enough for me!

Audrey
__________________
audreyh1 is offline   Reply With Quote
Old 01-03-2008, 12:18 PM   #6
Thinks s/he gets paid by the post
SecondCor521's Avatar
 
Join Date: Jun 2006
Location: Boise
Posts: 2,402
Quote:
Originally Posted by jIMOh View Post
my function wizard does not have XIRR

running excel 2003.

the link does not show the math behind the calculation, can you display it here? thx
I'm pretty sure the math for XIRR is not a simple equation. I believe Excel starts with a guess (of 10%, unless you give it a different guess) and tweaks it slightly up or down until it gets the correct answer.

2Cor521
__________________
"At times the world can seem an unfriendly and sinister place, but believe us when we say there is much more good in it than bad. All you have to do is look hard enough, and what might seem to be a series of unfortunate events, may in fact be the first steps of a journey." Violet Baudelaire.
SecondCor521 is offline   Reply With Quote
Old 01-03-2008, 12:45 PM   #7
Thinks s/he gets paid by the post
Rustic23's Avatar
 
Join Date: Dec 2005
Location: Lake Livingston, Tx
Posts: 3,624
From Microsoft:
Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent. If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned. The rate is changed until:
where:
di = the ith, or last, payment date.
d1 = the 0th payment date.
Pi = the ith, or last, payment.

Good Luck! I use an HP calculator.
__________________
Rustic23 is offline   Reply With Quote
Old 02-01-2008, 04:23 PM   #8
Recycles dryer sheets
 
Join Date: Dec 2007
Posts: 102
Quote:
Originally Posted by audreyh1 View Post
For ROI (that's not the same as IRR, but it's what I use when looking at my YTD performance), Quicken does the calculation like this:

[Ending Value + all withdrawals]/[Starting Value + all deposits] - 1

This is a somewhat conservative approach, as if you made some deposits late in the year, the remainder of the portfolio would have supplied most of the return (assuming a linearly positive year), but it's good enough for me!

Audrey
Audrey - Is it ending value PLUS or MINUS all withdrawals. I have been staring at this and its starting to go fuzzy. In other words, does my ending balance go up or down after I factor in withdrawals?
__________________
podey is offline   Reply With Quote
Old 02-01-2008, 05:23 PM   #9
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
audreyh1's Avatar
 
Join Date: Jan 2006
Location: Rio Grande Valley
Posts: 16,464
PLUS (ending balance goes up).

You have to give yourself credit for withdrawals, otherwise your ROI is artificially lowered.

Audrey
__________________
audreyh1 is offline   Reply With Quote
Old 02-02-2008, 05:12 AM   #10
Dryer sheet wannabe
 
Join Date: Mar 2007
Posts: 22
Here are some handy calculations and approximations that make sense to me. Maybe someone else will find them useful. Or maybe not. DW claims that I do everything backward.:confused:

S = Starting portfolio value.
E = Ending portfolio value
F = net cash Flow = deposits - withdrawals

F must include all money moving in or out of the portfolio. I need to track this anyway, since this is the Withdrawal part of Safe Withdrawal Rate.

G = Gain = E-S-F (Will be negative in the case of a loss.)

If you made all deposits or withdrawals at the end of the period, your percentage gain or loss, also called Return On Investment (ROI), would be G / S. If you made all deposits or withdrawals at the start of the period ROI would be G / (S+F). If your deposits and/or withdrawals are made evenly over the period, a reasonable approximation is

ROI = G / (S+(F/2)).

To obtain an annualized rate of return (IRR) from ROI the formula is

IRR = ((1+ROI)^(d/365)) - 1

Where d = number of days between the start date and the end date and "^" is used as in spread sheet functions to mean "to the power of".

ExHermit
__________________
ExHermit is offline   Reply With Quote
Old 02-02-2008, 07:52 AM   #11
Thinks s/he gets paid by the post
saluki9's Avatar
 
Join Date: Feb 2005
Posts: 2,032
The most common method of performance calculation used in the investment industry is the Modified Dietz Method

Modified-Dietz Method
r(T) = {MV(T)-MV(0)-sum[C(t)]}/{MV(0)+sum[w(t)*C(t)]}
r(T)... Modified Dietz Return
MV(T)... Ending market value
MV(0)... Beginning market value
C(t)... Net contribution occurring on day t
w(t)... weight of the net contribution on day t...
w(i) = {T - t} / T
T... Total number of days
t... day the net contribution occurs
The Modified Dietz method assumes that net contributions are invested at the end of the respective day they occur.
__________________
saluki9 is offline   Reply With Quote
Old 02-02-2008, 07:59 AM   #12
Moderator
Alan's Avatar
 
Join Date: Jul 2005
Location: Eee Bah Gum
Posts: 21,095
Quote:
Originally Posted by ExHermit View Post
Here are some handy calculations and approximations that make sense to me. Maybe someone else will find them useful. Or maybe not. DW claims that I do everything backward.:confused:

S = Starting portfolio value.
E = Ending portfolio value
F = net cash Flow = deposits - withdrawals

F must include all money moving in or out of the portfolio. I need to track this anyway, since this is the Withdrawal part of Safe Withdrawal Rate.

G = Gain = E-S-F (Will be negative in the case of a loss.)

If you made all deposits or withdrawals at the end of the period, your percentage gain or loss, also called Return On Investment (ROI), would be G / S. If you made all deposits or withdrawals at the start of the period ROI would be G / (S+F). If your deposits and/or withdrawals are made evenly over the period, a reasonable approximation is

ROI = G / (S+(F/2)).

To obtain an annualized rate of return (IRR) from ROI the formula is

IRR = ((1+ROI)^(d/365)) - 1

Where d = number of days between the start date and the end date and "^" is used as in spread sheet functions to mean "to the power of".

ExHermit
This is similar to what I use except I estimate avg monthly balance.
G = Gain = E-S-F (Will be negative in the case of a loss.)
AvgBal = S+(E-F)/2

ROI = G/AvgBal
__________________
Retired in Jan, 2010 at 55, moved to England in May 2016
Now it's adventure before dementia
Alan is offline   Reply With Quote
Old 02-02-2008, 04:43 PM   #13
Dryer sheet wannabe
 
Join Date: Mar 2007
Posts: 22
Quote:
Originally Posted by saluki9 View Post
The most common method of performance calculation used in the investment industry is the Modified Dietz Method

Modified-Dietz Method
r(T) = {MV(T)-MV(0)-sum[C(t)]}/{MV(0)+sum[w(t)*C(t)]}
r(T)... Modified Dietz Return
MV(T)... Ending market value
MV(0)... Beginning market value
C(t)... Net contribution occurring on day t
w(t)... weight of the net contribution on day t...
w(i) = {T - t} / T
T... Total number of days
t... day the net contribution occurs
The Modified Dietz method assumes that net contributions are invested at the end of the respective day they occur.
We are on the same page here. If it is assumed that all deposits and withdrawals are made in one lump sum in the middle of the period then the formula I posted is numerically identical to the Modified Dietz Return formula you posted

Clearly, the Modified Dietz Return is more accurate if this is not a reasonable assumption. The more the cash flow is skewed toward one end or the other of the period, the more error my simplifying assumption introduces.

Even the Modified Dietz Return is slightly off, in that it does not quite account correctly for compounding effects. I don't think that there is a closed function that is 100% accurate, hence the use of XIRR iterative numerical approximation.
__________________
ExHermit is offline   Reply With Quote
Old 02-02-2008, 05:24 PM   #14
Dryer sheet wannabe
 
Join Date: Mar 2007
Posts: 22
Quote:
Originally Posted by Alan View Post
This is similar to what I use except I estimate avg monthly balance.
G = Gain = E-S-F (Will be negative in the case of a loss.)
AvgBal = S+(E-F)/2

ROI = G/AvgBal
I'm not sure that I follow your computations, Alan.

Let's take an example:

Starting value = S = $100
Ending value = E = $125
$10 was deposited half way through the period, F =10$

You, I and saluki9 all agree on the numerator, G = E - S - F = $15

Using your formula, the denominator would be
AvgBal = S+(E-F)/2 = 100+(125-10)/2 = 100+(115)/2 = 100+57.5 =157.5

and ROI would be G/AvgBal = 15/157.5 = 9.52%

I would use a denominator of S+(F/2) = 100+(10/2) = 105

giving a ROI of 15/105 = 14.29%

We have made $15, having put in a total of $110, $100 at the start plus $10 halfway through. I would think that the rate of return would be at least 15/110 = 13.64%. Allowing some credit for the fact the the $10 deposit was only earning returns for half the period, 14.29% seems to me more reasonable than 9.25% as an estimated rate of return.

Am I missing something?
__________________
ExHermit is offline   Reply With Quote
Old 02-02-2008, 07:38 PM   #15
Thinks s/he gets paid by the post
 
Join Date: Jan 2006
Posts: 2,928
Quote:
Originally Posted by jIMOh View Post
my function wizard does not have XIRR

running excel 2003.

the link does not show the math behind the calculation, can you display it here? thx
Perhaps your software has the IRR function?
ExcelTips: Using the IRR Function

I guess you'd set up the time scale as months and the IRR would be the
monthly return so probably not as accurate as XIRR. I'm assuming the annual return would be the monthly return compounded for 12 mos and you could set up the spreadsheet to calculate that.
__________________
kaneohe is offline   Reply With Quote
Old 12-23-2008, 03:25 PM   #16
Thinks s/he gets paid by the post
jIMOh's Avatar
 
Join Date: Apr 2007
Location: Milford, OH
Posts: 2,085
Bumping this with a question.

Using this formula:
([starting value-.5*deposits]/[ending value+.5*desposits])-1

My 401k IRR is -29.6%
According to my 401k provider my loss was twice that (-58.8%).

**edit** this 401k transferred custodians from Vanguard (old employer) to current employer (self maintained plan with institutional funds)- could this be causing the issue?

Thoughts?
__________________
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-23-2008, 04:22 PM   #17
Moderator
Alan's Avatar
 
Join Date: Jul 2005
Location: Eee Bah Gum
Posts: 21,095
Quote:
Originally Posted by jIMOh View Post
Bumping this with a question.

Using this formula:
([starting value-.5*deposits]/[ending value+.5*desposits])-1

My 401k IRR is -29.6%
According to my 401k provider my loss was twice that (-58.8%).

**edit** this 401k transferred custodians from Vanguard (old employer) to current employer (self maintained plan with institutional funds)- could this be causing the issue?

Thoughts?
I think your formula is wrong. I just tried your formula on my 401k data for the year and compared it with my 401k website and also my own IRR calculation (using the Excel function).

Using your calculation I get a number wildly different from mine.

Am I using your formula wrong?

Starting value = $373,467, Ending value = $287,890.
Total deposits = $25,383

your calculation gives 20% while website gives -29%
__________________
Retired in Jan, 2010 at 55, moved to England in May 2016
Now it's adventure before dementia
Alan is offline   Reply With Quote
Old 12-23-2008, 08:24 PM   #18
Moderator Emeritus
 
Join Date: May 2007
Posts: 11,038
Is your 401K provider quoting you an IRR?

As I understand it, the XIRR formula is based on cash flow, i.e. how much went into / out of the account and when did that transaction occur. XIRR calculations give you an annualized rate of return, so your account has to be opened at least 1 year for your results to be accurate.

So my guess is, if your transferred your 401K from one institution to another during the last 12 months, then the IRR quoted by the new institution is probably meaningless because they have less than 1 year worth of cash flow data to work with.
__________________
FIREd is offline   Reply With Quote
Old 01-02-2009, 02:17 PM   #19
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 Alan View Post
I think your formula is wrong. I just tried your formula on my 401k data for the year and compared it with my 401k website and also my own IRR calculation (using the Excel function).

Using your calculation I get a number wildly different from mine.

Am I using your formula wrong?

Starting value = $373,467, Ending value = $287,890.
Total deposits = $25,383

your calculation gives 20% while website gives -29%
Yeah I think my forumula is wrong too.

starting value 41202
ending value 33226
deposits 10741
I have the equation as [33226/ (41202+10741)]-1= -36.03%

or
starting value 167,685
ending value 120,496
desposits 27720

Which is [120496/ (167685+27720]-1= -36.29%

Do both equations look accurate now?
__________________
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 01-02-2009, 03:19 PM   #20
Thinks s/he gets paid by the post
Free To Canoe's Avatar
 
Join Date: May 2008
Location: Cooksburg,PA
Posts: 1,738
Quote:
Originally Posted by ExHermit View Post
Here are some handy calculations and approximations that make sense to me.....

...ROI = G / (S+(F/2)).

ExHermit
Thanks ExHermit,

I do the same except I just use G/S for the ROI.

Yours is better, of course.

Free
__________________

__________________
Free To Canoe 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
Nominal rates of return & SD for asset classes walkinwood FIRE and Money 5 04-26-2007 11:54 AM
calculation help pinkmali Young Dreamers 7 08-02-2006 04:35 PM
FV calculation ferco FIRE and Money 6 12-31-2005 07:16 AM
SS Calculation jodz FIRE and Money 14 08-31-2004 09:07 PM

 

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