Alteryx alternate to excel solver for minimizing end result

[PreetiVatnani]

Hi,

I am working on a sheet with interconnected data in columns. it is an amortization schedule where rate needs to be determined.

Beginning liability and Installment amount is provided. Based on this, interest and end liability is calculated for that month.

We keep calculating for all months(in each). Total tenure is provided. In the end we use excel solver to change Interest amount to Zero for the last month. Based on this, rate is calculated and all amounts are adjusted.

I am struggling to recreate this in alteryx.

Given parameters are-

Given parameters
schedule_start_date	schedule_end_date	Payment
1-Mar-22	31-Aug-22	45,501
1-Sep-22	31-Aug-27	72,267.00
1-Sep-27	31-Aug-32	75,455.25

First, I expanded the table to get monthly payment for each month

And now the formula in E5 is =(C5+D5)*($I$1/365/100*(A5)) where I1 is the rate.

We want to select such a rate so that we bring Interest payment to zero in the last installment. This can be done using a solver in excel. where i chose to get value of zero for last interest and variable is selected as rate.  But how to do it in alteryx

[Matthew]

i think you could do this by creating a macro that iterates through a range of interest rates, and returns the minimum.. basically:

- create a workflow that does the basic calculation

- put that workflow inside a macro

- make the macro loop through interest values (stepping 0.01% with each iteration)

- and for each run-through, return the interest rate and the value for the last payment

after the macro runs, you just filter the output for the interest rate where the last payment result ~= 0

[PreetiVatnani]

Thanks @Matthew for your response, but could you share some sample as a jump start for me.

Wouldnt that be 2 loops because all the rows are calculated depending on a row prior to itself

Also, how many times should the loop run.. The excel solver returns values with 10-15 decimal points too. 

[Matthew]

since i assume youre looking at one case at a time, you could probably run tens of thousands of scenarios to optimise the interest rate in a fairly short period of time

that said, building the macros would probably take a bit of time to do..

alternatively, have you looked at the built in finance functions in alteryx? i dont think this example gets you all the way to your solution (in fact i think it's not calculating the principal paydown correctly).. but maybe it's a start?