166 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL
flows for the assets and two tranches of debt, so three loops are necessary.
However, if there was an additional tranche of debt, then four loops would
be necessary; two more tranches of debt and five loops would be necessary,
and so on.
This can be overcome by using the range that was created for the PV
differences. Recall that the range rngYieldTarget was defined earlier as E6:G6
on the Analytics sheet. This range should always encompass all streams of cash
flow that are to be analyzed. If an additional tranche of debt were in column
H, then this range should be extended to column H. Since the range will always
contain a number of cells equal to the number of cash flow streams to be
optimized, a count method can be integrated into the For statement. Enter the
following below the previous line of code:
For i = 1 To Range(Target).Cells.Count
The code begins like a standard For statement with For i = 1 To, but, instead of
providing a numerical value, the reference Range(Target).Cells.Count is used.
The range ‘‘rngYieldTarget’’ is being referenced here using the Target variable.
Cells is referring to the individual cells within the range and the Count method
counts those cells. Since there are threecells in the range, the For statement is
effectively For i = 1 to 3.
6.The next few lines of code are mostly familiar, with a few new concepts. Enter
this code below the previous line:
Set TargetRange = Range(Target).Cells(1, i)
Set YieldRange = Range(YieldChange).Cells(1, i)
TargetRange.GoalSeek Goal: = 0, ChangingCell: = YieldRange
Next i
Notice the addition of the Cells property that comes after Range(Target) and
Range(YieldChange). Earlier Cells was used to count the number of cells; but
it can also be used to reference individual cells within a range. By entering
an open parenthesis right after the Cells property, VBA is instructed to look
for a reference within the cells of the respective range. VBA follows an RC
(rows, columns) convention, so a (1, 1) would be the first row and first
column. In the above code the reference is (1, i), which means row 1 and
column i. The variable i will take on a numerical variable depending on
the loop iteration. This numerical value will also correspond to the column
order in the ranges. Specifically during the first loop i will equal 1, making
the reference (1, 1). For each range the first row and first column will be
referenced. During the next iteration iwill equal 2 starting at the beginning
of the For statement, making the reference (1, 2), which will refer to the
first row and second column. The entire process will carry on until i is equal
to the parameter set in the For statement, which is three (the total range
count). This is the crux of how looping works in ranges. Finally, add a
button labeledCalculate Analyticsnear D7 on the Inputs sheet and assign the
macro.
