# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Cumulative Return

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

----------


## 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:

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

----------


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

--
Regards,
Fred


"Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
news:0678A894-1092-49D5-93B9-0916043CCD57@microsoft.com...
> 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:
>
>> 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!!!

----------

