Hi all,
I have been playing around with a function all day and it is driving me nuts.
My goal is to automatically calculate the volatility of a warrant's (equity) price over time as it approaches the end of its term (in this case 60 months).
I want to create a formula that takes the STDEV of the Compounded Rate of Return and multiply that by the square root of the period (12 months). But I want to automate it somewhat by allowing me to copy the formula to other cells over time.
PROBLEM: For each new time period that I add, I need to subtract two periods off the backend of the calculation. So, the range essentially shifts down two, but increases by one as well at the bottom.
Example: Download the workbook here: DivShare File - Warrant fair value calculation - shifting.xls
Range I11:I79 contains Rate of Return percentages over time
I want to calculate volatility as STDEV(PERIOD)*SQRT(12) every month until the warrant expires in 60 months.
In my example, you can see I have tried to use the following formula with some success in cell N72:
=STDEV(INDEX(_RR,COLUMNS(N72:$O72)):INDEX(_RR,COLUMNS(N72:$O72)+59))*SQRT(12)
where _RR is a dynamic named range referring to:
=OFFSET(Volatility!$I$13,0,0,COUNTA(Volatility!$I13:I80),1)
I need to be able to copy and paste this function (and have it automatically fill in the correct formula) in cells N12:N79. I also need to be able to add new entries to the end.
Thanks!
Any help is much appreciated.
I have been playing around with a function all day and it is driving me nuts.
My goal is to automatically calculate the volatility of a warrant's (equity) price over time as it approaches the end of its term (in this case 60 months).
I want to create a formula that takes the STDEV of the Compounded Rate of Return and multiply that by the square root of the period (12 months). But I want to automate it somewhat by allowing me to copy the formula to other cells over time.
PROBLEM: For each new time period that I add, I need to subtract two periods off the backend of the calculation. So, the range essentially shifts down two, but increases by one as well at the bottom.
Example: Download the workbook here: DivShare File - Warrant fair value calculation - shifting.xls
Range I11:I79 contains Rate of Return percentages over time
I want to calculate volatility as STDEV(PERIOD)*SQRT(12) every month until the warrant expires in 60 months.
In my example, you can see I have tried to use the following formula with some success in cell N72:
=STDEV(INDEX(_RR,COLUMNS(N72:$O72)):INDEX(_RR,COLUMNS(N72:$O72)+59))*SQRT(12)
where _RR is a dynamic named range referring to:
=OFFSET(Volatility!$I$13,0,0,COUNTA(Volatility!$I13:I80),1)
I need to be able to copy and paste this function (and have it automatically fill in the correct formula) in cells N12:N79. I also need to be able to add new entries to the end.
Thanks!
Any help is much appreciated.
