Related Topics
George (3)
It's often desirable to get live financial data and everyone knows. XML is the thing to use but actually writing programs that work takes a bit of trouble. Plus, once you've got the data you need to display it.
Popular Passages
New topic 2013-02-05 15:24:06 description
The provenance is numerous sources including Wilmott, Hull, and Benninga but I wrote the majority of this myself while I was at MIT in the Master of Finance program.
Option Explicit
' Black Scholes Merton Excel VBA George Fisher MIT Fall 2011
' d1, d2
' N (en) std normal CDF
' N' (nprime) std normal PDF
'
' Binary Options
' Euro Call & Put; Black's Model Call & Put
' Black's Formulation
' Greeks
' Implied Volatility
' Put/Call Parity
' American_Call_Dividend
' American_Put_Binomial
'
' Also includes
' risk-neutral prob
' nodal value of a binimial tree
' Monte Carlo terminal value of one stock path
' Monte Carlo next-step along one stock path
' forward prices & rates
' CAGR
' randn/randn_ssdt
' cell-display functions
' convert discrete to continuous interest rate
' Developer > Visual Basic (Alt_F11)
' Insert > Module
' Interest is
' risk-free rate
' domestic risk-free rate for currencies
' Yield is
' dividend yield for a stock
' lease rate for commodities
' foreign currency risk-free rate for currencies
' Sigma is the standard deviation of the underlying asset
' Time is a year fraction: for 3-months ... Time = 3/12
' Stock is S_0
' Exercise is K
' => Interest, Yield, Sigma, Time are all annual numbers
' => Time = 0 is the value at maturity
' most of the functions accommodate this
' for some, it's infinity or otherwise meaningless
' => Sigma = 0 is also accommodated in most functions
''
'' Utilities
'' ---------
''
' N: the standard-normal CDF
Function en(x)
en = Application.NormSDist(x)
End Function
' N': the first derivative of N(x) ... the standard normal PDF
Function prime(x As Double) As Double
nprime = Exp(-0.5 * x * x) / Sqr(2 * 3.1415926)
End Function
' Random Normal (epsilon)
Function RandN()
' produces a standard normal random variable epsilon
RandN = Application.NormSInv(Rnd())
End Function
Function RandN_ssdt(ssdt)
' produces a standard normal random variable epsilon times sigma*sqrt(deltaT)
RandN_ssdt = Application.NormInv(Rnd(), 0, ssdt)
End Function
' Functions to display formula in a cell (from Benning)
Function GetFormula(Rng As Range) As String
Application.Volatile True
GetFormula = "<-- " & Application.Text(Rng.FormulaLocal, "")
End Function
Function GgetFormula(Rng As Range) As String
Application.Volatile True
GgetFormula = Application.Text(Rng.FormulaLocal, "")
End Function
' binomial tree risk-neutral probability (Hull 7th edition Ch 19 P 409)
Function RiskNeutralProb(Interest, Yield, sigma, deltaT)
Dim u As Double
u = Exp(sigma * Sqr(deltaT))
Dim d As Double
d = Exp(-sigma * Sqr(deltaT))
Dim a As Double
a = Exp((Interest - Yield) * deltaT)
Dim numerator As Double
numerator = a - d
Dim denominator As Double
denominator = u - d
RiskNeutralProb = numerator / denominator
End Function
' value of node j at time t in a binomial tree ***NOT DEBUGGED***
' t starts at zero (on the left)
' j starts at zero (at the bottom ... all downs)
Function BinomialValue(S_0, u, d, T, j)
BinomialValue = S_0 * u ^ j * d ^ (T - j)
End Function
' terminal value of one stock path (one of many) for Monte Carlo simulation
Function MCterm(S_0, Interest, sigma, Time)
MCterm = S_0 * Exp((Interest - 0.5 * sigma * sigma) * Time + sigma * RandN() * Sqr(Time))
End Function
' next step along a path for Monte Carlo simulation
Function MCnextStep(prior_value, Interest, sigma, deltaT)
MCnextStep = prior_value + prior_value * (Interest * deltaT + sigma * RandN() * Sqr(deltaT))
End Function
' next step along a path for Monte Carlo simulation;
' Antithetic Variable ***NOT SURE ABOUT THIS***
Function MCnextStepAV(prior_value, Interest, sigma, deltaT)
Dim f1, f2 As Double
f1 = prior_value + prior_value * (Interest * deltaT + sigma * RandN() * Sqr(deltaT))
f2 = prior_value + prior_value * (Interest * deltaT + sigma * -RandN() * Sqr(deltaT))
MCnextStepAV = (f1 + f2) / 2
End Function
' Call & Put prices derived from put-call parity
Function CallParity(Stock, Exercise, Time, Interest, Yield, Put_price)
CallParity = Put_price + _
Stock * Exp(-Yield * Time) - _
Exercise * Exp(-Interest * Time)
End Function
Function PutParity(Stock, Exercise, Time, Interest, Yield, Call_price)
PutParity = Call_price + _
Exercise * Exp(-Interest * Time) - _
Stock * Exp(-Yield * Time)
End Function
' forward price
Function forward price(Spot, Time, Interest, Yield)
ForwardPrice = Spot * Exp((Interest - Yield) * Time)
End Function
' forward rate from Time1 to Time2 (discrete compounding)
Function ForwardRate(SpotInterest1, Time1, SpotInterest2, Time2)
Dim numerator As Double
numerator = (1 + SpotInterest2) ^ Time2
Dim denominator As Double
denominator = (1 + SpotInterest1) ^ Time1
ForwardRate = ((numerator / denominator) ^ (1 / (Time2 - Time1))) - 1
End Function
' CAGR
Function CAGR(Starting_value, Ending_Value, Number_of_years, Optional form)
' the default for is discrete; the alternative is continuous
' the parameter "form" is optional; leave out for discrete, put in a zero for continuous
If IsMissing(form) Then
CAGR = ((Ending_Value / Starting_value) ^ (1 / Number_of_years)) - 1
Else
CAGR = Log(Ending_Value / Starting_value) / Number_of_years
End If
End Function
Function r_continuous(r_discrete, compounding_periods_per_year)
Dim m As Double
m = compounding_periods_per_year
r_continuous = m * Log(1 + r_discrete / m)
End Function
' Convert TO discrete compounding FROM continuous
'
' t_discrete = m * (exp(r_continuous / m) - 1)
'
' where m is the number of compounding periods per year
'
Function r_discrete(r_continuous As Double, m As Integer) As Double
r_discrete = m * (Exp(r_continuous / m) - 1)
End Function
' --------------------------------------------------------------------------------
''
'' Black Scholes
'' -------------
''
Function dOne(Stock, Exercise, Time, Interest, Yield, sigma)
' (365 * 24 * 60 * 60) = number of seconds in a year = 31,536,000
' 1 / (365 * 24 * 60 * 60) = 3.17097919837646E-08
' the year fraction of a second
If Time <= 0 Then Time = 1E-20
dOne = (Log(Stock / Exercise) + (Interest - Yield + 0.5 * sigma * sigma) * Time) _
/ (sigma * Sqr(Time))
End Function
Function dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
' (365 * 24 * 60 * 60) = number of seconds in a year = 31,536,000
' 1 / (365 * 24 * 60 * 60) = 3.17097919837646E-08
' the year fraction of a second
If Time <= 0 Then Time = 1E-20
dTwo = (Log(Stock / Exercise) + (Interest - Yield - 0.5 * sigma * sigma) * Time) _
/ (sigma * Sqr(Time))
End Function
'
' Binary Options
'
' Digital: Cash or Nothing
Function CashCall(Stock, Exercise, Time, Interest, Yield, sigma)
If Time < 0.000000005 Then
If Stock >= Exercise Then
CashCall = 1
Exit Function
Else
CashCall = 0
Exit Function
End If
End If
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d2_, Nd2
d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
Nd2 = Application.NormSDist(d2_)
CashCall = Exp(-Interest * Time) * Nd2
End Function
Function CashPut(Stock, Exercise, Time, Interest, Yield, sigma)
If Time < 0.000000005 Then
If Stock >= Exercise Then
CashPut = 0
Exit Function
Else
CashPut = 1
Exit Function
End If
End If
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d2_, Nminusd2
d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
Nminusd2 = Application.NormSDist(-d2_)
CashPut = Exp(-Interest * Time) * Nminusd2
End Function
' Asset or Nothing
Function AssetCall(Stock, Exercise, Time, Interest, Yield, sigma)
If Time < 0.000000005 Then
If Stock >= Exercise Then
AssetCall = Stock
Exit Function
Else
AssetCall = 0
Exit Function
End If
End If
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d1_, Nd1
If Exercise < 0 Then Exit Function
If Exercise < 0.000000005 Then
Nd1 = 1
Else
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
Nd1 = Application.NormSDist(d1_)
End If
AssetCall = Stock * Exp(-Yield * Time) * Nd1
End Function
Function AssetPut(Stock, Exercise, Time, Interest, Yield, sigma)
If Time < 0.000000005 Then
If Stock >= Exercise Then
AssetPut = 0
Exit Function
Else
AssetPut = Stock
Exit Function
End If
End If
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d1_, Nminusd1
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
Nminusd1 = Application.NormSDist(-d1_)
AssetPut = Stock * Exp(-Yield * Time) * Nminusd1
End Function
'
' Black's Formulation
'
Function BFormulationCall(Forward, Exercise, Time, Interest, Yield, sigma)
Dim d1 As Double, d2 As Double
d1 = (Log(Forward / Exercise) / (sigma * Sqr(Time))) + ((sigma * Sqr(Time)) / 2)
d2 = (Log(Forward / Exercise) / (sigma * Sqr(Time))) - ((sigma * Sqr(Time)) / 2)
BFormulationCall = Exp(-Interest * Time) * (Forward * en(d1) - Exercise * en(d2))
End Function
'
' European Call and Put
'
Function EuroCall(Stock, Exercise, Time, Interest, Yield, sigma)
If Time = 0 Then
EuroCall = Application.Max(0, Stock - Exercise)
Exit Function
End If
If sigma = 0 Then
EuroCall = Application.Max(0, Exp(-Yield * Time) * Stock - Exp(-Interest * Time) * Exercise)
Exit Function
End If
Dim d1_ As Double, d2_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
EuroCall = Stock * Exp(-Yield * Time) * Application.NormSDist(d1_) _
- Exercise * Exp(-Interest * Time) * Application.NormSDist(d2_)
End Function
Function EuroPut(Stock, Exercise, Time, Interest, Yield, sigma)
If Time = 0 Then
EuroPut = Application.Max(0, Exercise - Stock)
Exit Function
End If
If sigma = 0 Then
EuroPut = Application.Max(0, Exp(-Interest * Time) * Exercise - Exp(-Yield * Time) * Stock)
Exit Function
End If
EuroPut = Exercise * Exp(-Interest * Time) * _
Application.NormSDist(-dTwo(Stock, Exercise, Time, Interest, Yield, sigma)) - _
Stock * Exp(-Yield * Time) * Application.NormSDist(-dOne(Stock, Exercise, _
Time, Interest, Yield, Sigma))
End Function
'
' Black's Model
'
Function VeronessiCaplet(n, Z, f, K, Sigma, Tminus1)
' caplet for period T_i+1
'
' n = time in years between periods (1 for a year; 0.25 for quarterly)
' Z = discount rate Z(0, T_i+1)
' f = forward rate f(0, T_i, T_i+1) = (1/n) * [Z(0, T_i) / Z(0, T_i+1) -1]
' K = exercise rate/cap rate
' sigma = annual vol of f
' Tminus1 = T_i
'
Dim d1_ As Double, d2_ As Double
d1_ = (1 / (sigma * Sqr(Tminus1))) * Log(f / K) + 0.5 * sigma * Sqr(Tminus1)
d2_ = d1_ - sigma * Sqr(Tminus1)
VeronessiCaplet = n * (Z * 100) * (f * Application.NormSDist(d1_) - K * Application.NormSDist(d2_))
End Function
Function BlacksModelCall(Forward, Exercise, Time, Spot_Yield_to_term_of_option, vol_of_forward)
Dim Interest As Double, sigma As Double
Interest = Spot_Yield_to_term_of_option
sigma = vol_of_forward
If Time = 0 Then
BlacksModelCall = Application.Max(0, Forward - Exercise)
Exit Function
End If
If sigma = 0 Then
BlacksModelCall = Application.Max(0, Exp(-Interest * Time) * (Forward - Exercise))
Exit Function
End If
Dim d1_ As Double, d2_ As Double
d1_ = (Log(Forward / Exercise) + ((1 / 2) * sigma ^ 2) * Time) / (sigma * Sqr(Time))
d2_ = (Log(Forward / Exercise) - ((1 / 2) * sigma ^ 2) * Time) / (sigma * Sqr(Time))
BlacksModelCall = Exp(-Interest * Time) * (Forward * Application.NormSDist(d1_) _
- Exercise * Application.NormSDist(d2_))
End Function
Function BlacksModelPut(Forward, Exercise, Time, Spot_Yield_to_term_of_option, vol_of_forward)
Dim Interest As Double, sigma As Double
Interest = Spot_Yield_to_term_of_option
sigma = vol_of_forward
If Time = 0 Then
BlacksModelPut = Application.Max(0, Exercise - Forward)
Exit Function
End If
If sigma = 0 Then
BlacksModelPut = Application.Max(0, Exp(-Interest * Time) * (Exercise - Forward))
Exit Function
End If
Dim d1_ As Double, d2_ As Double
d1_ = (Log(Forward / Exercise) + ((1 / 2) * sigma ^ 2) * Time) / (sigma * Sqr(Time))
d2_ = (Log(Forward / Exercise) - ((1 / 2) * sigma ^ 2) * Time) / (sigma * Sqr(Time))
BlacksModelPut = Exp(-Interest * Time) * (Exercise * Application.NormSDist(-d2_) _
- Forward * Application.NormSDist(-d1_))
End Function
'
' Per Kerry Back Chapt5.bas
'
Function American_Put_Binomial(S0, K, r, sigma, q, T, n)
'
' Inputs are S0 = initial stock price
' K = strike price
' r = risk-free rate
' sigma = volatility
' q = dividend yield
' T = time to maturity
' N = number of time periods
'
Dim dt, u, d, pu, dpu, dpd, u2, S, i, j
Dim PutV() As Double
ReDim PutV(n)
dt = T / n ' length of time period
u = Exp(sigma * Sqr(dt)) ' size of up step
d = 1 / u ' size of down step
pu = (Exp((r - q) * dt) - d) / (u - d) ' probability of up step
dpu = Exp(-r * dt) * pu ' one-period discount x prob of up step
dpd = Exp(-r * dt) * (1 - pu) ' one-period discount x prob of down step
u2 = u * u
S = S0 * d ^ n ' stock price at bottom node at last date
PutV(0) = Application.Max(K - S, 0) ' put value at bottom node at last date
For j = 1 To n
S = S * u2
PutV(j) = Application.Max(K - S, 0)
Next j
For i = n - 1 To 0 Step -1 ' back up in time to date 0
S = S0 * d ^ i ' stock price at bottom node at date i
PutV(0) = Application.Max(K - S, dpd * PutV(0) + dpu * PutV(1))
For j = 1 To i ' step up over nodes at date i
S = S * u2
PutV(j) = Application.Max(K - S, dpd * PutV(j) + dpu * PutV(j + 1))
Next j
Next i
American_Put_Binomial = PutV(0) ' put value at bottom node at date 0
End Function
'
' from Kerry Back Chapt8.bas
'
Function American_Call_Dividend(S, K, r, sigma, Div, TDiv, TCall)
'
' Inputs are S = initial stock price
' K = strike price
' r = risk-free rate
' sigma = volatility
' Div = cash dividend
' TDiv = time until dividend payment
' TCall = time until option matures >= TDiv
'
Dim LessDiv, upper, tol, lower, flower, fupper, guess, fguess
Dim LessDivStar, d1, d2, d1prime, d2prime, rho, N1, N2, M1, M2
LessDiv = S - Exp(-r * TDiv) * Div ' stock value excluding dividend
If Div / K <= 1 - Exp(-r * (TCall - TDiv)) Then ' early exercise cannot be optimal
American_Call_Dividend = Black_Scholes_Call(LessDiv, K, r, sigma, 0, TCall)
Exit Function
End If
'
' Now we find an upper bound for the bisection.
'
upper = K
Do While upper + Div - K < Black_Scholes_Call(upper, K, r, sigma, 0, TCall - TDiv)
upper = 2 * upper
Loop
'
' Now we use bisection to compute Zstar = LessDivStar.
'
tol = 10 ^ -6
lower = 0
flower = Div - K
fupper = upper + Div - K - Black_Scholes_Call(upper, K, r, sigma, 0, TCall - TDiv)
guess = 0.5 * lower + 0.5 * upper
fguess = guess + Div - K - Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)
Do While upper - lower > tol
If fupper * fguess < 0 Then
lower = guess
flower = fguess
guess = 0.5 * lower + 0.5 * upper
fguess = guess + Div - K _
- Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)
Else
upper = guess
fupper = fguess
guess = 0.5 * lower + 0.5 * upper
fguess = guess + Div - K _
- Black_Scholes_Call(guess, K, r, sigma, 0, TCall - TDiv)
End If
Loop
LessDivStar = guess
'
' Now we calculate the probabilities and the option value.
'
d1 = (Log(LessDiv / LessDivStar) _
+ (r + sigma ^ 2 / 2) * TDiv) / (sigma * Sqr(TDiv))
d2 = d1 - sigma * Sqr(TDiv)
d1prime = (Log(LessDiv / K) _
+ (r + sigma ^ 2 / 2) * TCall) / (sigma * Sqr(TCall))
d2prime = d1prime - sigma * Sqr(TCall)
rho = -Sqr(TDiv / TCall)
N1 = Application.NormSDist(d1)
N2 = Application.NormSDist(d2)
M1 = BiNormalProb(-d1, d1prime, rho)
M2 = BiNormalProb(-d2, d2prime, rho)
American_Call_Dividend = LessDiv * N1 + Exp(-r * TDiv) * (Div - K) * N2 _
+ LessDiv * M1 - Exp(-r * TCall) * K * M2
End Function
'
' Greeks from Hull (Edition 7) Chapter 17 p378
' --------------------------------------------
'
' per the Black Scholes PDE for a portfolio of options
' on a single non-dividend-paying underlying stock
'
' Theta + Delta * S * r + Gamma * 0.5 * sigma^2 * S^2 = r * Portfolio_Value
' Per Hull: for large option portfolios, usually created by banks in the
' course of buying and selling OTC options to clients, the portfolio is
' Delta hedged every day and Gamma/Vega hedged as needed
'
' Delta Gamma Vega
' Portfolio P_delta P_gamma P_vega
' Option1 w1*1_delta w1*1_gamma w1*1_vega
' Option2 w2*2_delta w2*2_gamma w2*2_vega
'
' Set the columns equal to zero and solve the simultaneous equations
' Most OTC options are sold close to the money; high gamma and vega
' as (if) the price of the underlying move away gamma and vega decline
' Delta
' -----
'
' If a bank sells a call to a client (short a call)
' ... it hedges itself with a synthetic long call
'
' Synthetic long call = Delta * Stock_price - bond
' ie., borrow the money to buy Delta shares of the stock
'
Function DeltaCall(Stock, Exercise, Time, Interest, Yield, sigma)
If Time = 0 Then
If Stock > Exercise Then
DeltaCall = 1
Exit Function
Else
DeltaCall = 0
Exit Function
End If
End If
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d1_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
DeltaCall = Exp(-Yield * Time) * Application.NormSDist(d1_)
End Function
Function DeltaPut(Stock, Exercise, Time, Interest, Yield, sigma)
If Time = 0 Then
If Stock < Exercise Then
DeltaPut = -1
Exit Function
Else
DeltaPut = 0
Exit Function
End If
End If
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d1_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
DeltaPut = Exp(-Yield * Time) * (Application.NormSDist(d1_) - 1)
End Function
'
' Gamma the convexity
' -----
'
Function OptionGamma(Stock, Exercise, Time, Interest, Yield, sigma)
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d1_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
OptionGamma = nprime(d1_) * Exp(-Yield * Time) _
/ (Stock * sigma * Sqr(Time))
End Function
'
' Theta the decay in the value of an option/portfolio of options as time passes
' -----
'
' divide by 365 for "per calendar day"; 252 for "per trading day"
'
' In a delta-neutral portfolio, Theta is a proxy for Gamma
'
Function ThetaCall(Stock, Exercise, Time, Interest, Yield, sigma)
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d1_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
Dim d2_ As Double
d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
Dim Nd1_ As Double
Nd1_ = Application.NormSDist(d1_)
Dim Nd2_ As Double
Nd2_ = Application.NormSDist(d2_)
ThetaCall = -Stock * nprime(d1_) * sigma * Exp(-Yield * Time) / (2 * Sqr(Time)) _
+ Yield * Stock * Nd1_ * Exp(-Yield * Time) _
- Interest * Exercise * Exp(-Interest * Time) * Nd2_
End Function
Function ThetaPut(Stock, Exercise, Time, Interest, Yield, sigma)
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d1_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
Dim d2_ As Double
d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
Dim Nminusd1_ As Double
Nminusd1_ = Application.NormSDist(-d1_)
Dim Nminusd2_ As Double
Nminusd2_ = Application.NormSDist(-d2_)
ThetaPut = -Stock * nprime(d1_) * sigma * Exp(-Yield * Time) / (2 * Sqr(Time)) _
- Yield * Stock * Nminusd1_ * Exp(-Yield * Time) _
+ Interest * Exercise * Exp(-Interest * Time) * Nminusd2_
End Function
'
' Vega the sensitivity to changes in the volatility of the underlying
' ----
'
Function Vega(Stock, Exercise, Time, Interest, Yield, sigma)
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d1_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
Vega = Stock * Sqr(Time) * nprime(d1_) * Exp(-Yield * Time)
End Function
'
' Rho the sensitivity to changes in the interest rate
' ---
'
'
' Note the various Rho calculations see Hull 7th Edition Ch 17 P378
'
Function RhoFuturesCall(Stock, Exercise, Time, Interest, Yield, sigma)
RhoFuturesCall = -EuroCall(Stock, Exercise, Time, Interest, Yield, sigma) * Time
End Function
Function RhoFuturesPut(Stock, Exercise, Time, Interest, Yield, sigma)
RhoFuturesPut = -EuroPut(Stock, Exercise, Time, Interest, Yield, sigma) * Time
End Function
'
' The Rho corresponding to the domestic interest rate is RhoCall/Put, below
' foreign interest rate is RhoFXCall/Put, shown here
'
Function RhoFXCall(Stock, Exercise, Time, Interest, Yield, sigma)
Dim d1_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
Dim Nd1_ As Double
Nd1_ = Application.NormSDist(d1_)
RhoFXCall = -Time * Exp(-Yield * Time) * Stock * Nd1_
End Function
Function RhoFXPut(Stock, Exercise, Time, Interest, Yield, sigma)
Dim d1_ As Double
d1_ = dOne(Stock, Exercise, Time, Interest, Yield, sigma)
Dim Nminusd1_ As Double
Nminusd1_ = Application.NormSDist(-d1_)
RhoFXPut = Time * Exp(-Yield * Time) * Stock * Nminusd1_
End Function
'
' "Standard" Rhos
'
Function RhoCall(Stock, Exercise, Time, Interest, Yield, sigma)
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d2_ As Double
d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
Dim Nd2_ As Double
Nd2_ = Application.NormSDist(d2_)
RhoCall = Exercise * Time * Exp(-Interest * Time) * Nd2_
End Function
Function RhoPut(Stock, Exercise, Time, Interest, Yield, sigma)
If sigma = 0 Then
sigma = 0.0000000001
End If
Dim d2_ As Double
d2_ = dTwo(Stock, Exercise, Time, Interest, Yield, sigma)
Dim Nminusd2_ As Double
Nminusd2_ = Application.NormSDist(-d2_)
RhoPut = -Exercise * Time * Exp(-Interest * Time) * Nminusd2_
End Function
'
' Since Bennigna and Back produce identical numbers
' and MATLAB produced numbers that are +/- 2%, I'm
' inclined to go with these numbers
'
'
' Implied Volatility from Benningna
' ---------------------------------
'
Function EuroCallVol(Stock, Exercise, Time, Interest, Yield, Call_price)
Dim High, Low As Double
High = 2
Low = 0
Do While (High - Low) > 0.000001
If EuroCall(Stock, Exercise, Time, Interest, Yield, (High + Low) / 2) > _
Call_price Then
High = (High + Low) / 2
Else: Low = (High + Low) / 2
End If
Loop
EuroCallVol = (High + Low) / 2
End Function
Function EuroPutVol(Stock, Exercise, Time, Interest, Yield, Put_price)
Dim High, Low As Double
High = 2
Low = 0
Do While (High - Low) > 0.000001
If EuroPut(Stock, Exercise, Time, Interest, Yield, (High + Low) / 2) > _
Put_price Then
High = (High + Low) / 2
Else: Low = (High + Low) / 2
End If
Loop
EuroPutVol = (High + Low) / 2
End Function
'
' Implied Volatility from Kerry Back p64
' Chapt3.bas Newton Raphson technique
' Answer IDENTICAL to Bennigna (EuroCallVol)
'
Function Black_Scholes_Call(S, K, r, sigma, q, T)
Black_Scholes_Call = EuroCall(S, K, T, r, q, sigma)
End Function
Function Black_Scholes_Call_Implied_Vol(S, K, r, q, T, CallPrice)
'
' Inputs are S = initial stock price
' K = strike price
' r = risk-free rate
' q = dividend yield
' T = time to maturity
' CallPrice = call price
'
Dim tol, lower, flower, upper, fupper, guess, fguess
If CallPrice < Exp(-q * T) * S - Exp(-r * T) * K Then
MsgBox ("Option price violates the arbitrage bound.")
Exit Function
End If
tol = 10 ^ -6
lower = 0
flower = Black_Scholes_Call(S, K, r, lower, q, T) - CallPrice
upper = 1
fupper = Black_Scholes_Call(S, K, r, upper, q, T) - CallPrice
Do While fupper < 0 ' double upper until it is an upper bound
upper = 2 * upper
fupper = Black_Scholes_Call(S, K, r, upper, q, T) - CallPrice
Loop
guess = 0.5 * lower + 0.5 * upper
fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
Do While upper - lower > tol ' until root is bracketed within tol
If supper * guess < 0 Then ' root is between the guess and upper
lower = guess ' make guess the new lower bound
flower = fguess
guess = 0.5 * lower + 0.5 * upper ' new guess = bi-section
fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
Else ' root is between lower and guess
upper = guess ' make guess the new upper bound
fupper = fguess
guess = 0.5 * lower + 0.5 * upper ' new guess = bi-section
fguess = Black_Scholes_Call(S, K, r, guess, q, T) - CallPrice
End If
Loop
Black_Scholes_Call_Implied_Vol = guess
End Function
'
' Implied Volatility from Wilmott Into Ch 8 p192 Newton Raphson***NOT DEBUGGED***
'
Function ImpVolCall(Stock, Exercise, Time, Interest, Yield, Call_price)
Volatility = 0.2
epsilon = 0.0001
dv = epsilon + 1
While Abs(dv) > epsilon
PriceError = EuroCall(Stock, Exercise, Time, Interest, Yield, Volatility) - Call_price
dv = PriceError / Vega(Stock, Exercise, Time, Interest, Yield, Volatility)
Volatility = Volatility - dv
Wend
ImpVolCall = Volatility
End Function
My thanks to Encode / Decode HTML Entities
Originally published: Friday, December 14, 2012; most-recently modified: Tuesday, May 14, 2019