How to calculate Annualized Rate of Return

[yalteryx]

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!

[AbhilashR]

@yalteryx - attached solution is an implementation of your formula. Not my best approach but the solution does work:)

Please mark this post as solution accepted if it satisfies your requirement.

[yalteryx]

Hi @AbhilashR Thanks for your prompt response! 

The calculations work great and certainly applies to the data set I provided.  However if I wanted to make it dynamic as opposed to hard coding the date, would that be possible? For example instead of using a ">12-31-2018" to apply the annualized calculation, is there a formula we can leverage based on "# of records or count > 12" per account?  

Thanks for your assistance!

[AbhilashR · Accepted solution]

Hi @yalteryx, I was somewhat curious to know if my solution would work for you or not, thank you for getting back to me! As for making the criteria more dynamic with something like counter < 12, you certainly can. I have modified my previous workflow and attached here. Let me know if this works.

[yalteryx]

tweaked it a little so it can handle multiple accounts and larger sets of data but it works for us! Thank you!