Vba - Option (Callvolatility + Impliedvarcovar)

VBA_Option (CallVolatility + ImpliedVarCovar)
Function dOne(Stock, Exercise, Time, Interest, sigma)
dOne = (Log(Stock / Exercise) + Interest * Time) / (sigma * Sqr(Time)) + 0.5 * sigma * Sqr(Time)
End Function
Function dTwo(Stock, Exercise, Time, Interest, sigma)
dTwo = dOne(Stock, Exercise, Time, Interest, sigma) - sigma * Sqr(Time)
End Function
Function BSCall(Stock, Exercise, Time, Interest, sigma)
BSCall = Stock * Application.NormSDist(dOne(Stock, Exercise, Time, Interest, sigma)) - Exercise * Exp(-

Time * Interest) * _
Application.NormSDist(dTwo(Stock, Exercise, Time, Interest, sigma))
End Function
Function callVolatility(Stock, Exercise, Time, Interest, Target)
High = 2
Low = 0
Do While (High - Low) > 0.0001
If BSCall(Stock, Exercise, Time, Interest, (High + Low) / 2) > Target Then
High = (High + Low) / 2
Else: Low = (High + Low) / 2
End If
Loop
callVolatility = (High + Low) / 2
End Function
Function ImpliedVolVarCov(varcovarmatrix As Range, volatilities As Range, corr As Double) As Variant
Dim i As Integer
Dim j As Integer
Dim numcols As Integer
numcols = varcovarmatrix.Columns.Count
numrows = numcols
Dim matrix() As Double
ReDim matrix(numcols - 1, numcols - 1)
If Abs(corr) >= 1 Then GoTo Out
For i = 1 To numcols
For j = 1 To numcols
If i = j Then
matrix(i - 1, j - 1) = volatilities(i) ^ 2
Else
matrix(i - 1, j - 1) = corr * volatilities(i) * volatilities(j)
End If
Next j
Next i
Out:
If Abs(corr) >= 1 Then ImpliedVolVarCov = “ERR” Else ImpliedVolVarCov = matrix
End Function
VBA_Đường biên hiệu quả
Sub Solve()
SolverOk SetCell:="$C$28", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$17:$C$24"
SolverSolve UserFinish:=True
End Sub
Sub Doit()
Range("Results").ClearContents
For counter = 1 To 40
Range("constant") = -0.15 + counter * 0.005
Solve
Application.SendKeys ("{Enter}")
Range("Results").Cells(counter, 1) = ActiveSheet.Range("constant")
Range("Results").Cells(counter, 2) = ActiveSheet.Range("portfolio_sigma")
Range("Results").Cells(counter, 3) = ActiveSheet.Range("portfolio_mean")
Range("Results").Cells(counter, 4) = ActiveSheet.Range("crm")
Range("Results").Cells(counter, 5) = ActiveSheet.Range("abbv")
Range("Results").Cells(counter, 6) = ActiveSheet.Range("mcd")
Range("Results").Cells(counter, 7) = ActiveSheet.Range("bmy")
Range("Results").Cells(counter, 8) = ActiveSheet.Range("tmo")
Range("Results").Cells(counter, 9) = ActiveSheet.Range("cost")
Range("Results").Cells(counter, 10) = ActiveSheet.Range("amgn")
Range("Results").Cells(counter, 11) = ActiveSheet.Range("mdt")
Next counter
End Sub
Private Sub benny()
i=1
Do Until (i = AddIns.Count) Or (AddIns(i).Name = "SOLVER.XLA")
i=i+1
Loop
If (AddIns(i).Name = "SOLVER.XLA") Then
AddIns(i).Installed = True
j=1
Do Until (j = Application.VBE.ActiveVBProject.References.Count) Or _
(Application.VBE.ActiveVBProject.References(j).Name = "SOLVER.xla")
j=j+1
Loop
If (Application.VBE.ActiveVBProject.References(j).Name <> "SOLVER.xla") Then
Application.VBE.ActiveVBProject.References.AddFromFile AddIns(i).FullName
End If
Else
MsgBox Prompt:="Solver not found this workbook will not WORK", Buttons:=vbCritical
End If
End Sub
Private Sub Workbook_Open()
benny
End Sub
'8/5/2006 Thanks to Maja Sliwinski and Beni Czaczkes
Function getformula(r As Range) As String
Application.Volatile
If r.HasArray Then
getformula = "<-- " & " {" & r.FormulaArray & "}"
Else
getformula = "<-- " & " " & r.FormulaArray
End If
End Function
'8/5/2006 Thanks to Maja Sliwinski and Beni Czaczkes
Function ggetformula(r As Range) As String

