01-02-2009, 03:33 PM   #21
Moderator

Join Date: Jul 2005
Location: Eee Bah Gum
Posts: 21,125
Quote:
 Originally Posted by jIMOh 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?
I now get the same as you using your calculations but I think it is wrong.

I used the Excel IRR function with the 1st set of figures, assuming the \$10741 deposit goes in 12 equal payments of \$895 and I get -50.16%.

When I use my simple calculation I get -50.17%.

My simple calculation is as follows:

Gain = ending balance - starting balance - deposits
Avg balance = start +(end-start)/2
Return = Gain/Avg balance

Gain = 33266 - 41202 - 10741 = -18677
Avg = 41202 + (33266 - 41202)/2 = 37234
Return = -18677/37234 = -50.16%
__________________

__________________
Retired in Jan, 2010 at 55, moved to England in May 2016

 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
01-02-2009, 03:44 PM   #22
Full time employment: Posting here.

Join Date: Jul 2008
Posts: 622
Quote:
 Originally Posted by jIMOh Yeah I think my forumula is wrong too.
There is definitely something wrong with your formula.
If your starting value was 100,000 and you deposited 20,000 and at the end of the year you had 124,000 (matching inflation so no loss)

Your formula gives a -32% yield.

If you want one years return on investment and had a constant investment amount That number is simple enough to calculate. First of all to get how much you made vs. how much you should have made you need to have your ending value divided by what the ending value would have been with only inflationary growth.

Here's what you need (annual rate of inflation)/12=r This will be your expected monthly inflation rate. Normally annual inflation is 3% or 0.03.

Then you need to break up how much money was added assuming you added an equal amount each month (deposits)/12=D This is your monthly deposits.

S= starting balance

E= ending balance

IRR=investment rate of return

So now here's the equation:

IRR= E/(S*(1+r)^12+D((1+r^12)-1)/r)

To get IRR as a percentage subtract 1 and multiply by 100.

For the numbers you gave:starting value 41202
ending value 33226
deposits 10741

IRR =-37%

Quote:
 Starting value = \$373,467, Ending value = \$287,890. Total deposits = \$25,383 your calculation gives 20% while website gives -29%
I get -29.87% looks like the website is rounding up to make your returns look a little less terrible.
__________________

__________________

01-02-2009, 03:58 PM   #23
Moderator

Join Date: Jul 2005
Location: Eee Bah Gum
Posts: 21,125
Quote:
 Originally Posted by Frugality_of_Apathy Alan: for your numbers Starting value = \$373,467, Ending value = \$287,890. Total deposits = \$25,383 your calculation gives 20% while website gives -29% I get -29.87% looks like the website is rounding up to make your returns look a little less terrible.
The Excel IRR calculation gives -33.44% for those figures, and my simple calculation gives -33.56%.

But I agree that your formula giving -29% taking into account inflation looks pretty good to me.
__________________
Retired in Jan, 2010 at 55, moved to England in May 2016

01-02-2009, 04:24 PM   #24
Full time employment: Posting here.

Join Date: Jul 2008
Posts: 622
Quote:
 Originally Posted by Alan The Excel IRR calculation gives -33.44% for those figures, and my simple calculation gives -33.56%. But I agree that your formula giving -29% taking into account inflation looks pretty good to me.
Yeah I have to use an 8.42% inflation to get your numbers.

I'm wondering how you are putting in your values for excels IRR. When I use your numbers in excel I get -23%

Why are you dividing your gain by the average (which is much easier to calculate as (33266 + 41202)/2= 37234 BTW) instead of the final value in your simple equation?
__________________

01-02-2009, 04:49 PM   #25
Thinks s/he gets paid by the post

Join Date: Apr 2007
Location: Milford, OH
Posts: 2,085
I am not using IRR within xcel.
I am trying to aproximate my IRR year over year, and also compare my IRR with the returns of the funds.
I will also soon do some 3 yr and 5 yr averaging once I have enough data.

I have seen the formula mentioned above
Quote:
 Gain = ending balance - starting balance - deposits Avg balance = start +(end-start)/2 Return = Gain/Avg balance
but it was different than this (something like numerator is current value+1/2 deposits and denominator was previous balance-1/2 deposits).

In this equation (also mentioned above), what is r? INFLATION, just caught that. If I assume no inflation (because I don't measure that right now), is that 0 or 1? 1 is my SWAG. I need to take inflation out of this so I can compare the returns to the mutual funds actual return numbers.
Quote:
 IRR= E/(S*(1+r)^12+D((1+r^12)-1)/r)
__________________
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.

01-02-2009, 05:12 PM   #26
Full time employment: Posting here.

Join Date: Jul 2008
Posts: 622
Quote:
 Originally Posted by jIMOh I am not using IRR within xcel. I am trying to aproximate my IRR year over year, and also compare my IRR with the returns of the funds. I will also soon do some 3 yr and 5 yr averaging once I have enough data. I have seen the formula mentioned above but it was different than this (something like numerator is current value+1/2 deposits and denominator was previous balance-1/2 deposits). In this equation (also mentioned above), what is r? INFLATION, just caught that. If I assume no inflation (because I don't measure that right now), is that 0 or 1? 1 is my SWAG. I need to take inflation out of this so I can compare the returns to the mutual funds actual return numbers.
R is the monthly rate of inflation. No inflation would be zero though this equation would break with that number since you divide by inflation at one point. The equation for no inflation is (E/(S+D)-1)*100=IRR

Or you could just do Gain/(Total Investment)*100=IRR

I don't know what that equation you're trying to use is but one thing I do know is that if you are trying to get return on investments the current value should never be the denominator under any circumstances unless you have it negative or multiplied by a negative.
__________________

__________________

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