none
Normslnv(Rnd()) RRS feed

  • Question

  • Monte carlo simulation

    Phi1 and phi2 are going to generate random standard distributed probabilities and when j increases to over 3000, only phi2 stops working and says it says running error 1004, i do not know what is going on.

    Sub Mc()

    Dim S1(0 To 100000) As Double

    Dim S2(0 To 100000) As Double

    Dim N1(0 To 10000) As Double

    Dim N2(0 To 10000) As Double

    Dim sum(0 To 10000) As Double

    Dim sum1(0 To 10000) As Double

    Dim sum2(0 To 10000) As Double

    Dim N, T, i, j, K As Integer

    Dim Sigma1, Sigma2, div1, div2, r, Delta, pho, S10, S20, S1B, S2B, P, N3, N4, V As Double

    K = 1000

    N = 365 * 3

    Sigma1 = 0.2

    Sigma2 = 0.2236

    div1 = 0.02103

    div2 = 0.0132

    r = 0.021816

    T = 3

    Delta = T / N

    pho = 0.5

    S10 = 2378.25

    S20 = 1391.524

    S1B = 1902.6

    S2B = 1113.219

    For j = 1 To K Step 1

    For i = 1 To N Step 1

    sum1(0) = 0

    sum2(0) = 0

    phi1 = Application.WorksheetFunction.NormSInv(Rnd())

    phi2 = Application.WorksheetFunction.NormSInv(Rnd())

    S1(0) = S10

    S2(0) = S20

    S1(i) = S1(i - 1) * Exp((r - div1 - 0.5 * (Sigma1) ^ 2) * Delta + Sigma1 * phi1 * Delta ^ 0.5)

    S2(i) = S2(i - 1) * Exp((r - div2 - 0.5 * (Sigma2) ^ 2) * Delta + Sigma2 * pho * phi1 * Delta ^ 0.5 + Sigma2 * (1 - (pho) ^ 2) ^ 0.5 * (Delta) ^ 0.5 * phi2)

    sum1(i) = sum1(i - 1) + S1(i)

    sum2(i) = sum2(i - 1) + S2(i)

    N3 = (sum1(N) - sum1(N - 180)) / 180

    N4 = (sum2(N) - sum2(N - 180)) / 180

    Next i

    sum(0) = 0

    If (N3 >= S1B) And (N4 >= S2B) Then

    V = Exp(-r * T) * 11.79

    End If

    If (N3 < S1B) Or (N4 < S2B) Then

    V = Exp(-r * T) * (10 + 10 * (WorksheetFunction.Min(((N3 - S1B) / S1B), ((N4 - S2B) / S2B)) + 0.2))

    End If

    sum(j) = sum(j - 1) + V

    Next j

    P = sum(K) / K

    Range("MC!A7") = P

    End Sub

    Friday, December 22, 2017 2:10 AM

All replies

  • YushengCao,
    re: code

    Try changing...
      Range("MC!A7") = P
    To...
      Range("A7") = P

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Friday, December 22, 2017 1:55 PM
  • No,It still doesn't work````
    Friday, December 22, 2017 8:27 PM
  • Y,
    re: code

    I declared phi1 and phi2 and revised the range callout and the code ran for me in Excel 2010.
    In cell A7 it returned:   10.56789  (or some such).

    If you want someone else here to possibly help, you need to explain exactly what "it still doesn't work" means.

    '---
    Jim Cone

    Friday, December 22, 2017 10:29 PM
  • YushengCao,

    In addition, the Rnd function can return a zero value.
    The NormSInv function fails if provided a zero.
    So...
    Suggest you replace your phi1 and phi2 code lines with...
    '---
    Do
     On Error Resume Next
     phi1 = Application.WorksheetFunction.NormSInv(Rnd())
    Loop Until Err.Number < 1

    Do
     On Error Resume Next
     phi2 = Application.WorksheetFunction.NormSInv(Rnd())
    Loop Until Err.Number < 1
    On Error GoTo 0
    '---

    Also, the Rnd function in Excel VBA is not always exactly random.
    Suggest you add the expression...
       Randomize
    just before the line... For j = 1 To K Step 1
    so the code looks like...

    Randomize
    For j = 1 To K Step 1

    '---
    Jim Cone
    Free Excel 'Special Randoms' Excel workbook
    Download from...  https://goo.gl/IUQUN2   (Dropbox)



    • Edited by James Cone Saturday, December 23, 2017 1:06 AM
    Friday, December 22, 2017 11:56 PM