Alteryx alternate to excel solver for minimizing end result
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Optimization
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
