# Normslnv(Rnd())

• ### 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.
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