Hello Community,
I am looking to calculate annualized rate of return in Alteryx. I have been able to calculate cumulative returns using the 'Multi-Row Formula' Tool but need help getting to the last step for annualized returns (if i have monthly returns for 2 years for example - the formula changes after the 2018 year-end).
The formula used in excel is {=PRODUCT(C$2:C14+1)^(12/COUNT(C$2:C14))-1}
Need help calculating this one in Alteryx.
Below is an example and how it is calculated in Excel spreadsheet (fictitious account/numbers used).
| Account Number | Date | Gross Return | Cumulative Gross Return | Annualized Gross Return | Cumulative Excel Formula | Annualized Excel Formula |
| 123 | 1/31/2018 | -0.963% | -0.963% | -0.96% | | |
| 123 | 2/28/2018 | -1.131% | -2.084% | -2.08% | | |
| 123 | 3/31/2018 | 0.588% | -1.508% | -1.51% | | |
| 123 | 4/30/2018 | -0.488% | -1.989% | -1.99% | | |
| 123 | 5/31/2018 | 0.491% | -1.507% | -1.51% | | |
| 123 | 6/30/2018 | -0.039% | -1.546% | -1.55% | | |
| 123 | 7/31/2018 | 0.247% | -1.303% | -1.30% | | |
| 123 | 8/31/2018 | 0.501% | -0.808% | -0.81% | | |
| 123 | 9/30/2018 | -0.345% | -1.150% | -1.15% | | |
| 123 | 10/31/2018 | -1.089% | -2.227% | -2.23% | | |
| 123 | 11/30/2018 | 0.307% | -1.927% | -1.93% | | |
| 123 | 12/31/2018 | 1.572% | -0.385% | -0.38% | {=PRODUCT($C$2:C13+1)-1} | {=PRODUCT(C$2:C13+1)-1} |
| 123 | 1/31/2019 | 1.785% | 1.393% | 1.28% | {=PRODUCT($C$2:C14+1)-1} | {=PRODUCT(C$2:C14+1)^(12/COUNT(C$2:C14))-1} |
| 123 | 2/28/2019 | 0.106% | 1.500% | 1.28% | {=PRODUCT($C$2:C15+1)-1} | {=PRODUCT(C$2:C15+1)^(12/COUNT(C$2:C15))-1} |
| 123 | 3/31/2019 | 2.010% | 3.540% | 2.82% | | |
| 123 | 4/30/2019 | 0.092% | 3.635% | 2.71% | | |
| 123 | 5/31/2019 | 1.423% | 5.110% | 3.58% | | |
| 123 | 6/30/2019 | 1.500% | 6.687% | 4.41% | | |
| 123 | 7/31/2019 | 0.283% | 6.988% | 4.36% | | |
| 123 | 8/31/2019 | 2.325% | 9.476% | 5.58% | | |
| 123 | 9/30/2019 | -0.173% | 9.286% | 5.21% | | |
| 123 | 10/31/2019 | 0.271% | 9.582% | 5.12% | | |
| 123 | 11/30/2019 | 0.020% | 9.604% | 4.90% | | |
| 123 | 12/31/2019 | 0.127% | 9.743% | 4.76% | | |
Thanks, any help would be greatly appreciated!
