Calculate remaining weighted average

Question

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 monthsLease paymentsRemaining WALT183 739,0011,38283 739,0011,34383 739,0011,3483 739,0011,26583 739,0011,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!!

Spreadsheet (remaining WALT).xlsx

fmvizcaino · Accepted Answer

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