Help!

VBA for bisection method to find volatility--> What's wron..

 
  

Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Query DAO RSS
Next:  how to query a data range?  
Author Message
eliasjohnk
External


Since: Jun 23, 2007
Posts: 2



PostPosted: Sat Jun 23, 2007 10:44 am    Post subject: VBA for bisection method to find volatility--> What's wrong with my code?
Archived from groups: microsoft>public>excel>querydao (more info?)

It get #Name when I insert the function-> my problem is with
ImpliedVol2--> I defined the option pricing in the other functions.

Option Explicit
Function ImpliedVol2(optType, S0, K, rcinterest, q, T, trueprice)

Dim optionpricelow As Double
Dim optionpricehigh As Double
Dim lowervol As Double
Dim uppervol As Double
Dim sigma As Double
Dim EJ As Double

sigma = 5
optionpricelow = BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)
sigma = 200
optionpricehigh = BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)

If optionpricehigh - trueprice > trueprice - optionpricelow Then
uppervol = (200 + 5) / 2
lowervol = 5
Else
uppervol = 200
lowervol = (200 + 5) / 2
End If

Do While EJ > 0.0001

sigma = lowervol
optionpricelow = BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)
sigma = uppervol
optionpricehigh = BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)

If optionpricehigh - trueprice > trueprice - optionpricelow Then
uppervol = (uppervol + lowervol) / 2
lowervol = lowervol
Else
uppervol = uppervol
lowervol = (uppervol + lowervol) / 2

End If

EJ = optionpricehigh - optionpricelow
Loop

ImpliedVol2 = sigma

End Function



Function BSMOptPrice(optType, S0, K, rcinterest, q, T, sigma)

' Calculates Black-Scholes-Merton option price
' optType = 1 for call, -1 for put
' This function uses Functions BSD1 and BSD2

Dim exprT, expqT, ND1, ND2

If S0 > 0 And K > 0 And T > 0 And sigma > 0 Then
exprT = Exp(-rcinterest * T)
expqT = Exp(-q * T)
ND1 = Application.NormSDist(optType * _
BSD1(S0, K, rcinterest, q, T, sigma))
ND2 = Application.NormSDist(optType * _
BSD2(S0, K, rcinterest, q, T, sigma))
BSMOptPrice = optType * (S0 * expqT * ND1 - _
K * exprT * ND2)
ElseIf S0 > 0 And K > 0 And sigma > 0 And T = 0 Then
BSMOptPrice = Application.Max(0, optType * (S0 - K))
Else
'MsgBox "One of the inputs provided is invalid"
BSMOptPrice = 0
End If

End Function


Private Function BSD1(S0, K, rcinterest, q, T, sigma)

' Calculates D1 for Balck-Scholes-Merton option pricing

BSD1 = (Log(S0 / K) + (rcinterest - q + 0.5 * sigma ^ 2) * T) / _
(sigma * Sqr(T))

End Function


Private Function BSD2(S0, K, rcinterest, q, T, sigma)

' Calculates D2 for Balck-Scholes-Merton option pricing

BSD2 = BSD1(S0, K, rcinterest, q, T, sigma) - (sigma * Sqr(T))

End Function


Please help.
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Query DAO All times are: Eastern Time (US & Canada) (change)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum