My VBA is taking 4 days to run. Need help optmizing it.

[dsbrin]

Hello all. Here I am again in the need of your help.

I have a VBA that I use to set values in my excel model. Each of those values are linked to a cash-flow schedule that I use to calculate IRR (using XIRR). The same VBA that set the values of the cash-flows also writes the resulting IRR in one of the worksheets in my model.
There are 15 cash-flows, and for each of them, I have 3 possible values (let's say 3 cases).

I need to calculate the resulting IRR for all the possible combination of cash-flows and scenarios. (3^15 = 14,348,907 possible arranges).
The way I managed to do it was build 15 For loops, one inside each other. It worked, but it took me 4 days to run it.

I would like some advice on how to improve it and make it run faster - and possibly a way to make the number of for loops dynamic, or leaner.

Any help is welcome.

Here is my code:


Sub MC()
Dim Q_Val As Range
Dim VA, VB, VC, VD, VE, VF, VG, VH, VI, VJ, VK, VL, VM, VN, VO, r, n, Total As Long
Dim ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn, co, m As Integer
Dim pctCompl, IRR As Single
Set Q_Val = Range("N_Val") ' number of valuations (counter for the for loop)
ca = 1
cb = 1
cc = 1
cd = 1
ce = 1
cf = 1
cg = 1
ch = 1
ci = 1
cj = 1
ck = 1
cl = 1
cm = 1
cn = 1
co = 1

r = 1 'row to write
n = 1 'total loop count
m = 1 'collumn to write
Application.ScreenUpdating = False

Total = Worksheets("Summary").Cells(5, 3).Value
Worksheets("Results").Activate
Cells.Select
Selection.delete Shift:=xlUp 'clears up whatever values are writen in the Results worksheet
For ca = 1 To Q_Val 'Asset 1
VA = Worksheets("Cases").Cells(ca, 1).Value
For cb = 1 To Q_Val 'Asset 2
VB = Worksheets("Cases").Cells(cb, 2).Value
For cc = 1 To Q_Val 'Asset 3
VC = Worksheets("Cases").Cells(cc, 3).Value
For cd = 1 To Q_Val 'Asset 4
VD = Worksheets("Cases").Cells(cd, 4).Value
For ce = 1 To Q_Val 'Asset 5
VE = Worksheets("Cases").Cells(ce, 5).Value
For cf = 1 To Q_Val 'Asset 6
VF = Worksheets("Cases").Cells(cf, 6).Value
For cg = 1 To Q_Val 'Asset 7
VG = Worksheets("Cases").Cells(cg, 7).Value
For ch = 1 To Q_Val 'Asset 8
VH = Worksheets("Cases").Cells(ch, 8).Value
For ci = 1 To Q_Val 'Asset 9
VI = Worksheets("Cases").Cells(ci, 9).Value
For cj = 1 To Q_Val 'Asset 10
VJ = Worksheets("Cases").Cells(cj, 10).Value
For ck = 1 To Q_Val 'Asset 11
VK = Worksheets("Cases").Cells(ck, 11).Value
For cl = 1 To Q_Val 'Asset 12
VL = Worksheets("Cases").Cells(cl, 12).Value
For cm = 1 To Q_Val 'Asset 13
VM = Worksheets("Cases").Cells(cm, 13).Value
For cn = 1 To Q_Val 'Asset 14
VN = Worksheets("Cases").Cells(cn, 14).Value
For co = 1 To Q_Val 'Asset 15
VO = Worksheets("Cases").Cells(co, 15).Value
Worksheets("Transfer").Cells(1, 2).Value = VA
Worksheets("Transfer").Cells(2, 2).Value = VB
Worksheets("Transfer").Cells(3, 2).Value = VC
Worksheets("Transfer").Cells(4, 2).Value = VD
Worksheets("Transfer").Cells(5, 2).Value = VE
Worksheets("Transfer").Cells(6, 2).Value = VF
Worksheets("Transfer").Cells(7, 2).Value = VG
Worksheets("Transfer").Cells(8, 2).Value = VH
Worksheets("Transfer").Cells(9, 2).Value = VI
Worksheets("Transfer").Cells(10, 2).Value = VJ
Worksheets("Transfer").Cells(11, 2).Value = VK
Worksheets("Transfer").Cells(12, 2).Value = VL
Worksheets("Transfer").Cells(13, 2).Value = VM
Worksheets("Transfer").Cells(14, 2).Value = VN
Worksheets("Transfer").Cells(15, 2).Value = VO
IRR = Round(Worksheets("Transfer").Cells(17, 2).Value, 4)
If r > 1048576 Then
r = r - 1048576
m = m + 1
'1048576 rows tops
End If
Worksheets("Results").Cells(r, m).Value = IRR
progress pctCompl
n = n + 1
r = r + 1
Next co
Next cn
Next cm
Next cl
Next ck
Next cj
Next ci
Next ch
Next cg
Next cf
Next ce
Next cd
Next cc
Next cb
Next ca
Application.ScreenUpdating = True

End Sub

 

[Eric W]

Wow! 4 days? My question is, what are you going to do with all those numbers? You can't possibly plan on eyeballing 14M+ numbers? Are you looking to find the range? Best vs. Worst? If so, you don't save the parameters that go into that function. And even so, a little numerical analysis can achieve your answer (to a large degree) in a much shorter time.

Here is some updated code, just for illustration. It will perform the same calculation in a much shorter time, but you still have to come up with a rationale for doing all the calculations.

Sub temp1()
Dim MyIndex() As Long, i As Long, NumIndexes As Long, MaxSize As Long, MyValues As Variant
Dim MyVals() As Long, IRR As Double

    NumIndexes = 4
    MaxSize = 3
    
    ReDim MyIndex(1 To NumIndexes)
    ReDim MyVals(1 To NumIndexes)
    
    For i = 1 To NumIndexes
        MyIndex(i) = 1
    Next i
    
    MyValues = Worksheets("Cases").Range("A1:O3").Value
    
MyLoop:
    

' Calculate IRR.
    For i = 1 To NumIndexes
        MyVals(i) = MyValues(MyIndex(i), i)
    Next i
    IRR = WorksheetFunction.IRR(MyVals)

' Now we increment the indices
    For i = NumIndexes To 1 Step -1
        MyIndex(i) = MyIndex(i) + 1
        If MyIndex(i) <= MaxSize Then Exit For
        MyIndex(i) = 1
    Next i
    If i > 0 Then GoTo MyLoop:

End Sub

Several improvements. First, it's shorter. Next, you can have a variable number of parameters. Change NumIndexes to whatever you want, 15 in your case, and MaxSize to what you want, 3 in your case. Also change the MyValues = line so that it reads the range on your sheet. Which is the next big improvement. One of the slowest things you can do in VBA is to read/write to the worksheet. And you do it about 230,000,000 times! No wonder it took 4 days! Ideally, read the data you want to use at the start of your macro, and write out the results at the end. Here I show you how to read all your parameters in one shot, and how to calculate the IRR without resorting to the sheet calculating. I don't show how to save the results, because I still don't understand what you want to do with all those numbers. You could create an array, 1048576 X 219, to store the values and write it all out in one shot, or write them 1 at a time like before (which will be slow). But it would make more sense to figure out what you need from those numbers without writing them all out.

And frankly, I'm sure you could get what you need without going through all 3^15 combinations. Let me know if this helps, or if you have followup questions.

 

[Beyond_avarice]

Man that is one "loopy" script (Badabah...ting).

Doing the following actually declares all but your last variable as a "Variant":

Dim VA, VB, VC, VD, VE, VF, VG, VH, VI, VJ, VK, VL, VM, VN, VO, r, n, Total As Long
    Dim ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn, co, m As Integer

I've always understood that only the last variable will be declared as the "data type" presented and so you should declare all of them eg:

Dim VA as Long, VB as Long, VC as Long, etc etc etc as long
    Dim ca as integer, cb as integer, cc as integer, etc etc etc as integer

The Data type of "Variant" can be over 16 bytes each while "Long" or "Integer" are 4 bytes and 2 bytes, respectively.

You don't need to set your "Integer" variables as = 1, because you do that in your loop with XX = 1 To Q_Val 'Asset (#)

For ca = 1 To Q_Val 'Asset 1
        VA = Worksheets("Cases").Cells(ca, 1).Value
        For cb = 1 To Q_Val 'Asset 2
            VB = Worksheets("Cases").Cells(cb, 2).Value
            For cc = 1 To Q_Val 'Asset 3
                VC = Worksheets("Cases").Cells(cc, 3).Value
                For cd = 1 To Q_Val 'Asset 4
                    VD = Worksheets("Cases").Cells(cd, 4).Value

So is VC dependent on VB and on VA and every consecutive variable down? I see that you eventually carry these variables to the "Transfer" sheet, in row 2 column 1 to 15.

I've got a feeling you don't need this many loops.

I'm presuming " Q_Val" are your 15 values.

 

[dsbrin]

Eric_W, thanks for you response. It was definitely helpful, though I still didn't fully understand it. Same for Beyond_Avarice - I took note and will use your tips from now on.

I will give a little bit more flavor to what I am doing and why I want to calculate the 3^15 combinations:

We are measuring the IRR for this Fund, which contains 15 assets in its portfolio. Each of those assets pay dividends (part 1 of cash flows) and have a unrealized NAV (valuations, part 2 of the cash flows). I'm trying to assess the distribution of all the possible IRRs in this portfolio, given the unrealized NAV (since it is unrealized, it can still vary - we used -25% and +25% as a range).

In my XIRR formula I have all the dividends (which are static) and the NAVs (which I'm varying with all those endless for loops). Here is my cash flow schedule: http://i.imgur.com/hAleVBm.png

The values with blue labels are the values I am setting in my VBA (D25, for instance, refers to Transfer!B1) The VBA writes the values in the Transfer worksheet at each iteration of the loop. http://i.imgur.com/YR6qvXu.png . In this sense, the VA, VB, VC... that you saw in the VBA code refers to Valuation of Asset A, Valuation of Asset B, etc.

Q_Val is the number of cases for each asset (in here we are using 3: downside, base and upside)


Thanks!

 

[shg]

You could instead do a Monte Carlo analysis, letting the cash flows (or whatever) vary randomly according to some distribution, and calculate the IRR for each. I'd expect that would give a good estimate for a small fraction of the effort to calculate them all exhaustively.