Transcript PowerPoint 프레젠테이션

Monte Carlo Simulation
European Option Price
Jan Röman
Group member:Shanwei Huang
An Gong
Mesut Bora Sezen
MMA 707 Analytical Finance I
1.Introduction
2.Simulation
3.Summary
MMA 707 Analytical Finance I
1
Introduction
History
研究 of Monte Carlo Method
Use
背景 and Main Fields of Monte Carlo
Monte Carlo in Financial Engineering
MMA 707 Analytical Finance I
2
'returns an array of n normally distributed variables
'using box muller transformation
Public Function NRandVars(N As Single) As Variant
ReDim randArr(1 To N) As Variant
Dim i, n2, counter As Single
n2 = Application.Floor(N / 2, 1)
Dim v1, v2, tmp, fac As Double
counter = 0
For i = 1 To n2
Do
v1 = 2 * Rnd - 1
v2 = 2 * Rnd - 1
tmp = v1 * v1 + v2 * v2
Loop Until tmp <= 1
fac = Sqr(-2 * Log(tmp) / tmp)
counter = counter + 1
randArr(counter) = v1 * fac
counter = counter + 1
randArr(counter) = v2 * fac
Next i
If (N > (n2 * 2)) Then
Do
v1 = 2 * Rnd - 1
v2 = 2 * Rnd - 1
tmp = v1 * v1 + v2 * v2
Loop Until tmp <= 1
fac = Sqr(-2 * Log(tmp) / tmp)
counter = counter + 1
randArr(counter) = v2 * fac
End If
NRandVars = randArr
End Function
Simulation by Excel/VBA
Modules
Public Function
'returns
mean ofGetMaximum(x
an array
As Variant) As Double
Public
Dim maxVal
Function
As GetMean(x
Double
As Variant) As Double
Dim i As
maxVal
= Single
-2 ^ 50
Dim i
For
tmpsum
= LBound(x,
As Double
1) To UBound(x, 1)
tmpsum
If x(i,
= 01) > maxVal Then
Dim NmaxVal
As Single
= x(i, 1)
N =End
UBound(x,
If
1) - LBound(x, 1) + 1
For ii= LBound(x, 1) To UBound(x, 1)
Next
GetMaximum
tmpsum = tmpsum
= maxVal
+ x(i, 1)
End
Next
Function
i
GetMean = tmpsum / N
End Function
MMA 707 Analytical Finance I
2
Simulation by Excel/VBA
Mathematical formulas
The
We divide
payoffthe
function
maturity
of the
T into
callseveral
and put
intervals,and
option is given
this by
is t
he formula to calculate the stock price .
CT  max{0,
S

K
}
2
T
( r  / 2) t  t 
Sti  Sti1 e
PT  max{0, K  ST }
MMA 707 Analytical Finance I
2
Simulation by Excel/VBA
Mathematical formulas
Then,we can extimate the premium of the european option
by using the following function.
 rt
f  e E [ fT ]
Q
For
i = 1 Sub
To nsimulations
'get the pay off by "for" cycle
MMA 707 Analytical Finance I
Private
CommandButton1_Click()
st nsim
= S0 As Single
Dim
For j = 1 To nsteps
Application.Range("D20").Value
= ""
randvar = randvec(counter)
Application.Range("D21").Value
= ""
st = st * Exp(drift + vsqrdt * randvar)
Application.Range("D24").Value
= ""
Sheet2.Cells(j, i).Value = st
S0counter
= Application.Range("D11").Value
'underlying price 1
= counter + 1
k Next
= Application.Range("D12").Value
'strike
j
st >= k Then probcounter = probcounter
+ 1
T If
= Application.Range("D15").Value
'maturity
callpayoffvec(i,
1) = Application.Max(st - k,
0)
sigma
= Application.Range("D13").Value
'volatility
1) = Application.Max(k -'risk
st, 0)free rate
rputpayoffvec(i,
= Application.Range("D14").Value
Next i
nsteps = Application.Range("D17").Value 'no of timesteps
MC_callprice = Exp(-r * T) * GetMean(callpayoffvec) 'get the option price
nsimulations
= Application.Range("D18").Value
' no of mc simulations
MC_putprice
= Exp(-r
* T) * GetMean(putpayoffvec)
Randomize
Application.Range("D20").Value
= MC_callprice
Application.Range("D21").Value = MC_putprice
Application.Range("D24").Value
= (probcounter / nsimulations)
dt = T / nsteps
Dim ch =Assigma
ChartObject
vsqrdt
* dt ^ 0.5 'plot chart
Dim PRange
Range ^ 2 / 2) * dt
drift
= (r -As sigma
2
Simulation by Excel/VBA
Main Program
ReDim
callpayoffvec(1 To nsimulations, 1 To 1)
Worksheets("sheet1").ChartObjects.Delete
ReDim putpayoffvec(1 To nsimulations, 1 To 1)
Dim
Single
Setcounter
PRange =As
Application.Range("Sheet2!A1").Resize(nsteps,
nsimulations)
counter
1
Set ch == Worksheets("sheet1").ChartObjects.Add(280,
120, 480, 250)
ch.Chart.ChartType
= xlLine
Dim
procounter As Single
ch.Chart.HasLegend
= False
probcounter
= 0
ch.Chart.ChartWizard
Source:=Worksheets("sheet2").Range(PRange.Address),
randvec
= NRandVars(nsteps
* nsimulations) 'get the random number _
CategoryTitle:="simulation step", ValueTitle:="Avista value"
End Sub
MMA 707 Analytical Finance I
2
Simulation by Excel/VBA
Application
MMA 707 Analytical Finance I
3
Summary
Stochastic vs Deterministic
Significance of Simulation
Disadvantage