#### Andy Nguyen

##### Member

I got most of the instruction from this blog

It has a good guide for Visual Studio 2005 and Excel 2003. Since I already wrote some Black-Scholes option pricer in C#, this is a good chance to reuse those code and see if it works in the latest versions of VS, Office.

If you don't have Visual Studio 2008, you can download the Express version for free. Much of this tutorial should also apply to earlier versions of Visual Studio and Excel.

**Step 1**

First, launch Visual Studio 2008 and create a new C# class library project called BlackScholesAddin for this example.

**Step 2**

In your Class1.cs file, remove all existing code and replace it by the following code

Code:

```
using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
namespace BlackScholesAddin
{
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class Functions
{
public Functions()
{
}
//cumulative normal distribution function
private double CND(double X)
{
double L = 0.0;
double K = 0.0;
double dCND = 0.0;
const double a1 = 0.31938153;
const double a2 = -0.356563782;
const double a3 = 1.781477937;
const double a4 = -1.821255978;
const double a5 = 1.330274429;
L = Math.Abs(X);
K = 1.0 / (1.0 + 0.2316419 * L);
dCND = 1.0 - 1.0 / Math.Sqrt(2 * Convert.ToDouble(Math.PI.ToString())) *
Math.Exp(-L * L / 2.0) * (a1 * K + a2 * K * K + a3 * Math.Pow(K, 3.0) +
a4 * Math.Pow(K, 4.0) + a5 * Math.Pow(K, 5.0));
if (X < 0)
{
return 1.0 - dCND;
}
else
{
return dCND;
}
}
//function phi
private double phi(double x)
{
double phi = 0.0;
phi = Math.Exp(-x * x / 2) / Math.Sqrt(2 * Math.PI);
return phi;
}
//implied volatility using Newton-Raphson method
public double blsimpvCall(double Price, double Strike, double Rate, double Time, double Value, double Yield)
{
const double ACCURACY = 1.0e-6;
double ComputedVolatility = Math.Pow(Math.Abs(Math.Log(Price / Strike) + Rate * Time) * 2 / Time, 0.5); // initial value of volatility
double ComputedValue = blsCall(Price, Strike, Rate, Time, ComputedVolatility, Yield);
double Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
while (Math.Abs(Value - ComputedValue) > ACCURACY)
{
ComputedVolatility = ComputedVolatility - ((ComputedValue - Value) / Vega);
ComputedValue = blsCall(Price, Strike, Rate, Time, ComputedVolatility, Yield);
Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
}
return ComputedVolatility;
}
public double blsimpvPut(double Price, double Strike, double Rate, double Time, double Value, double Yield)
{
const double ACCURACY = 1.0e-6;
double ComputedVolatility = Math.Pow(Math.Abs(Math.Log(Price / Strike) + Rate * Time) * 2 / Time, 0.5); // initial value of volatility
double ComputedValue = blsPut(Price, Strike, Rate, Time, ComputedVolatility, Yield);
double Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
while (Math.Abs(Value - ComputedValue) > ACCURACY)
{
ComputedVolatility = ComputedVolatility - ((ComputedValue - Value) / Vega);
ComputedValue = blsPut(Price, Strike, Rate, Time, ComputedVolatility, Yield);
Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
}
return ComputedVolatility;
}
//Call pricer
public double blsCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double Call = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
Call = Price * Math.Exp(-Yield * Time) * CND(d1) - Strike * Math.Exp(-Rate * Time) * CND(d2);
return Call;
}
//Put pricer
public double blsPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double Put = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
Put = Strike * Math.Exp(-Rate * Time) * CND(-d2) - Price * Math.Exp(-Yield * Time) * CND(-d1);
return Put;
}
//delta for Call
public double blsdeltaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
return Math.Exp(-Yield * Time) * CND(d1);
}
//delta for Put
public double blsdeltaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
return Math.Exp(-Yield * Time) * CND(d1) - 1;
}
//gamma is the same for Put and Call
public double blsgamma(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
return Math.Exp(-Yield * Time) * phi(d1) / (Price * Volatility * Math.Sqrt(Time));
}
//vega is the same for Put and Call
public double blsvega(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
return Price * Math.Exp(-Yield * Time) * phi(d1) * Math.Sqrt(Time);
}
//theta for Call
public double blsthetaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
return -Math.Exp(-Yield * Time) * Price * phi(d1) * Volatility / (2 * Math.Sqrt(Time)) - Rate * Strike * Math.Exp(-Rate * Time) * CND(d2) + Yield * Price * Math.Exp(-Yield * Time) * CND(d1);
}
//theta for Put
public double blsthetaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
return -Math.Exp(-Yield * Time) * Price * phi(d1) * Volatility / (2 * Math.Sqrt(Time)) + Rate * Strike * Math.Exp(-Rate * Time) * CND(-d2) - Yield * Price * Math.Exp(-Yield * Time) * CND(-d1);
}
//rho for Call
public double blsrhoCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
return Strike * Time * Math.Exp(-Rate * Time) * CND(d2);
}
//rho for Put
public double blsrhoPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
return -Strike * Time * Math.Exp(-Rate * Time) * CND(-d2);
}
//volga is the same for Call and Put
public double blsvolga(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
return Price * Math.Exp(-Yield * Time) * phi(d1) * Math.Sqrt(Time) * d1 * d2 / Volatility;
}
//vanna is the same for Call and Put
public double blsvanna(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double vanna = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
vanna = -Math.Exp(-Yield * Time) * phi(d1) * d2 / Volatility;
return vanna;
}
//charm for Call
public double blscharmCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double charmC = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
charmC = -Yield * Math.Exp(-Yield * Time) * CND(d1) + Math.Exp(-Yield * Time) * phi(d1) * (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) / (2 * Time * Volatility * Math.Sqrt(Time));
return charmC;
}
//charm for Put
public double blscharmPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double charmP = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
charmP = Yield * Math.Exp(-Yield * Time) * CND(-d1) - Math.Exp(-Yield * Time) * phi(d1) * (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) / (2 * Time * Volatility * Math.Sqrt(Time));
return charmP;
}
//color is the same for Call and Put
public double blscolor(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double color = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
color = -Math.Exp(-Yield * Time) * (phi(d1) / (2 * Price * Time * Volatility * Math.Sqrt(Time))) * (2 * Yield * Time + 1 + (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) * d1 / (2 * Time * Volatility * Math.Sqrt(Time)));
return color;
}
//dual delta for Call
public double blsdualdeltaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double ddelta = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
ddelta = -Math.Exp(-Rate * Time) * CND(d2);
return ddelta;
}
//dual delta for Put
public double blsdualdeltaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double ddelta = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
ddelta = Math.Exp(-Rate * Time) * CND(-d2);
return ddelta;
}
//dual gamma is the same for Call and Put
public double blsdualgamma(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
{
double d1 = 0.0;
double d2 = 0.0;
double dgamma = 0.0;
d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
d2 = d1 - Volatility * Math.Sqrt(Time);
dgamma = Math.Exp(-Rate * Time) * phi(d2) / (Strike * Volatility * Math.Sqrt(Time));
return dgamma;
}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("",
System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type,
string subKeyName)
{
System.Text.StringBuilder s =
new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
}
}
```

