WUGNET, the Windows User Group Network
Your Complete Resource Center for "The Best" in Shareware, Computing Tips and Support, Windows Industry News... and much more!
Home Forums Shareware Windows Tips Hot Offers FREE Newsletters Arcade Contact Us About Partners
Search WUGNET: RSS Feeds RSS Feeds Advertise with WUGNET    |    Shareware eBooks
HomeHome FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

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

 
   Home -> Office -> Query DAO RSS
Next:  Conditional Format order  
Author Message
eliasjohnk

External


Since: Jun 23, 2007
Posts: 2



(Msg. 1) Posted: 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
Login to vote
Display posts from previous:   
       Home -> Office -> 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
Categories:
 Windows XP
 Windows Vista
 Windows Other
  Office
 Office Other
 Security
 WinRAR
  • Home |
  • Shareware |
  • Windows Tips |
  • Hot Offers |
  • FREE Newsletters |
  • Arcade |
  • Forums |
  • eBooks |
  • About WUGNET |
  • Partners |
  • Contact

  • WUGNET Privacy Policy |
  • Link to WUGNET