Asked by:
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
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 addins & workbooks)


Y,
re: codeI 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 
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