This is a least-square fitting through a DLL with an Excel interface for
an objective functions f: IR^1 x IR^n -> IR^1 with n parameters (but do
not have too much of them or too less of data). This is fitting of 1-dim
curves against data by estimating parameters.
One can provide weights for all the residues, which then are treated as
weight * ( f(x,parameters) - y ), so a Chi^2-fit is possible.
The objective function is defined and handled within VBA through a call
back technique from the DLL, hence it can be chosen quite freely by the
user. As a syntax the parameters are enumerated param(1), param(2), ...
within arrays. For details check the example.
No need to provide partial derivatives, they are computed numerically.
Data are feed through an Excel sheet, but the example is easily modified
to have a solution working within VBA only.
The objective function is formulated within the module m_model. What actually
is used are the functions 'objFct_array' and 'objFct_GDA' which uses a global
array to hold the parameters (and is accessed through the DLL). Do never change
the prototype of that function.
The fitting routine is handled from module m_minimizeLS. There is no need to
touch code here (except for change the number of parameters or the weighting,
I did not implement the most general way).
The module m_minimizeLS has a function 'setParam' for the global data area
which the DLL uses for call backs. The other is the interface 'minimize_LS'
to call the optimizer in the DLL. It interally uses objFct_GDA, setParam and
GDA (for callbacks from the DLL) and outputs the estimated parameters as an
array. Do never change the the function types of that things. GDA is a global
data area to have the parameters outside of any function (and that makes
the objective function 1-dimensional as parameters are chosen for it).
The example is Gatheral's SVI smile model. He gives a model to approximate
variance = volatility^2 * time over moneyness = log( strike / forward):
A parsimonious arbitrage-free implied volatility parameterization
with application to the valuation of volatility derivatives (June 2004),
http://www.math.nyu.edu/fellows_fin_math/gatheral/madrid2004.pdf
Thus the actual model is x-values = moneyness, y-values = variance and there
are five parameters (a trivial weighting is chosen) to estimate.
Within the module m_vola the conversion from the observed values to the model
values is done (so it is needed only for that example).
For the particular example a function initialParams is included which will
estimate initial parameters for the fit. Beyond the example one has to guess
the initial parameters - but one always needs some as a starting point for an
optimizer.
As a worksheet example everything is done in the sheet 'volatility approx':
Entering strikes, vola, forward (~ spot * exp(rates*time) and time-to-expiry
the input is transformed for the actual model to have variance and moneyness.
An 'automatic' guess is computed in the named range 'arrParams_automatic' (1).
Copy it to place (2) - then it will be used by the minimizer.
As an alternative one can check against Excel's Solver - but it is not part
of the solution, it's only for checking the example (and it turns out that
the DLL is more robust than the Solver).
Just for fun one can try the variable approach by choosing a polynomial of
deg=4 with objFct = a + b * x + sigma * x ^ 2 + rho * x ^ 3 + m * x ^ 4 for
the objective function (just replace that only line). That should work and
in the worksheet one has to ignore Solver's result (delete the graphs which
involve the Solver or let the Solver run until he gets results). Note that
in this case the initial guess is useless (but practically one would use a
linear regression), but the robust behaviour is typical for a Levenberg-
Marquardt method (which is coded in the DLL).