Saturday, June 28, 2014

Configurable C# Monte Carlo zero-coupon bond pricer in Excel

Monte Carlo design, what was presented in my previous blog article could actually be used, not only for pricing options, but for all applications where one would like to simulate stochastic process. One such useful application would be to simulate short rate process for pricing zero-coupon bonds, for example.

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 OUTCOME

The 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 TASKS

Download 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 DESIGN

As 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)

There will be some small modifications made to Builder component.

C# PROGRAM

Create 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.