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

Configurable C# Monte Carlo option pricer in Excel

This time, I wanted to present one possible design for Monte Carlo (MC) option pricer, what I have been chewing for some time. The great wisdom what I have learned so far is the following: MC application is always inherently a tradeoff between speed and flexibility. The fastest solution is just one monolithic program, where everything is hard-coded. However, this type of solution leads to maintenance and extendability problems, when any new types of pricers needs to be created, for example. And again, a desire for more flexible solution leads to increases in design complexity and running time.

Now, with this design example, we may not create the fastest possible solution, but the one with extremely flexible design and great configurability. More specifically when pricing options, the user is able to select different types of

• (SDE) Stochastic differential equation (Geometric Brownian Motion, CIR, Vasicek, etc)
• SDE discretization scheme (Euler, Milstein, etc)
• Random number generator (Low discrepancy sequence, pseudo-random numbers, etc)
• Option pricer type (European, Digital, Asian, Barrier, etc)
• Option payoff type (Call, Put)
• The source, from which the model is going to be created (Excel, file, console, etc)

The presented design can actually be used, not only for pricing options, but for all applications where we would like to simulate any stochastic processes. For example, we could use "the core part" of this design (SDE, Discretization, RandomGenerator and MonteCarloEngine) when simulating short rate processes for yield curve estimation. However, this example concentrates only on pricing options. More specifically, the user is able to use this design example when pricing options without embedded decisions (American, Bermudan).

PROJECT OUTCOME

The outcome of this small project is fully configurable C# Monte Carlo pricer application. Application can be used to price wide range of different types of one-factor options (European, binary, path-dependent). 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 my previous blog post.

PREPARATORY TASKS

Download and unzip Excel-DNA Version 0.30 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.

 

DESIGN OVERVIEW

The application design is presented in the UML class diagram below.




















In order to understand this design better, we go through the core components and general logic of this design.

STOCHASTIC PATH CREATION - THE CORE OF THE ENGINE

Whenever we need to simulate stochastic process path, we need to define stochastic differential equation to be used. In addition to this, we also need to define discretization scheme for this SDE. In order to model differential equation to be stochastic, we need standardized normal random number. The following three components provide service to create prices according to a given stochastic differential equation, discretization scheme and random number generator.

With SDE component, we can model the following types of one-factor stochastic differential equations.

Interface ISDE defines methods for retrieving drift and diffusion terms for a given S and t. Abstract class SDE implements this interface. Finally, from abstract SDE we can implement concrete classes for different types of SDE's (GBM, Vasicek, CIR, etc). In this design example, we are using Standard Geometric Brownian Motion.

IDiscretization interface defines method for retrieving spot price for a given S, t, dt and random term. Abstract Discretization class implements this interface and also defines initial spot price (initialPrice) and time to maturity (expiration) as protected member data. It should be noted, that our concrete SDE is aggregated into Discretization. In this design example, we are using Euler discretization scheme.

Finally, IRandomGenerator defines method for getting standard normal random number. Again, RandomGenerator implements this interface and in this design example our concrete class NormalApproximation is "quick and dirty way" to generate normal random approximations as the sum of 12 independent uniformly distributed random numbers minus 6. Needless to say, we should come up with the better random number generator implementation for this class, when starting to test option pricing against benchmark prices.

 

BUILDER AND MONTE CARLO ENGINE

Creating all previously presented "core objects" in the main program can easily lead to maintenance problems and main program "explosion". The solution for this common problem is to use Builder design pattern. The interaction between Builder component and MonteCarloEngine is described in the picture below.































IBuilder interface defines method for creating and retrieving all three core objects inside Tuple. Abstract Builder class implements this interface and concrete class implements Builder class. In this design example, our concrete implementation for Builder class is ExcelBuilder class, which will build all three core objects directly from Excel workbook and finally packs these objects into Tuple.

There is an association between Builder and MonteCarloEngine. Selected Builder object to be used will be given as one argument in MonteCarloEngine constructor. In constructor code, Builder will build three core objects and packs those into Tuple. After this, constructor code will assign values for private data members directly from Tuple (SDE, Discretization, RandomGenerator).

The purpose of MonteCarloEngine class is to create stochastic price paths, by using these three core objects described above. Actually, this class is also an implementation of Mediator design pattern. We have been implementing all the needed components as loosely coupled classes. All communication between these objects are handled by MonteCarloEngine (Mediator).