**Step 3**

With this code written, show the properties for the project by double clicking on the properties node under the project in Solution Explorer. Click on the Build tab and check the check box that says "Register for COM Interop". Then choose "Build" to build the add-in. ]

**Step 4**]

Add the UDF we just created to the list of Excel addins

*For Excel 2007*Launch Excel and click on the main button at the top left corner and click on Excel Options. Select Add-Ins on the left menu and at the bottom drop down menu, select Excel Add-ins and click Go. Click on the Automation button. You can find the class you created by looking for BlackScholesAddin.Functions in the list of Automation add-ins:

__For Excel 2003, XP__Launch Excel and go to the Tools, Add-ins dialog in Excel and click on the Automation button. You can find the class you created by looking for BlackScholesAddin.Functions in the list of Automation add-ins:

By clicking OK in this dialog, you add BlackScholesAddin.Functions to the list of installed add-ins as shown here. You might get a dialog at this point about mscoree.dll. Click No to this dialog (Yes will delete the add-in from the list).]

**Step 5**

Now, let's try to use all the functions blsCall inside Excel. First create an empty spreadsheet. Click on an empty cell in the workbook and then click on the Insert Function button in the formula bar. From the dialog of available formulas, drop down the "Or select a category" drop down box and choose "BlackScholesAddin.Functions". Then click on the

**blsCall**function as shown here:

__:__

**Note***I use the same notation as used by Matlab Black-Scholes functions for option price and Greeks. More on it can be found here*

**Note 2**: As noted by chancea if BlackScholesAddin.Functions does not appear in the Automation list (in his installation of VS2010 sp1 and Excel 2007), change the project properties from using .NET 4.0 to 3.5.

**Step 6**

Use it just like regular Excel builtin functions.

**Option pricer**

blsCall(Price, Strike, Rate, Time, Volatility, Yield)

blsPut(Price, Strike, Rate, Time, Volatility, Yield)

**Greeks**

*Delta*blsdeltaCall(Price, Strike, Rate, Time, Volatility, Yield)

blsdeltaPut(Price, Strike, Rate, Time, Volatility, Yield)

*Theta*blsthetaCall(Price, Strike, Rate, Time, Volatility, Yield)

blsthetaPut(Price, Strike, Rate, Time, Volatility, Yield)

__Rho__blsrhoCall(Price, Strike, Rate, Time, Volatility, Yield)

blsrhoPut(Price, Strike, Rate, Time, Volatility, Yield)

*Charm*blscharmCall(Price, Strike, Rate, Time, Volatility, Yield)

blscharmPut(Price, Strike, Rate, Time, Volatility, Yield)

*Dual delta*blsdualdeltaCall(Price, Strike, Rate, Time, Volatility, Yield)

blsdualdeltaPut(Price, Strike, Rate, Time, Volatility, Yield)

*Gamma*blsgamma(Price, Strike, Rate, Time, Volatility, Yield) //gamma is same for C and P

*Vega*blsvega(Price, Strike, Rate, Time, Volatility, Yield) //vega is same for C and P

*Vanna*blsvanna(Price, Strike, Rate, Time, Volatility, Yield) //vanna is same for C and P

*Volga*blsvolga(Price, Strike, Rate, Time, Volatility, Yield) //volga is same for C and P

*Color*blscolor(Price, Strike, Rate, Time, Volatility, Yield) //color is same for C and P

*Dual Gamma*blsdualgamma(Price, Strike, Rate, Time, Volatility, Yield) //dual gamma is same for C and P