# Black-Scholes in Excel, Part II

Following on from the post a few weeks ago on a simple Black-Scholes Excel macro, here is a follow up, with a slightly updated version that also calculates the major greeks (vega, gamma and delta). The formulae are from Hull’s book, or see here for examples of the closed-form greeks.

Usage of the spreadsheet is identical to the previous example, so refer to that post. The only difference is the output, which now looks like this (click to enlarge):

Function BlackScholes(SpotPrice As Double, ExercisePrice As Double,
TimeToMaturity As Double, RiskFreeRate As Double, sigma As Double,
Optional DividendYield As Double) As Double()

Dim d1 As Double
Dim d2 As Double
Dim Nd1 As Double
Dim Nd2 As Double
Dim N_dash_d1 As Double
Dim ResultArray() As Double
Dim gamma As Double
Dim vega As Double
Dim theta As Double

ReDim ResultArray(10) As Double

If (IsMissing(DividendYield)) Then
d1 = WorksheetFunction.Ln(SpotPrice / ExercisePrice)
+ ((RiskFreeRate + (0.5 * (sigma ^ 2))) * TimeToMaturity)
Else
d1 = WorksheetFunction.Ln(SpotPrice / ExercisePrice)
+ ((RiskFreeRate - DividendYield + (0.5 * (sigma ^ 2))) * TimeToMaturity)
End If

d1 = d1 / (sigma * (TimeToMaturity ^ (1 / 2)))
d2 = d1 - (sigma * (TimeToMaturity ^ (1 / 2)))
Nd1 = WorksheetFunction.NormSDist(d1)
Nd2 = WorksheetFunction.NormSDist(d2)

'Call Value
If (IsMissing(DividendYield)) Then
ResultArray(0) = (SpotPrice * Nd1) - (ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
Else
ResultArray(0) = Exp(-DividendYield * TimeToMaturity)
* (SpotPrice * Nd1) - (ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
End If

'Call Delta
ResultArray(1) = Nd1 * Exp(-DividendYield * TimeToMaturity)

'Call Gamma
N_dash_d1 = (1 / ((2 * WorksheetFunction.Pi) ^ (1 / 2)) * (Exp((-d1 ^ 2) / 2)))
gamma = (N_dash_d1 * Exp(-DividendYield * TimeToMaturity)) / (SpotPrice * sigma * (TimeToMaturity ^ (1 / 2)))
ResultArray(2) = gamma

'Call Vega
vega = SpotPrice * (TimeToMaturity ^ (1 / 2)) * N_dash_d1 * Exp(-DividendYield * TimeToMaturity)
ResultArray(3) = vega

'Call Theta
theta = -(SpotPrice * N_dash_d1 * sigma * Exp(-DividendYield * TimeToMaturity)) / (2 * (TimeToMaturity ^ (1 / 2)))
theta = theta + (DividendYield * SpotPrice * Nd1 * Exp(-DividendYield * TimeToMaturity))
theta = theta - (RiskFreeRate * ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * Nd2)
ResultArray(4) = theta

'Put Value
If (IsMissing(DividendYield)) Then
ResultArray(5) = Exp(-RiskFreeRate * TimeToMaturity) * ExercisePrice
* (1 - Nd2) - SpotPrice * (1 - Nd1)
Else
ResultArray(5) = Exp(-RiskFreeRate * TimeToMaturity) * ExercisePrice
* WorksheetFunction.NormSDist(-d2) - Exp(-DividendYield * TimeToMaturity)
* SpotPrice * WorksheetFunction.NormSDist(-d1)
End If

'Put delta
ResultArray(6) = (Nd1 - 1) * Exp(-DividendYield * TimeToMaturity)

'Put Gamma
ResultArray(7) = gamma

'Put Vega
ResultArray(8) = vega

'Put Theta
theta = -(SpotPrice * N_dash_d1 * sigma * Exp(-DividendYield * TimeToMaturity)) / (2 * (TimeToMaturity ^ (1 / 2)))
theta = theta - (DividendYield * SpotPrice * WorksheetFunction.NormSDist(-d1) * Exp(-DividendYield * TimeToMaturity))
theta = theta + (RiskFreeRate * ExercisePrice * Exp(-RiskFreeRate * TimeToMaturity) * WorksheetFunction.NormSDist(-d2))
ResultArray(9) = theta

BlackScholes = ResultArray
End Function



# Apache Source License Scanner (Ruby)

The other day, I needed to replace all instances of the old-style Apache license header in the commons-net codebase with the new style license header. My first thought was to write a simple Perl script to extract and replace instances of the license, however I decided to give Ruby a shot. The result is below: LicenseScanner.rb will scan the given directory and all subdirectories for Java source files and attempt to locate and replace any instances of older-style source headers. Being used to Perl for this kind of thing, you really appreciate some of the Perl-isms built into Ruby, for instance:

• “Here” variables – see the definition of @@new_asl_license;
• First class regular expression support, via // and =~.

Here is the source:

require "find"
# Written by Rory Winston <rwinston@apache.org>
#
@@asl_patt_10 = /(\/\*(.*) \* The Apache Software License, Version 1.1(.*?)\*?(?!\/)\*\/)/mis
@@asl_patt_other = /(\/\*(.*) \* Copyright 200[0-9](-200[0-9])? The Apache Software Foundation(.*?)\*?(?!\/)\*\/)/mis

# New corrected header - Leave as-is
@@new_asl_pattern = /(\/\*(.*) \* Licensed to the Apache Software Foundation (ASF)(.*?)\*?(?!\/)\*\/)/mis

/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements.  See the NOTICE file distributed with
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License.  You may obtain a copy of the License at
*
*
* Unless required by applicable law or agreed to in writing, software
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
*/
EOL

@prompt = true

def initialize(dir)
@dir = dir
@str = ""
end

def initialize(dir, prompt)
@dir = dir
@prompt = prompt
end

def scan
Find.find(@dir) do |path|
Find.prune if [".",".."].include? path
end
end

@str = ""
@filename = filename
@str.gsub!("\015", "")
scanFile
end

def scanFile
if @str =~ @@asl_patt_10
puts "Found Apache 1.x License in #{@filename}"
elsif @str =~ @@asl_patt_20
puts "Found other Apache 2.0 License in #{@filename}"
elsif @str =~ @asl_patt_other
puts "Found Apache 1/2 license in file in #{@filename}"
elsif @str =~ @new_asl_pattern
puts "Correct license found in #{@filename}"
else
puts "No license found in #{@filename}"
end
end

# Replace an existing Apache 2.0 license with the new format
resp = confirm "Replace license in #{@filename}"

case resp
when "y","Y"
# Open existing file and truncate
srcFile = File.new(@filename, "w+")

srcFile.close
when "n", "N"
puts "Skipping..."
end
end

# Insert a new license at the beginning of the file
resp = confirm "Insert license in #{@filename}"

case resp
when "y","Y"
srcFile = File.new(@filename, File::TRUNC | File::RDWR)
srcFile.puts @str
srcFile.close
when "n", "N"
puts "Skipping..."
end

end

def confirm(msg)
if @prompt != true
puts "#{msg} [y/n]?"
resp = gets
resp.chomp!

while (resp !~ /[YyNn]/)
puts "#{msg} [y/n]?"
resp = gets
resp.chomp!
end
else
resp = "y"
end

resp
end

end