這是SHEET 1的程式碼

Public Sub jj()

Dim S As Double
Dim K As Double
Dim T As Double
Dim sig As Double
Dim r As Double
Dim y As Double
Dim X As Double
Dim CPrice As Double, PPrice As Double


S = Worksheets("sheet1").Range("B2").Value
K = Worksheets("sheet1").Range("C2").Value
X = Worksheets("sheet1").Range("D2").Value
T = Worksheets("sheet1").Range("E2").Value
sig = Worksheets("sheet1").Range("F2").Value
r = Worksheets("sheet1").Range("G2").Value
y = Worksheets("sheet1").Range("H2").Value

CPrice = CashOrNothing("C", S, K, X, T, sig, r, y)
PPrice = CashOrNothing("P", S, K, X, T, sig, r, y)

Worksheets("sheet1").Range("B7").Value = CPrice
Worksheets("sheet1").Range("C7").Value = PPrice




End Sub




這是MODULE 的程式碼
'// cash or nothing option


Public Function NorPdf(d As Double) As Double
Dim ans As Double

Const pi = 3.14159265358979
ans = Exp(-d * d / 2) / Sqr(2 * pi)
NorPdf = ans

End Function


Public Function NorCdf(d As Double) As Double
Dim ans As Double
Dim g As Double

Const a1 = 0.4361836
Const a2 = -0.1201676
Const a3 = 0.937298

g = 1 / (1 + 0.33267 * d)

If d >= 0 Then
ans = 1 - (a1 * g + a2 * g * g + a3 * g * g * g) * NorPdf(d)
Else
ans = 1 - NorCdf(-d)
End If
NorCdf = ans

End Function



Public Function CashOrNothing(ClassFlag As String, S As Double, K As Double, X As Double, T As Double, r As Double, y As Double, sig As Double) As Double
Dim d As Double
d = (Log(S / K) + (r - y - sig ^ 2 / 2) * T) / (sig * Sqr(T))

If ClassFlag = " C " Then
   CashOrNothing = X * Exp(-r * T) * NorCdf(d)
ElseIf ClassFlag = " P " Then
   CashOrNothing = X * Exp(-r * T) * NorCdf(-d)
End If

    Range("B6").Select
    ActiveCell.FormulaR1C1 = "call"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "put"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "premium"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "S"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "K"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "X"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "T"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "sig"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "r"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "y"




End Function