We often come across problem of optimisation. And that too multivariate optimisation. That is, the problem will be dependent on more than one variables. In order to find out the solution by some numerical methods we have some methods. The solution for the problem can be obtained by numerical techniques. Microsoft excel comes with an add-in for numerical solution of such problems.
1. Go to 'Excel Options'
2. Select Add-Ins
3. At the bottom select 'Excel Add-ins' and click 'Go'
4. Now from the pop up, select 'Solver Add-in' and click 'Ok'
Now in the 'Data' tab, solver will appear.
Two types of methods are available in Microsoft excel. GRG Nonlinear and Evolutionary. Since the method requires to be implemented with its default user interface, it may be difficult to incorporate it as a part in a whole VBA code or as a particular step in a loop of say, 1000 cycles. We will feel to have the process done with the call of a function. We will now see one particular method of doing that. Nelder mead algorithm. A detailed description of the method and a working VBA implementation is given in this reference. 'Option Pricing Models and Volatility Using Excel-VBA by Fabrice Rouah'. It was a surprise, when I first discovered the method in this reference. Stay tuned for more. Like the facebook page for all the updates.