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 * [Link](dOne(Stock, Exercise, Time, Interest, sigma)) - Exercise * Exp(-
Time * Interest) * _
[Link](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 = [Link]
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
[Link] ("{Enter}")
Range("Results").Cells(counter, 1) = [Link]("constant")
Range("Results").Cells(counter, 2) = [Link]("portfolio_sigma")
Range("Results").Cells(counter, 3) = [Link]("portfolio_mean")
Range("Results").Cells(counter, 4) = [Link]("crm")
Range("Results").Cells(counter, 5) = [Link]("abbv")
Range("Results").Cells(counter, 6) = [Link]("mcd")
Range("Results").Cells(counter, 7) = [Link]("bmy")
Range("Results").Cells(counter, 8) = [Link]("tmo")
Range("Results").Cells(counter, 9) = [Link]("cost")
Range("Results").Cells(counter, 10) = [Link]("amgn")
Range("Results").Cells(counter, 11) = [Link]("mdt")
Next counter
End Sub
Private Sub benny()
i=1
Do Until (i = [Link]) Or (AddIns(i).Name = "[Link]")
i=i+1
Loop
If (AddIns(i).Name = "[Link]") Then
AddIns(i).Installed = True
j=1
Do Until (j = [Link]) Or _
([Link](j).Name = "[Link]")
j=j+1
Loop
If ([Link](j).Name <> "[Link]") Then
[Link] 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
[Link]
If [Link] Then
getformula = "<-- " & " {" & [Link] & "}"
Else
getformula = "<-- " & " " & [Link]
End If
End Function
'8/5/2006 Thanks to Maja Sliwinski and Beni Czaczkes
Function ggetformula(r As Range) As String
[Link]
If [Link] Then
ggetformula = " {" & [Link] & "}"
Else
ggetformula = [Link]
End If
End Function
Data: Câu 2: Báo cáo TC
CRM: [Link]
[Link]?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: [Link]
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: [Link]
reports/2019%20Annual%[Link]?
fbclid=IwAR3t2C_UydF77WkAZZJiGh6N9oCgOgFpSdUrHrPaGbdoxW1zhtkeTwqVENk
Trang 40: Balance Sheet, T38: Consolidated Statement of Income: tìm về Tax rate, Interest expense
BMY: [Link]
[Link]?fbclid=IwAR1NJhDnYi6KEwhbOzFIEpAgJo4TgeiOtEECKtp3fVHvBxa0z92I008ec7M
Trang 47: Balance Sheet, T46: Consolidated Statements of Earnings: tìm về Tax rate
T30: Số liệu về Interest Expense
TMO: [Link]
fbclid=IwAR0SCOxS6DmVxZvcP3JXUAWlm04EvVdCdCGun3H3tkye0tehh2Il0VbAAqg
Trang 58: Balance Sheet, T59: Consolidated Statement of Income: tìm về Tax rate, Interest expense
COST: [Link]
fbclid=IwAR0FWQZvTfsRhOx8kC46K07vPR5b5yqTN2JvIf1CqUIgV6I2kyYyBRU70O8
Trang 42: Balance Sheet, T43: Consolidated Statement of Income: tìm về Tax rate, Interest expense
T58: Mục Debt – Cost of Debt
AMGN: [Link]
fbclid=IwAR36QCz2wTw4c9KgChvnh-onkfaNiYYkbUYpC5k2ACV-TqYh1qCqC_1QeuQ
Trang 103: Balance Sheet, T101: Consolidated Statement of Income: tìm về Tax rate, Interest expense
T130: Financing arrangements – Cost of debt
MDT: [Link]
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