In this article, we investigate how to implement such pricing scheme with the current design for one-factor short-rate models. As a result of this small project, we will have a framework for pricing zero-coupon bonds with any given one-factor short-rate model. Specifically, we implement zero-coupon bond pricing schemes for Vasicek model and CIR model. Since both of these models are nicely tractable Gaussian models, we also compare the simulated bond prices with the exact analytical model prices. Before going further with this article, make sure that you have clear understanding of the design presented in my previous blog article.
PROJECT OUTCOMEThe outcome of this small project is fully configurable C# Monte Carlo pricer application in Excel. Application can be used to price zero-coupon bond prices with any different types of one-factor short-rate models. The application gets all the required input parameters directly from Excel, then performs calculations in C# and finally returns calculation results back to Excel. Excel and C# are interfaced with Excel-DNA and Excel itself is used only as data input/output platform, exactly like presented in this blog article.
PREPARATORY TASKSDownload and unzip Excel-DNA zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.
EXTENSIONS FOR CURRENT DESIGNAs a base design for this application, we will use the design presented in my previous blog article. There will be no changes made to core components of this base design (SDE, Discretization, RandomGenerator). We are going to implement the following extensions for this base design.
- Two new concrete class implementations for abstract class SDE (Vasicek, CIR)
- One new concrete class implementation for abstract class Pricer (BondPricer)
C# PROGRAMCreate a new C# Class Library project (BondPricer), save the project and copyPaste the following code blocks into separate cs files. These classes are all we need from our current base design. For the sake of clarity, I made a decision not to include any option-related classes to this design.
abstract class SDE
abstract class Discretization
concrete class EulerDiscretization
abstract class RandomGenerator
concrete class NormalApproximation
concrete class MonteCarloEngine
abstract class Builder
abstract class Pricer
After implementing all the interfaces and classes described above, add the following new implementations to this design.
concrete class ExcelBuilder
concrete class Vasicek
concrete class CIR
concrete class ZCBPricer
concerete class BondPricer (VBA program will call run method of this class).
One essential input for this application is the array of zero-coupon bond maturities, fetched directly from Excel worksheet. Time in years for the longest bond maturity is saved into local variable maxMaturity and time step length (dt) is calculated as maxMaturity divided by the number of steps. In the Excel example below, maxMaturity is 10 years, steps per simulation is 2500 and dt is 0.004. During the simulation process, MonteCarloEngine will then always simulate a path having maturity equal to maxMaturity (10.0) and having time step length equal to dt (0.004).
Now, for each simulated short-rate path, generated by MonteCarloEngine, ZCBPricer will get a path update (array of doubles) by its processPath method. ZCBPricer will then calculate zero-coupon bond price as integral of this short rate path. However, time length of short-rate path in years, provided by MonteCarloEngine (10.0), could be longer than the maturity of zero-coupon bond defined in ZCBPricer (double t). In order to process only the rates where time is less or equal to maturity of zero-coupon bond, local variable n (t / dt) is used in loop condition of pricer's processPath method. So, the pricer will always receive the whole path, but it picks only the items it needs.
In essence, most of the time our MonteCarloEngine is "working more than it should", since most of the bond maturities are less than maxMaturity and MonteCarloEngine will always simulate a path having maturity equal to maxMaturity. However, this design has been deliberate choice for this particular application, in order to maintain a scheme with only one engine plus n pricers. Needless to say, the price we pay for this particular scheme is increased processing time.
EXCEL-DNA INTEGRATIONAfter implementing all the previous cs files into C# Class Library project, we are receiving a lot of errors. However, all errors should be related to missing references to Excel-DNA integration library and Windows Forms library. Next, carefully follow the instructions described here in step two.
In a nutshell
- add reference to Excel-DNA library (ExcelDna.Integration.dll). From the properties of this reference, set Copy Local to be False.
- add reference to Windows Forms library (System.Windows.Forms)
- create BondPricer.dna file (consisting XML tags). DnaLibrary Name="BondPricer" and Path="BondPricer.dll". From the properties of this dna file, set Copy to Output Directory to be Copy if newer.
- copy ExcelDna.xll file into your project folder and rename it to be BondPricer.xll. From
the properties of this xll file, set Copy to Output Directory to be Copy if newer.
USER INTERFACE AND C#-VBA INTEGRATIONThe essence of this part of the process has been described here in step three. Open a new Excel workbook. Create the following source data into worksheet. In the picture below, I have also included Excel formulas for calculating bond yield from zero-coupon bond price and calculating exact analytical price for Vasicek model (coefficients A, B).
From Excel Name Manager (Formulas - Name Manager), set the following range names.
In VB editor, create the following event handling program for CommandButton (run).
TEST RUNAt this point, our zero-coupon bond pricing application is ready for test run. While the current Excel workbook is open, doubleClick BondPricer.xll file in your \\BondPricer\bin\Release folder. After this, xll file content can be used by Excel and our C# program is available to be called from VBA program (Application.Run). VBA program will call and start C# program run, which then reads all input data, performs calculations and sends result data back to Excel worksheet.
After pressing command button in Excel interface, my C# MC zero-coupon bond pricer application simulated the following prices for all requested maturities from 1 year to 10 years (Monte Carlo simulation).
AFTERTHOUGHTSThis small project was presenting one possible Monte Carlo design for pricing zero-coupon bonds with one-factor short-rate processes. For this application, we basically just extended our existing flexible Monte Carlo design. Comparing our simulated bond prices with the exact analytical counterparties, we can conclude that our application is pricing bonds correctly, related to short-rate model used.
Thanks for reading and have a nice summer!