ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community is currently in implementation of the new SSO experience starting at 5pm MDT. PLEASE DO NOT POST ANYTHING. For more information, read the blog.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Annualized Returns for time range

prveenk86kumar
6 - Meteoroid

Hi All - I am new to Alteryx  i am trying to do small calculation using alteryx. I want help in designing work flow. I have monthly data which i need to annualized returns every month - the formula used in excel is 

{=PRODUCT(C$2:C14+1)^(12/COUNT(C$2:C14))-1}

 

I need a summary of data in below format which is were i am stuck and this table will be updated on month to month basis as when the data gets added to input columns

 

 1 Mo (%)3 Mo (%)YTD (%)1YR (%)3YR (%)5YR (%)7 YR (%)10 YR (%)SI (%)
Input 1         
Input 2         
Input 3         
Input 4         

 

I want 1 month data to be latest data example= Nov month returns and rest with formula

 

prveenk86kumar
6 - Meteoroid

Any help on above topic

pedrodrfaria
13 - Pulsar

Hi @prveenk86kumar 

 

I'm not 100% sure I get exactly what you are looking for.

 

The workflow below will assign the value per different category for the time period for each input column.

 

pedrodrfaria_0-1613406363631.png

 

Please let me know if this was what you were looking for.

 

Pedro.

 

 

RolandSchubert
15 - Aurora
15 - Aurora

Hi @prveenk86kumar ,

 

hope I got you right. In my understanding, you want to calculate the Excel matrix formula for each input column and the defined period ranges based on the period you specified (currently Nov 2020).

 

2021-02-16_09-13-07.jpg

 

You could start with an Text Input tool to define your current period (if this is not the last month). Based on this, you could calculate the start periods for the ranges and add this identifier to your data. The next steps are intended to calculate the base value (e.g. product of returns for last year). Based on this results, you can calculate the final result for each period range (did this only for some of the ranges)

 

 Let me know if this is helpful.

 

Best,

 

Roland

prveenk86kumar
6 - Meteoroid

Thanks for the solution partial its working, I had couple questions or help which i would require in completing this workflow.

 

1) YTD - needs to be rolling 12months returns  which should be {=PRODUCT(C$2:C14+1) currently its not working could please help me on this?

3)Also i need to build formulas for 1y,3y,5y,7y and inception data using below formula, i tried using it but workflow is failing

1y ={=PRODUCT(C$2:C14+1)^(12/12-1}

3y ={=PRODUCT(C$2:C14+1)^(12/36)-1}

5y={=PRODUCT(C$2:C14+1)^(12/60)-1}

7y={=PRODUCT(C$2:C14+1)^(12/84)-1}

total return = {=PRODUCT(C$2:C14+1)^(12/COUNT(C:C))-1}

RolandSchubert
15 - Aurora
15 - Aurora

Hi @prveenk86kumar ,

 

I've added to calculation for missing period ranges. 

 

2021-02-17_08-08-56.jpg

Basically, the Multi-Row Formula tools calculate the first part of your Excel formula for the specific period ranges (i.e. the =PRODUCT(C$2:C14+1) part), while the formula tool does the "annualization". 

 

Have a look and let me know, if it works as expected.

 

Best,

 

Roland

 

 

prveenk86kumar
6 - Meteoroid

Formula tool is not calculating the annualization formula correctly, I was calculating the same in excel file i was not able to match back. I have attached the excel file for your example.

prveenk86kumar
6 - Meteoroid

Hi Roland, Can you helpme on the below solution

prveenk86kumar
6 - Meteoroid

Could you help me on below issue

Labels