Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Annualized Returns for time range

prveenk86kumar
7 - Meteor

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

 

8 REPLIES 8
prveenk86kumar
7 - Meteor

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
16 - Nebula
16 - Nebula

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
7 - Meteor

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
16 - Nebula
16 - Nebula

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
7 - Meteor

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
7 - Meteor

Hi Roland, Can you helpme on the below solution

prveenk86kumar
7 - Meteor

Could you help me on below issue

Labels