Calculate remaining weighted average

[Afournier]

Hi everyone, 

In excel I am using a formula to calculate the remaining weighted average of a lease terme. 

The spreadsheet look like this: 

No of months	Lease payments	Remaining WALT
1	83 739,00	11,38
2	83 739,00	11,34
3	83 739,00	11,3
4	83 739,00	11,26
5	83 739,00	11,22

The formula in excel for column "Remaining WALT" is "=(SUMPRODUCT(A2:$A$261;B2:$B$261)/SUM(B2:$B$261)-A2)/12". 

In this specific case, I have a lease with 260 month. 

First, is their something I can do in Alteryx to replicate this formula? Also to make it a bit more complex, lets say I have 1000 other template/spreadsheet like those with different terms (different number of month) so I need it to work for every single lease schedule. 

For information, I have built an iterative macro in my Alteryx workflow so I would need a solution to integrate in that macro 🙂 

I have attached the spreadsheet for reference. 

Thanks for any help!! 

[fmvizcaino · Accepted solution]

Hi @Afournier ,

Attached is an example showing how to get that done! I have also added an example with 2 different files showing that it is also possible to work with a bunch of files. For many types of files, you can automatically get the file name from the input data tool's configuration tab on the left.

Take a look and let me know if that works for you.

Best,

Fernando Vizcaino