The book is "modeling financial markets with Excel and VBA ".
Modeling tick data. then produce bar data chart. later found that the return is approximately normal.
Modeling time series data. (like using an SDE and volatility estimation methods involved )(to be continued)
Simulating European Call Option
Function Sim_Eur_Call(S As Double, X As Double, r As Double, _
t As Double, sigma As Double) As Double
Dim sum_payoffs As Double
Dim i As Integer
For i = 1 To 1000
ST = S * Exp(Application.NormSInv(Rnd) * sigma * Sqr(t))
sum_payoffs = sum_payoffs + Max(ST - X, 0#)
Next i
Sim_Eur_Call = Exp(-r * t) * (sum_payoffs / 1000)
End Function
Function Max(a As Double, b As Double) As Double
If a >= b Then
Max = a
Else
Max = b
End If
End Function
Binomial Tree way
BS Formula way
Public Function BS_Eur_Call(S As Double, X As Double, r As Double, _
t As Double, sigma As Double) As Double
Dim d1 As Double
Dim d2 As Double
d1 = (Log(S / X) + (r + sigma ^ 2 / 2) * t) / (sigma * Sqr(t))
d2 = d1 - sigma * Sqr(t)
BS_Eur_Call = S * Application.NormSDist(d1) - X * Exp(-r * t) * _
Application.NormSDist(d2)
End Function
Calculating implied volatility(二分法找方程根)
Function Implied_Vol(S As Double, X As Double, r As Double, _
t As Double, price As Double) As Double
Dim High As Double
Dim Low As Double
Dim test_price As Double
Dim test_vol As Double
High = 1
Low = 0
Do While (High - Low) > 0.00001
test_vol = (High + Low) / 2
test_price = BS_Eur_Call(S, X, r, t, test_vol)
If (test_price > price) Then
High = test_vol
Else
Low = test_vol
End If
Loop
Implied_Vol = test_vol
End Function
Calculating American Option
