How can I perform a “Goal Seek” function in Knime similar to Excel?
For example, if I perform a calculate that shows that the $1m purchase price of an asset will return a 10% IRR, what do I need to do to find out what the purchase price needs to be if I want a 15% IRR?
I think the Parameter Optimizer is the way to go but I have no idea how to use it.
I hope this makes sense? Thanks in advance
Hi @JaySmith123 , yes I think Parameter Optimization Loop is likely the way to go.
This post from @elsamuel gives an example which may give you some pointers.
Thanks I’ll definitely take a look.
This is the workflow I’ve created just in case you are able to help me edit it. Really appreciate your response thank you.
For example, in Project 1, the IRR is 20.1% at the purchase price of 63,750,000. What would this purchase price need to be in order to get an IRR of say 25%. In excel, the updated purchase price is 55,488,050 so I am expecting something around this figure. Any help would be welcomed 
Hi @JaySmith123 , are you trying to seek an IRR of 25% on all of the rows, or is it going to vary from one row to the next?
Firstly, I’m no expert in goal-seeking with Parameter Optimization Loop so there may be better suggestions or solutions.
As far as I can see, the Parameter Optimization Loop is going to have to work on your data set one row at a time, so it will itself need to be in within a loop. I think it also needs to be given a range of values, so for each row you’d have to give an idea of a start and end range. So if it were in a loop, maybe you could base the range on “so much above or below” the current purchase value in the table.
I have just looked at how you could use the PO Loop against the first row, based on a range from 10,000,000 to 100,000,000.
It adjusts the Purchase Price to each value that it tries. A math formula after your IRR calculation determines the difference between the returned IRR and 0.25 (25%). The PO Loop End looks for this difference being the minimum (hopefully zero).
At the end, the top output from the Loop End returns the “best” value, which is then appended back onto the row.
You can play with the different settings of the Loop Start which will change the number of iterations attempted, and the approach taken.
In the current config, it returned 55,488,751, which when plugged into your IRR calculator returns 25%, so in the right ballpark of your Excel result.
The attached workflow doesn’t contain your IRR component due to forum size restrictions, and as you already have that, there didn’t seem much point in creating another remote file share, but it contains the other “pieces” so you can add your component back into it to try it out. (And you’ll need to move the data folder back into this workflow so it has your jar files available). Alternatively copy and past the content of this workflow onto your other workflow. That may be easier.
Example Workflow - Goal Seek IRR.knwf (31.3 KB)
I hope it gives some pointers and helps move you in the right direction.
Hi @JaySmith123,
A tip here, you can use the math formula node or column expression to make some calcs. Like financial methods, you can bring there the formula conception and apply the formula to bring the result too.
Excel have some functions to it, but if you know the expression to be applied, you can build it, keep it simple.
Can you try it?
Tks, Denis
This is amazing thank you I will definitely have a look at this and let you know how it goes
Yes I have this goal seek formula in excel working fine in excel. I can do 1000 rows in a matter of seconds using just VBA but I wanted to do all of my analysis in Knime but it appears to be very complicated and takes very long. I will try your suggestion and see how I get on. I really appreciate your feedback
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
