Monte Carlo Simulation European Option Price

1. Monte Carlo SimulationEuropean Option Price Jan Röman Group member:Shanwei Huang An Gong Mesut Bora Sezen

2. MMA 707 Analytical Finance I 1.Introduction 2.Simulation 3.Summary

3. MMA 707 Analytical Finance I Introduction 1 • History of Monte Carlo Method • Use and Main Fields of Monte Carlo • Monte Carlo in Financial Engineering 研究 背景

4. MMA 707 Analytical Finance I Simulation by Excel/VBA 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 Modules 'returns mean of an array Public Function GetMean(x As Variant) As Double Dim i As Single Dim tmpsum As Double tmpsum = 0 Dim N As Single N = UBound(x, 1) - LBound(x, 1) + 1 For i = LBound(x, 1) To UBound(x, 1) tmpsum = tmpsum + x(i, 1) Next i GetMean = tmpsum / N End Function Public Function GetMaximum(x As Variant) As Double Dim maxVal As Double maxVal = -2 ^ 50 For i = LBound(x, 1) To UBound(x, 1) If x(i, 1) > maxVal Then maxVal = x(i, 1) End If Next i GetMaximum = maxVal End Function

5. MMA 707 Analytical Finance I Simulation by Excel/VBA 2 Mathematical formulas We divide the maturity T into several intervals,and this is the formula to calculate the stock price . The payoff function of the call and put option is given by

6. MMA 707 Analytical Finance I Simulation by Excel/VBA 2 Mathematical formulas Then,we can extimate the premium of the european option by using the following function.

7. Private Sub CommandButton1_Click() Dim nsim As Single Application.Range("D20").Value = "" Application.Range("D21").Value = "" Application.Range("D24").Value = "" S0 = Application.Range("D11").Value 'underlying price 1 k = Application.Range("D12").Value 'strike T = Application.Range("D15").Value 'maturity sigma = Application.Range("D13").Value 'volatility r = Application.Range("D14").Value 'risk free rate nsteps = Application.Range("D17").Value 'no of timesteps nsimulations = Application.Range("D18").Value ' no of mc simulations Randomize dt = T / nsteps vsqrdt = sigma * dt ^ 0.5 drift = (r - sigma ^ 2 / 2) * dt ReDim callpayoffvec(1 To nsimulations, 1 To 1) ReDim putpayoffvec(1 To nsimulations, 1 To 1) Dim counter As Single counter = 1 Dim procounter As Single probcounter = 0 randvec = NRandVars(nsteps * nsimulations) 'get the random number For i = 1 To nsimulations 'get the pay off by "for" cycle st = S0 For j = 1 To nsteps randvar = randvec(counter) st = st * Exp(drift + vsqrdt * randvar) Sheet2.Cells(j, i).Value = st counter = counter + 1 Next j If st >= k Then probcounter = probcounter + 1 callpayoffvec(i, 1) = Application.Max(st - k, 0) putpayoffvec(i, 1) = Application.Max(k - st, 0) Next i MC_callprice = Exp(-r * T) * GetMean(callpayoffvec) 'get the option price MC_putprice = Exp(-r * T) * GetMean(putpayoffvec) Application.Range("D20").Value = MC_callprice Application.Range("D21").Value = MC_putprice Application.Range("D24").Value = (probcounter / nsimulations) Dim ch As ChartObject 'plot chart Dim PRange As Range Worksheets("sheet1").ChartObjects.Delete Set PRange = Application.Range("Sheet2!A1").Resize(nsteps, nsimulations) Set ch = Worksheets("sheet1").ChartObjects.Add(280, 120, 480, 250) ch.Chart.ChartType = xlLine ch.Chart.HasLegend = False ch.Chart.ChartWizard Source:=Worksheets("sheet2").Range(PRange.Address), _ CategoryTitle:="simulation step", ValueTitle:="Avista value" End Sub MMA 707 Analytical Finance I Simulation by Excel/VBA 2 Main Program

8. MMA 707 Analytical Finance I Simulation by Excel/VBA 2 Application

9. MMA 707 Analytical Finance I Summary 3 • Stochastic vs Deterministic • Significance of Simulation • Disadvantage

10. Thank You!