116 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL
FIGURE 7.6 Make sure the Vector’s sheet is updated so Cash Flow sheet
calculations work.
8.The calculation for the swap flow out is identical to the swap flow in, with the
exception of the referenced rate. Enter the following formula into cell AI7:
=AE7*AH7*C7
Copy and paste this formula over the range AI7:AI366.
9.To determine the net amount paid or earned from the swap, subtract the swap
flow out from the swap flow in. This is done with the following formula in
cell AJ7:
=AG7−AI7
Notice there is no MIN here because the value can be negative depending
on the interest rate assumptions. Copy and paste this formula over the range
AJ7:AJ366.
10.The swap section is completed by tracking the cash available after giving affect
to swap payments. Enter the following formula in cell AK7:
=X7+AJ7
Copy and paste this formula over the range AK7:AK366. By now the swap
section should look like Figure 7.7.
11.With the introduction of this advanced structure, a minor modification needs to
be made to an existing formula so cash continues to flow through the waterfall.
Change the formula in cell AP7 to:
=AK7−AN7
