Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cumulative Return
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cumulative Return
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!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cumulative Return
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" wrote in message ... 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
annualized return calculation for the purpose of portfolio performance evaluation | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
Excel - return a picture or range rows as the result of a formula | Excel Worksheet Functions |