VBA button for Monte Carlo portfolio simulation

[Vluggejapie]

Dear all,

I was hoping if you could help regarding a model I am creating to simulate different portfolio compositions for a financial fund.
I have build a model that generations an x amount of loans with variables which are randomly generated (Type, intererst rate, duration etc.).
The averages of the portfolio are calculated in my first tab Range("B5:B11").
My intention is to create a button that creates a loop to generate a portfolio, copy the results to another sheet and do this x amount of times (G2).
The idea is that you can change the parameters and get an idea what an average return might be after x rolls.

So far, I have the following syntax:

Sub Calculation_loop()
Dim Iteration As Integer, i As Integer
Iteration = Range("G2")
Application.ScreenUpdating = False
For i = 0 To Iteration
Application.Calculate
Range("B5:B11").Copy
Sheets("Simulation").Range("B1" & i).PasteSpecial Transpose:=True
Next i
Application.CutCopyMode = False 'Clear Clipboard
Application.ScreenUpdating = True
End Sub

My problem is that the syntax above only pastes the last roll in its corresponding cell but that the previous cells show 0 / errors.
What am I missing here?

Thanks in advance!

 

[Fluff]

Cross posted VBA button for (monte carlo) portfolio generation.

Cross-Posting
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Vluggejapie]

Solved through:

Sub Calculation_loop()
    
    Dim Iteration As Integer, i As Integer
    Dim sRow As Long
    Dim lRow As Long
    Iteration = Sheets("Key assumptions").Range("G2")
    lRow = Worksheets("Simulation").Cells(Worksheets("Simulation").Rows.Count, 2).End(xlUp).Row
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For i = 1 To Iteration
    Application.Calculate
    Sheets("Key assumptions").Range("B5:B11").Copy
    Sheets("Simulation").Range("B" & lRow + i).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Application.CutCopyMode = False
    Next
    Application.ScreenUpdating = True
End Sub