In this blog it’s explained how you can wrap up C++ managed or unmanaged code and make the functionality available in Excel / VBA. The blog uses levmar as specific example, but most steps are generic. The levmar specific example is due to the fact that we are currently working on some short rate lattice models that need to be calibrated to the interests market. The calibration requires a robust solver for a nonlinear least squares problem. To share our models and their implementation with the wider finance community we choose Excel / VBA for the implementation. This allows us to easily share our ideas and algorithms. For most simple cases this worked wonderfully using a solver algorithm that was originally part of MINPACK developed by Jorge More, Burt Garbow, and Ken Hillstrom at Argonne National Laboratory. The algorithm we used was translated from Fortran to VBA by Vanna and shared on quantcode.com. This algorithm proved not to be stable enough for more complex problems. So we started looking for more stable implementations and found levmar by Lourakis. This package was written in ANSI C. Here we explain how we took this code and build xll add-in for Excel using:
-Visual Studio 2010
-levmar 2.6 (http://users.ics.forth.gr/~lourakis/levmar/)
For a recent research project we needed to solve an optimization problem. In specific we were trying to reproduce the results in the paper “A Generalized Procedure for Building Trees for the Short Rate and its Application to Determining Market Implied Volatility Functions” by Hull and White. In the paper it is described how a lattice can be constructed and calibrated to market. The calibration is essentially an optimization problem where the difference between the discount factors (or interest rates) observed in the market and the discounts generated in the model is made as small as possible by varying the model parameters.