Make your own free website on Tripod.com
Black-Scholes Model
INPUT
Stock Price 60 <-- C4 Side Calculations
Strike Price 65 <-- C5 for Formulas
Volatility (SD) 0.3 E6 --> =(LN(C4/C5)+(C7+C6^2/2)*C8)/(C6*SQRT(C8)) `
Risk Free Rate 0.08 E7 --> =E6 - C6*SQRT(C8)
Term (Years) =1/4 <-- C8
OUTPUT
VBA (or Add-in) Formulas
European Call =Call_BSE(C4,C5,C6,C7,C8) =C4*NORMSDIST(E6)-C5*EXP(-C7*C8)*NORMSDIST(E7)
European Put =Put_BSE(C4,C5,C6,C7,C8) =C5*EXP(-C7*C8)*NORMSDIST(-E7)-C4*NORMSDIST(-E6)
VBA Code
Public Function Call_BSE(Price, Strike, Vol, Rate, Term)
    Dim d1 As Double
    Dim d2 As Double
    d1 = (Log(Price / Strike) + (Rate + 0.5 * Vol ^ 2) * Term) / (Vol * (Term ^ 0.5))
    d2 = d1 - Vol * (Term ^ 0.5)
    Call_BSE = Price * WorksheetFunction.NormSDist(d1) - Strike * Exp(-Rate * Term) * WorksheetFunction.NormSDist(d2)
End Function
Public Function Put_BSE(Price, Strike, Vol, Rate, Term)
    Dim d1 As Double
    Dim d2 As Double
    d1 = (Log(Price / Strike) + (Rate + 0.5 * Vol ^ 2) * Term) / (Vol * (Term ^ 0.5))
    d2 = d1 - Vol * (Term ^ 0.5)
    Put_BSE = Strike * Exp(-Rate * Term) * WorksheetFunction.NormSDist(-d2) - Price * WorksheetFunction.NormSDist(-d1)
End Function