community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE

Using R script to find a solver / or using macro to find a solver

Highlighted
Meteor

Hi,

 

I have compiled a table like below (data is attached as well)

 

Capture.PNG

 

 

 

The table is based on the 4 criteria on the very top.

Accrual amt = beginning amount * actual yield * 1/4

net payment = principal amount * face rate * 1/4 (except for the last pmt which pay the net payment + principal amount together)

Ending amount = Beginning Amount + Accrual Amt - Net Payment

 

The ultimate goal is to get the ending amount equals to zero to find out a target actual yield which right now equals to 10% (in excel then we use goal seek function but I want to replicate this process in alteryx):

 

 Capture.PNG

 

 

I have searched previous posts regarding goal seek replication,  and it seems that with the table set up I can run some R script to solve a function.  A regular alteryx workflow would be best, but I would assume a r script solution would be easier and neat.  

 

Here post something i tried.  i have very limited coding experience, and the below script is based on some research i did.

 

 

Capture.PNG

However, the result is close but not correct:

 

 Capture.PNG

 

 

see the expected result tab in the attached excel, second tab, the target yield calculated should be 9.88%, could someone point out what went wrong with my code?  have been struggling on this for days.

 

Thanks,

 

Blake

 

Meteor

 

 

Labels