When MonteCarloEngine has simulated a path, it uses event (delegate function PathSender) for distributing this simulated path (array of doubles) for pricers, one path at a time. Then, when MonteCarloEngine has been simulating desired number of paths, it uses event (delegate function ProcessStopper) for sending notification on the simulation process end for pricers. After receiving this notification from MonteCarloEngine, pricers will calculate the option prices.

PRICER

The final component of this solution is Pricer. This component is receiving simulated price path from MonteCarloEngine and calculating option value for a given one-factor payoff function for each simulated path (delegate function OneFactorPayoff). Pricer class uses a given discount factor (generic delegate function discountFactor) for calculating present value for option payoff expectation. Finally, client can retrieve calculated option price with public price method.

IPricer interface defines methods for processing simulated price path (processPath), calculating option price (calculate) and retrieving option price (price). Pricer implements this interface. Also, it has OneFactorPayoff delegate, discountFactor generic delegate and number of simulated paths as protected member data. Technically, the variable for simulated paths is only a running counter for expectation calculation purposes. Concrete implementation of Pricer class uses OneFactorPayoff delegate function for calculating the actual option payoff for a given spot and strike.

 

C# PROGRAM

All interfaces and classes described above, are given here below. Create a new C# Class Library project (MCPricer), save the project and copyPaste the following code blocks into separate cs files.

interface ISDE

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MCPricer
{
    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 MCPricer
{
    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);
    }
}

concrete class GBM

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MCPricer
{
    // concrete implementation for Standard Geometric Brownian Motion
    public class GBM : SDE
    {
        private double r; // risk-free rate
        private double q; // dividend yield
        private double v; // volatility
        //
        public GBM(double r, double q, double v)
        {
            this.r = r; this.q = q; this.v = v;
        }
        public override double drift(double s, double t)
        {
            return (r - q) * s;
        }
        public override double diffusion(double s, double t)
        {
            return v * s;
        }
    }
}

