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)

## 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.interface ISDE

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public interface ISDE { // methods for calculating drift and diffusion term of stochastic differential equation double drift(double s, double t); double diffusion(double s, double t); } }

abstract class SDE

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public abstract class SDE :ISDE { // abstract class implementing ISDE interface public abstract double drift(double s, double t); public abstract double diffusion(double s, double t); } }

interface IDiscretization

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public interface IDiscretization { // method for discretizing stochastic differential equation double next(double s, double t, double dt, double rnd); } }

abstract class Discretization

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public abstract class Discretization : IDiscretization { // abstract class implementing IDiscretization interface protected SDE sde; protected double initialPrice; protected double expiration; // // read-only properties for initial price and expiration public double InitialPrice { get { return initialPrice; } } public double Expiration { get { return expiration; } } public Discretization(SDE sde, double initialPrice, double expiration) { this.sde = sde; this.initialPrice = initialPrice; this.expiration = expiration; } public abstract double next(double s, double t, double dt, double rnd); } }

concrete class EulerDiscretization

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public class EulerDiscretization : Discretization { // concrete implementation for Euler discretization scheme public EulerDiscretization(SDE sde, double initialPrice, double expiration) : base(sde, initialPrice, expiration) { } public override double next(double s, double t, double dt, double rnd) { return s + sde.drift(s, t) * dt + sde.diffusion(s, t) * Math.Sqrt(dt) * rnd; } } }

interface IRandomGenerator

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public interface IRandomGenerator { // method for generating normally distributed random variable double getRandom(); } }

abstract class RandomGenerator

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public abstract class RandomGenerator : IRandomGenerator { // abstract class implementing IRandomGenerator interface public abstract double getRandom(); } }

concrete class NormalApproximation

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public class NormalApproximation : RandomGenerator { // concrete implementation for normal random variable approximation // normRand = sum of 12 independent uniformly disctibuted random numbers, minus 6 private Random random; public NormalApproximation() { random = new Random(); } public override double getRandom() { // implementation uses C# uniform random generator double[] rnd = new double[12]; Func<double> generator = () => { return random.NextDouble(); }; return rnd.Select(r => generator()).Sum() - 6.0; } } }

concrete class MonteCarloEngine

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public delegate void PathSender(ref double[] path); public delegate void ProcessStopper(); // public class MonteCarloEngine { private SDE sde; private Discretization discretization; private RandomGenerator randomGenerator; private long paths; private int steps; public event PathSender sendPath; public event ProcessStopper stopProcess; // public MonteCarloEngine(Tuple<SDE, Discretization, RandomGenerator> parts, long paths, int steps) { sde = parts.Item1; discretization = parts.Item2; randomGenerator = parts.Item3; this.paths = paths; this.steps = steps; } public MonteCarloEngine(Builder builder, long paths, int steps) { Tuple<SDE, Discretization, RandomGenerator> parts = builder.build(); sde = parts.Item1; discretization = parts.Item2; randomGenerator = parts.Item3; this.paths = paths; this.steps = steps; } public void run() { double[] path = new double[steps + 1]; double dt = discretization.Expiration / steps; double vOld = 0.0; double vNew = 0.0; // for (int i = 0; i < paths; i++) { path[0] = vOld = discretization.InitialPrice; // for (int j = 1; j <= steps; j++) { // get next value using discretization scheme vNew = discretization.next(vOld, (dt * j), dt, randomGenerator.getRandom()); path[j] = vNew; vOld = vNew; } sendPath(ref path); // send one simulated path to pricer to be processed } stopProcess(); // simulation ends - notify pricer } } }

interface IBuilder

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public interface IBuilder { // method for creating all the needed objects for asset price simulations Tuple<SDE, Discretization, RandomGenerator> build(); } }

abstract class Builder

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public abstract class Builder : IBuilder { // abstract class implementing IBuilder interface public abstract Tuple<SDE, Discretization, RandomGenerator> build(); } }

interface IPricer

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public interface IPricer { void processPath(ref double[] path); void calculate(); double price(); } }

abstract class Pricer

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public delegate double OneFactorPayoff(double spot, double strike); // public abstract class Pricer : IPricer { protected OneFactorPayoff payoff; // delegate function for payoff calculation protected Func<double> discountFactor; // generic delegate function for discount factor protected double v; // option price protected long paths; // running counter // public Pricer() { } public Pricer(OneFactorPayoff payoff, Func<double> discountFactor) { this.payoff = payoff; this.discountFactor = discountFactor; } public abstract void processPath(ref double[] path); public abstract void calculate(); public abstract double price(); } }

After implementing all the interfaces and classes described above, add the following new implementations to this design.

concrete class ExcelBuilder

using System; using System.Collections.Generic; using System.Linq; using System.Text; using ExcelDna.Integration; namespace BondPricer { public class ExcelBuilder : Builder { private double t; private dynamic Excel = ExcelDnaUtil.Application; // public ExcelBuilder(double t) { this.t = t; } public override Tuple<SDE, Discretization, RandomGenerator> build() { // build all objects needed for asset path simulations SDE sde = build_SDE(); Discretization discretization = build_discretization(sde); RandomGenerator randomGenerator = build_randomGenerator(); return new Tuple<SDE, Discretization, RandomGenerator>(sde, discretization, randomGenerator); } private SDE build_SDE() { SDE sde = null; string sdeType = (string)Excel.Range("_shortRateModel").Value; double longTermRate = (double)Excel.Range("_longTermRate").Value2; double reversionSpeed = (double)Excel.Range("_reversionSpeed").Value2; double rateVolatility = (double)Excel.Range("_rateVolatility").Value2; // if (sdeType == "VASICEK") sde = new Vasicek(longTermRate, reversionSpeed, rateVolatility); if (sdeType == "CIR") sde = new Vasicek(longTermRate, reversionSpeed, rateVolatility); // insert new stochastic model choices here return sde; } private Discretization build_discretization(SDE sde) { Discretization discretization = null; string discretizationType = (string)Excel.Range("_discretization").Value; // if (discretizationType == "EULER") { double initialPrice = (double)Excel.Range("_spotRate").Value2; discretization = new EulerDiscretization(sde, initialPrice, t); } // insert new discretization scheme choices here return discretization; } private RandomGenerator build_randomGenerator() { RandomGenerator randomGenerator = null; string randomGeneratorType = (string)Excel.Range("_randomGenerator").Value; // if (randomGeneratorType == "CLT") { randomGenerator = new NormalApproximation(); } // insert new random generator choices here return randomGenerator; } } }

concrete class Vasicek

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public class Vasicek : SDE { private double longTermRate; private double reversionSpeed; private double rateVolatility; // public Vasicek(double longTermRate, double reversionSpeed, double rateVolatility) { this.longTermRate = longTermRate; this.reversionSpeed = reversionSpeed; this.rateVolatility = rateVolatility; } public override double drift(double s, double t) { return reversionSpeed * (longTermRate - s); } public override double diffusion(double s, double t) { return rateVolatility; } } }

concrete class CIR

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public class CIR : SDE { private double longTermRate; private double reversionSpeed; private double rateVolatility; // public CIR(double longTermRate, double reversionSpeed, double rateVolatility) { this.longTermRate = longTermRate; this.reversionSpeed = reversionSpeed; this.rateVolatility = rateVolatility; } public override double drift(double s, double t) { return reversionSpeed * (longTermRate - s); } public override double diffusion(double s, double t) { return rateVolatility * Math.Sqrt(s); } } }

concrete class ZCBPricer

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace BondPricer { public class ZCBPricer : Pricer { private double t; private double dt; // public ZCBPricer(double t, double dt) : base() { this.t = t; this.dt = dt; } // public override void processPath(ref double[] path) { int n = (int)(t / dt); double integral = 0.0; for (int i = 0; i < n; i++) integral += path[i] * dt; v += integral; paths++; } public override void calculate() { v /= paths; // calculate expectation } public override double price() { return Math.Exp(-v); // return discount factor } } }

concerete class BondPricer (VBA program will call run method of this class).

using System; using System.Collections.Generic; using System.Linq; using System.Text; using ExcelDna.Integration; using System.Windows.Forms; namespace BondPricer { public static class BondPricer { private static dynamic Excel; private static MonteCarloEngine engine; private static List<Pricer> bonds; // public static void run() { try { // create Excel application Excel = ExcelDnaUtil.Application; // // fetch bond pricing parameters from named Excel ranges int steps = (int)Excel.Range("_steps").Value2; long paths = (long)Excel.Range("_paths").Value2; // // read maturities from named range dynamic maturityArray = Excel.Range["_maturity"].Value2; int rows = maturityArray.GetUpperBound(0); double maxMaturity = (double)maturityArray.GetValue(rows, 1); double dt = (double)maxMaturity / steps; // // create Monte Carlo engine engine = new MonteCarloEngine(new ExcelBuilder(maxMaturity), paths, steps); bonds = new List<Pricer>(); // // add pricers to list, order path updates and stop notification from engine for (int i = 0; i < rows; i++) { double t = (double)maturityArray.GetValue(i + 1, 1); bonds.Add(new ZCBPricer(t, dt)); engine.sendPath += bonds[i].processPath; engine.stopProcess += bonds[i].calculate; } // // run Monte Carlo engine engine.run(); // // create output array and add zero-coupon prices double[] zeroCouponCurve = new double[rows]; for (int i = 0; i < rows; i++) zeroCouponCurve[i] = bonds[i].price(); // // print curve back to Excel Excel.Range["_zcb"] = Excel.WorksheetFunction.Transpose(zeroCouponCurve); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } } }

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 INTEGRATION

After 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 INTEGRATION

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

At 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).

## AFTERTHOUGHTS

This 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!

-Mike Juniperhill

hey Mikael, thanks for publishing a great blog, i always make sure to follow your new posts.

ReplyDeleteOn this particular project i may have found a small typo:

in the ExcelBuilder class:

if (sdeType == "VASICEK") sde = new Vasicek(longTermRate, reversionSpeed, rateVolatility);

if (sdeType == "CIR") sde = new Vasicek(longTermRate, reversionSpeed, rateVolatility);

should this read:

if (sdeType == "VASICEK") sde = new Vasicek(longTermRate, reversionSpeed, rateVolatility);

if (sdeType == "CIR") sde = new CIR(longTermRate, reversionSpeed, rateVolatility);

Correct. Sorry for typo. My mistake.

DeleteHi Mikael,

ReplyDeleteFirst of all thank you for all your material, this is extremely useful,

I just get the following error in the ZCBPricer class

Error 1 'BondPricer.Pricer' does not contain a constructor that takes 0 arguments

This is obviously a problem in the constructor inheritance. Is this something you can see in your files too?

Thanks again

Never mind, I just added a second parameterless constructor and problem solved

DeleteThanks, George. I have now corrected the following two classes : MonteCarloEngine and Pricer. Actually, there was parameterless ctor in that abstract Pricer class, but I have used some earlier versions for those two classes in this post originally. Also, in the newer MonteCarloEngine class there is an additional ctor available.

ReplyDeleteI have also found my original Excel for this application, added the resulting XLL template into it and just tested, that this example program is working, as expected.

For anyone who has been fighting with the issue : my apologies. My mistake.