Goal Seek functionality in PHP

Question

I want to implement functionality in PHP similar to what is available as Goal Seek in Microsoft Excel.

Is such a thing possible to implement in PHP?

Here is the story in detail

We are writing a Personal Loan Management application for a financial company. They are at present using Excel sheets to do all kind of load, EMI, etc. calculations. Now they want to implement this online so that their staff can access it from any where.

In this we have to program interest calculation. This interest calculation is very complicated but suffice it to be said that to Interest is calculated in reducing method but, in such a way that it is always a bit less then flat interest.

Say for example someone takes a loan of 10000 at the rate of 10% for 12 months (1 year).

In this case Flat interest will be 1000 (10000 * (10/100)).

We have to show borrower interest calculations in reducing method and the total interest in reducing method should always be a bit less then Flat Interest.

For this we have to calculate a rate of interest which is actually higher than 10%. To arrive at the exact value they(client) are currently using Goal Seek functionality as this is very quick.

In the online software we are developing we have already implement a logic for performing this calculations but it is taking a very very long time as we have to execute a loop which at time iterates more than 100000 time to arrive at an acceptable value.

I hope I have explained the whole situation properly.

Answer 1

As PHP is Turing-complete, then yes, it is possible. Of course you'll need to implement your own algorithms and user interface for this. Please elaborate more on the design of such feature, how you see it, then maybe I will be able to give you some further pointers.

Answer 2

If you have the function to calculate and you need to run it 100K times then your data feed for the function is not optimal. You should feed the function firts with coarse grained data (big gaps between values) then finer ones.

Of course you'll need to code a bit more sophisticated control flow than a biiiig while cycle.

Simplest method (to code)

• you are at the high side of the goal
• feed the data with rate*0.5 and see if it will go to the low side
• equal: you are ready; lower than the goal: feed with rate*0.75 still higher: feed with rate*0.25

Repeat until it converges. Just keep in mind you want to elimiate exatly 1/2 of the remaining values.

It would be fast.

Answer 3

There is a bit (a little bit) about what Goal Seek does here, on the Microsoft support site. That might be enough for you to get started with.

In your case, an even faster-performing method would be to use something like Newton-Raphson, since you have a known formula to start with. If the wikipedia page doesn't help sufficiently, now you know what to google for you may be able to find a nice php walkthrough.