Need Formula

  • Thread starter Thread starter dbrew4
  • Start date Start date
D

dbrew4

I need a formula for a calculation of the following:

The value of $1.00 after a loss of 10.5% for every year for three year
and a gain of 9.5% for one year. Note: the percentages have to be us
as a cell location!!

eg: Value of $1.00 minus the contents of cell A3 for every year fo
three years plus the addition of the contents of cell A5 for one year.

I'm trying to figure which of my mutual funds has done the best an
worst based on the last three years and the current year to date base
on percentagess. Hope I explained it correctly.
Thank
 
Hi,

Check out Excel's pv and fv functions. You'll need two of whichever you
choose (perhaps nested), since the last year's result is dependent on the
1st 3 years' results.

Cheers
 
dbrew4,

You have to be careful with "mutual fund" advertising. And if you are in
one of the funds that has allowed "timing" by hedge funds and where Spitzer
is going after them, you have my sympathy.

There are two types of averages that people often use. One is arithmetic.
Simply add all up your percentage returns and divide by the number of years.
I don't like this approach, but it is often used. Why don't I like it.

Say you have $100 year 1, a loss of 50% year 2 to yield $50 remaining, and
100% gain in year 3 to get you back to a $100. The arithmetic average is
(-50%+100%)/2 or an average 25% per year. Yet you have gained 0 dollars.

I prefer the geometric average which is what you are using below.

You could do this all one one cell or you can use several. Or you can use
Excel financial functions. This is simple enough to just do yourself
without using financial functions.

A1=$1
B1= -10.5% year1
C1= -10.5% year2
D1= -10.5% year3
E1= +9.5%

In Cell A2 you can write the following formula

=A1*(1+B1)*(1+C1)*(1+D1)*(1*E1)

You will get 6.81% (remember to format your cell as a %, or just realize
that 0.0681 is 6.81%)

That is one method.

Or you could write....
=A1*(1+B1)^3*E1 (I am using the fact that B1=C1=D1
6.81%

If you use the first method, you can change your returns for your different
mutual funds and see how you have done.

I hope this helps.

Best regards,
Kevin
 
Whooopsss....

Sorry too early...

Let's try that again...

A1=$1
B1= -10.5% year1
C1= -10.5% year2
D1= -10.5% year3
E1= +9.5%

In Cell A2 you can write the following formula

=A1*(1+B1)*(1+C1)*(1+D1)*(1+E1)

You will get $0.785
That is one method.

Or you could write....
=A1*(1+B1)^3*(1+E1) (I am using the fact that B1=C1=D1
$0.785

Sorry 'bout that.

I hope this helps.

Best regards,
Kevin

 
Back
Top