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):

The spreadsheet is available for download here, and the code is below for reference.

**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**