interface IDiscretization

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MCPricer
{
    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 MCPricer
{
    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 MCPricer
{
    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 MCPricer
{
    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 MCPricer
{
    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 MCPricer
{
    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 MCPricer
{
    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(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 MCPricer
{
    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 MCPricer
{
    public abstract class Builder : IBuilder
    {
        // abstract class implementing IBuilder interface
        public abstract Tuple<SDE, Discretization, RandomGenerator> build();
    }
}

concrete class ExcelBuilder

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExcelDna.Integration;

namespace MCPricer
{
    public class ExcelBuilder : Builder
    {
        private dynamic Excel = ExcelDnaUtil.Application;
        //
        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("_stochasticModel").Value;
            //
            if (sdeType == "GBM")
            {
                double r = (double)Excel.Range("_rate").Value2;
                double q = (double)Excel.Range("_yield").Value2;
                double v = (double)Excel.Range("_volatility").Value2;
                sde = new GBM(r, q, v);
            }
            // 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("_spot").Value2;
                double expiration = (double)Excel.Range("_maturity").Value2;
                discretization = new EulerDiscretization(sde, initialPrice, expiration);
            }
            // 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;
        }
    }
}

interface IPricer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MCPricer
{
    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 MCPricer
{
    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(OneFactorPayoff payoff, Func<double> discountFactor)
        {
            this.payoff = payoff; this.discountFactor = discountFactor;
        }
        public abstract void processPath(ref double[] path);
        public void calculate()
        {
            // calculate discounted expectation
            v = (v / paths) * discountFactor();
        }
        public double price()
        {
            // return option value
            return v;
        }

    }
}

concrete class EuropeanPricer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MCPricer
{
    public class EuropeanPricer : Pricer
    {
        private double x; // option strike
        //
        public EuropeanPricer(OneFactorPayoff payoff, double x, Func<double> discountFactor)
            : base(payoff, discountFactor)
        {
            this.x = x;
        }
        public override void processPath(ref double[] path)
        {
            // calculate payoff
            v += payoff(path[path.Length - 1], x);
            paths++;
        }
    }
}

concrete class ArithmeticAsianPricer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MCPricer
{
    public enum ENUM_ASIAN_TYPE { average_price, average_strike }
    //
    public class ArithmeticAsianPricer : Pricer
    {
        private ENUM_ASIAN_TYPE asianType;
        private double x; // option strike
        private double averagePeriodStart; // time for starting averaging period
        private double t;
        private int steps;
        //
        public ArithmeticAsianPricer(OneFactorPayoff payoff, double x, Func<double> discountFactor, 
            double t, double averagePeriodStart, int steps, ENUM_ASIAN_TYPE asianType)
            : base(payoff, discountFactor)
        {
            this.x = x;
            this.t = t;
            this.steps = steps;
            this.averagePeriodStart = averagePeriodStart;
            this.asianType = asianType;
        }
        public override void processPath(ref double[] path)
        {
            double dt = t / steps;
            int timeCounter = -1;
            //
            // generic delegate for SkipWhile method to test if averaging period for an item has started
            Func<double, bool> timeTest = (double p) => 
            {
                timeCounter++;
                if ((dt * timeCounter) < averagePeriodStart) return true;
                    return false;
            };
            //
            // calculate average price for averaging period
            double pathAverage = path.SkipWhile(timeTest).ToArray().Average();
            //
            // calculate payoff
            if (asianType == ENUM_ASIAN_TYPE.average_price) v += payoff(pathAverage, x);
            if (asianType == ENUM_ASIAN_TYPE.average_strike) v += payoff(path[path.Length - 1], pathAverage);
            paths++;
        }
    }
}

concrete class BarrierPricer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MCPricer
{
    public enum ENUM_BARRIER_TYPE { up_and_in, up_and_out, down_and_in, down_and_out }
    //
    public class BarrierPricer : Pricer
    {
        private double x; // option strike
        private double b; // barrier level
        private ENUM_BARRIER_TYPE barrierType;
        //
        public BarrierPricer(OneFactorPayoff payoff, double x, Func<double> discountFactor, 
            double b, ENUM_BARRIER_TYPE barrierType) : base(payoff, discountFactor)
        {
            this.x = x;
            this.b = b;
            this.barrierType = barrierType;
        }
        public override void processPath(ref double[] path)
        {
            // calculate payoff - check barrier breaches
            if ((barrierType == ENUM_BARRIER_TYPE.up_and_in) && (path.Max() > b)) v += payoff(path[path.Length - 1], x);
            if ((barrierType == ENUM_BARRIER_TYPE.up_and_out) && (path.Max() < b)) v += payoff(path[path.Length - 1], x);
            if ((barrierType == ENUM_BARRIER_TYPE.down_and_in) && (path.Min() < b)) v += payoff(path[path.Length - 1], x);
            if ((barrierType == ENUM_BARRIER_TYPE.down_and_out) && (path.Min() > b)) v += payoff(path[path.Length - 1], x);
            paths++;
        }
    }
}

concerete class MCPricer (this is the main program, 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 MCPricer
{
    public static class MCPricer
    {
        private static dynamic Excel;
        private static Dictionary<string, Pricer> pricer;
        private static MonteCarloEngine engine;
        private static OneFactorPayoff callPayoff;
        private static OneFactorPayoff putPayoff;
        private static Func<double> discountFactor;
        //
        public static void run()
        {
            try
            {
                // create Excel application
                Excel = ExcelDnaUtil.Application;
                //
                // fetch pricing parameters from named Excel ranges
                int steps = (int)Excel.Range("_steps").Value2;
                long paths = (long)Excel.Range("_paths").Value2;
                double r = (double)Excel.Range("_rate").Value2;
                double t = (double)Excel.Range("_maturity").Value2;
                double averagePeriodStart = (double)Excel.Range("_averagingPeriod").Value2;
                double upperBarrier = (double)Excel.Range("_upperBarrier").Value2;
                double lowerBarrier = (double)Excel.Range("_lowerBarrier").Value2;
                double x = (double)Excel.Range("_strike").Value2;
                //
                // create Monte Carlo engine, payoff functions and discounting factor
                engine = new MonteCarloEngine(new ExcelBuilder(), paths, steps);
                callPayoff = (double spot, double strike) => Math.Max(0.0, spot - strike);
                putPayoff = (double spot, double strike) => Math.Max(0.0, strike - spot);
                discountFactor = () => Math.Exp(-r * t);
                //
                // create pricers into dictionary
                pricer = new Dictionary<string, Pricer>();
                pricer.Add("Vanilla call", new EuropeanPricer(callPayoff, x, discountFactor));
                pricer.Add("Vanilla put", new EuropeanPricer(putPayoff, x, discountFactor));
                pricer.Add("Asian average price call", new ArithmeticAsianPricer(callPayoff, x, discountFactor, t, averagePeriodStart, steps, ENUM_ASIAN_TYPE.average_price));
                pricer.Add("Asian average price put", new ArithmeticAsianPricer(putPayoff, x, discountFactor, t, averagePeriodStart, steps, ENUM_ASIAN_TYPE.average_price));
                pricer.Add("Asian average strike call", new ArithmeticAsianPricer(callPayoff, x, discountFactor, t, averagePeriodStart, steps, ENUM_ASIAN_TYPE.average_strike));
                pricer.Add("Asian average strike put", new ArithmeticAsianPricer(putPayoff, x, discountFactor, t, averagePeriodStart, steps, ENUM_ASIAN_TYPE.average_strike));
                pricer.Add("Up-and-in barrier call", new BarrierPricer(callPayoff, x, discountFactor, upperBarrier, ENUM_BARRIER_TYPE.up_and_in));
                pricer.Add("Up-and-out barrier call", new BarrierPricer(callPayoff, x, discountFactor, upperBarrier, ENUM_BARRIER_TYPE.up_and_out));
                pricer.Add("Down-and-in barrier call", new BarrierPricer(callPayoff, x, discountFactor, lowerBarrier, ENUM_BARRIER_TYPE.down_and_in));
                pricer.Add("Down-and-out barrier call", new BarrierPricer(callPayoff, x, discountFactor, lowerBarrier, ENUM_BARRIER_TYPE.down_and_out));
                pricer.Add("Up-and-in barrier put", new BarrierPricer(putPayoff, x, discountFactor, upperBarrier, ENUM_BARRIER_TYPE.up_and_in));
                pricer.Add("Up-and-out barrier put", new BarrierPricer(putPayoff, x, discountFactor, upperBarrier, ENUM_BARRIER_TYPE.up_and_out));
                pricer.Add("Down-and-in barrier put", new BarrierPricer(putPayoff, x, discountFactor, lowerBarrier, ENUM_BARRIER_TYPE.down_and_in));
                pricer.Add("Down-and-out barrier put", new BarrierPricer(putPayoff, x, discountFactor, lowerBarrier, ENUM_BARRIER_TYPE.down_and_out));
                //
                // order path updates for all pricers from engine
                foreach (KeyValuePair<string, Pricer> kvp in pricer) engine.sendPath += kvp.Value.processPath;
                //
                // order process stop notification for all pricers from engine
                foreach (KeyValuePair<string, Pricer> kvp in pricer) engine.stopProcess += kvp.Value.calculate;
                //
                // run Monte Carlo engine
                engine.run();
                //
                // print option types to Excel
                string[] optionTypes = pricer.Keys.ToArray();
                Excel.Range["_options"] = Excel.WorksheetFunction.Transpose(optionTypes);
                //
                // print option prices to Excel
                double[] optionPrices = new double[pricer.Count];
                for (int i = 0; i < pricer.Count; i++) optionPrices[i] = pricer.ElementAt(i).Value.price();
                Excel.Range["_prices"] = Excel.WorksheetFunction.Transpose(optionPrices);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
        }
    }
}

 

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 MCPricer.dna file (consisting XML tags). DnaLibrary Name="MCPricer" and Path="MCPricer.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 MCPricer.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.

From Excel Name Manager (Formulas - Name Manager), set the following range names.

In VB editor, create the following event handling program for CommandButton (Calculate option prices).

TEST RUN

At this point, our application is ready for test run. While this Excel workbook is open, doubleClick MCPricer.xll file in your \\MCPricer\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, C# MC option pricer application simulated the following prices for all requested options.

AFTERTHOUGHTS

This small project was presenting one possible design for Monte Carlo option pricer. We came up with extremely flexible design and great configurability. The presented design can actually be used, not only for pricing options, but for all applications where we would like to simulate any stochastic processes for any purpose (short rate processes for yield curve estimation, for example).

At this point, I would like to express my appreciation for Mr. Daniel Duffy for opening up some of his "well-brewed design wisdoms" during the one of his DatasimFinancial training courses. For those who would like get familiar with useful examples and ideas using C# in financial programs, there is a great book C# for Financial Markets available written by Daniel Duffy and Andrea Germani (published 2013).

As always, I owe Thank You again for Govert Van Drimmelen (inventor, developer and author of Excel-DNA), for his amazing Excel-DNA Excel/C API wrapper. For learning more about this extremely useful tool, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

And finally, Thank You for reading my blog again!
-Mike Juniperhill