Some time ago, I published one possible C# implementation for Ho-Lee one-factor model calibration scheme using AlgLib numerical libraries. This time I will present an implementation for the same scheme in VBA. General information concerning Levenberg-Marquardt algorithm implementation in AlgLib has been presented in here. Libraries for VBA (collection of BAS module files, which are going to be included into VBA project) can be downloaded from here.
A few words about this implementation. AlgLibLMASolver class uses AlgLib library functions (functions from 21 different modules) for processing (creating optimization model, setting conditions, processing iterations). One data member within this class is having a type of IModel. This data member is actually a reference to an interface, which provides a set of functions for all required calculations (objective function value, values for function terms, partial derivative values for function terms). Since all possible implementations for any interface method must honor signatures exactly, there is a problem with VBA since it does not have a real constructor mechanism. I have chewed this issue in here. It might help to explain the reason, why I have been distributing input parameters for interface implementation by using Dictionary object. Finally, HoLeeZeroCouponCalibration class is implementing IModel interface (a set of functions for all required calculations). In essence, algorithms (AlgLib-related processing) and data (values calculated specifically by using Ho-Lee model) have been completely separated. Needless to say, this type of scheme is flexible for new implementations.
Create a new VBA project and copy-paste the following classes and modules into this project. Also, import all required 21 AlgLib BAS files into this project.
The results from this calibration model have been verified against the previous results.
Importing several files into project may involve considerable amount of cruel and unusual repetitive labour. For this specific reason, I have also been woodshedding a separate module (employing VBIDE object), which might give some relief when babysitting those AlgLib modules.
Finally, thanks for reading this blog.