Hello there ! hoping you could extend some help , went through analytical functions like RANK partitions, and exp(ln), but still confused for this one.
I need to calculate the annualized return for investments, so currently i have monthly rate of return for stocks in a table. A which has ticker id ,month begin date and ,return value. I can use this return value and first need to calculate the cumulative return and then calculate annualized rate of return for time periods like 1 year, 3 years etc and store this output in a table for each stock . need to replicate the test data i have with below calculations.
MON_RTN_AT
MOM_RTN_AT*0.01)+1 as cumulative_return_current_month,
cumulative_return_current_month+(cumulative_return_current_month*(MON_RTN_AT for next record *0.01) and so on like this for past 1 year, or 3 years etc.
Month end ROR Cumulative return Annualized return
| 03-2015 | 2.572049 (Q1) | 1.02572049(R) | =>This is first calc for say current month say R1 |
| 02-2015 | 4.495951(Q2) | 1.07183638R2 | this uses (above R1+(R1*(Q2*0.01)) |
| 01-2015 | 5.696576 | 1.13289435 | =R2+(R2*(Q3*0.01)) |
| | | |
Is there any easy way to manipulate this ? any pointers or suggestions would be very helpful. thanks !