Application.Volatile
If r.HasArray Then
ggetformula = " {" & r.FormulaArray & "}"
Else
ggetformula = r.FormulaArray
End If
End Function
Data: Câu 2: Báo cáo TC
CRM: https://s23.q4cdn.com/574569502/files/doc_financials/2020/ar/Salesforce-FY-2020-Annual-
Report.pdf?fbclid=IwAR0BNQhEt7v54wjR6M10CDGGSO4Eoa4rNnuLaKqamNaU384yEYq6YkvtarA
Trang 74: Balance Sheet, T75: Consolidated Statements of Operations: tìm về Tax rate
T109: mục 9 _Debt - Cost of Debt, T110: Interest Expense on Debt
ABBV: https://investors.abbvie.com/static-files/71f9318f-9a32-42ee-92ee-a34975edcd19
Trang 61: Balance Sheet, T59: Consolidated Statements of Earnings: tìm về Tax rate
T71: Interest Expense: số liệu interest expenses of ABBV, T79: Cost of Debt
MCD: https://corporate.mcdonalds.com/content/dam/gwscorp/nfl/investor-relations-content/annual-
reports/2019%20Annual%20Report.pdf?
fbclid=IwAR3t2C_UydF77WkAZZJiGh6N9oCgOgFpSdUrHrPaGbdoxW1zhtkeTwqVENk
Trang 40: Balance Sheet, T38: Consolidated Statement of Income: tìm về Tax rate, Interest expense
BMY: https://s21.q4cdn.com/104148044/files/doc_financials/annual_reports/2019/2019-BMS-
Annual-Report.pdf?fbclid=IwAR1NJhDnYi6KEwhbOzFIEpAgJo4TgeiOtEECKtp3fVHvBxa0z92I008ec7M
Trang 47: Balance Sheet, T46: Consolidated Statements of Earnings: tìm về Tax rate
T30: Số liệu về Interest Expense
TMO: https://s1.q4cdn.com/008680097/files/doc_financials/2019/ar/final/2019-Annual-Report.pdf?
fbclid=IwAR0SCOxS6DmVxZvcP3JXUAWlm04EvVdCdCGun3H3tkye0tehh2Il0VbAAqg
COST: https://investor.costco.com/index.php/static-files/05c62fe6-6c09-4e16-8d8b-5e456e5a0f7e?
fbclid=IwAR0FWQZvTfsRhOx8kC46K07vPR5b5yqTN2JvIf1CqUIgV6I2kyYyBRU70O8
T58: Mục Debt – Cost of Debt
AMGN: http://investors.amgen.com/static-files/d45cb739-9637-4e13-856c-e2fc29571032?
fbclid=IwAR36QCz2wTw4c9KgChvnh-onkfaNiYYkbUYpC5k2ACV-TqYh1qCqC_1QeuQ
T130: Financing arrangements – Cost of debt
MDT: https://docoh.com/filing/1613103/0001613103-20-000021/MDT-10K-2020FY
Trang 62 - Mục 8 Balance Sheet, T60: Consolidated Statement of Income: tìm về Tax rate, Interest
expense
T80: Số liệu về debt để tính Cost of debt

Vba - Option (Callvolatility + Impliedvarcovar)

Uploaded by

Copyright:

Available Formats

Vba - Option (Callvolatility + Impliedvarcovar)

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Vba - Option (Callvolatility + Impliedvarcovar)

Uploaded by

Copyright:

Available Formats

VBA_Option (CallVolatility + ImpliedVarCovar)

Function dOne(Stock, Exercise, Time, Interest, sigma)

Function dTwo(Stock, Exercise, Time, Interest, sigma)

dTwo = dOne(Stock, Exercise, Time, Interest, sigma) - sigma * Sqr(Time)

Function BSCall(Stock, Exercise, Time, Interest, sigma)

BSCall = Stock * Application.NormSDist(dOne(Stock, Exercise, Time, Interest, sigma)) - Exercise * Exp(-

Application.NormSDist(dTwo(Stock, Exercise, Time, Interest, sigma))

Function callVolatility(Stock, Exercise, Time, Interest, Target)

Do While (High - Low) > 0.0001

If BSCall(Stock, Exercise, Time, Interest, (High + Low) / 2) > Target Then

High = (High + Low) / 2

Else: Low = (High + Low) / 2

callVolatility = (High + Low) / 2

Function ImpliedVolVarCov(varcovarmatrix As Range, volatilities As Range, corr As Double) As Variant

Dim matrix() As Double

ReDim matrix(numcols - 1, numcols - 1)

If Abs(corr) >= 1 Then GoTo Out

matrix(i - 1, j - 1) = corr * volatilities(i) * volatilities(j)

If Abs(corr) >= 1 Then ImpliedVolVarCov = “ERR” Else ImpliedVolVarCov = matrix

SolverOk SetCell:="$C$28", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$17:$C$24"

Range("constant") = -0.15 + counter * 0.005

Range("Results").Cells(counter, 10) = ActiveSheet.Range("amgn")

Range("Results").Cells(counter, 11) = ActiveSheet.Range("mdt")

Private Sub benny()

Do Until (i = AddIns.Count) Or (AddIns(i).Name = "SOLVER.XLA")

If (AddIns(i).Name = "SOLVER.XLA") Then

If (Application.VBE.ActiveVBProject.References(j).Name <> "SOLVER.xla") Then

Private Sub Workbook_Open()

'8/5/2006 Thanks to Maja Sliwinski and Beni Czaczkes

Function getformula(r As Range) As String

getformula = "<-- " & " " & r.FormulaArray

'8/5/2006 Thanks to Maja Sliwinski and Beni Czaczkes

Function ggetformula(r As Range) As String

ggetformula = " {" & r.FormulaArray & "}"

T109: mục 9 _Debt - Cost of Debt, T110: Interest Expense on Debt

T30: Số liệu về Interest Expense

T58: Mục Debt – Cost of Debt

T130: Financing arrangements – Cost of debt

You might also like