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.

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.
  

Make sure, that all properties for these references and files are exactly the same as described in this post. After adding all the required references, files and building this program once again, my project folder has the following four files.

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