Cumulative Return

N

Natalie

I have a series of monthly return and need to create a formula that can
compute it's cumulative return in the following manner:
(1+return1)*(1+return2)*(1+return3))*(1+return4)*(1+return5)

Subsequently, I would need to compute the "average" return by creating
another formula in the following manner:
((1+return1)*(1+return2)*(1+return3)*(1+return4)*(1+return5))^(1/n) – 1,
where "n" should be the "count" of months.

Appreciate any assistance. Thank you!!!
 
M

Marcelo

Natalie,

You should create a line to add 1 for each return than, the formula could be:
(assuming that the returns are between column C and G and line( +1) is 30)

Cumulative = "=product(c30:g30)-1"
Average = "=product(c30:g30)^(1/count(c30:g30))-1"

Hope its hekps

Marcelo - Brazil


"Natalie" escreveu:
 
F

Fred Smith

As Marcelo said, the best way to calculate the total return is to create another
column with =1+return in it. Then you can use the Product function. If this is a
series which gets added to every month, you can make the formula more easily
extendible by using:

=Product($c$30:g30)-1

That way, when you extend the formula to column h, it will copy without
requiring editing.

To calculate the average, I'm sure you want the average *compound* return. The
easiest way is to use the Rate function, as in:

=Rate((column(g30)-column($c30)+1),0,-1,1+totalreturn)
 
Top