104 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL
BV4:Unpaid
BW4:Cash Remaining
A similar debt principal calculation needs to be done for the subordinated debt;
but there is a major difference. In a sequential principal allocation system, the
subordinated tranche should not receiveany principal until the senior tranche
is completely paid off. Modeling this logic is achieved through an IF-AND
combination in cell BT7:
=IF(AND(LiabPrinType2="Sequential",CB6>0),0,
The beginning of the formula for BT7 will return a 0 value if the principal
allocation system is set to ‘‘Sequential’’ and the senior debt has a principal
balance. If the senior debt is paid off, then another IF statement is required
because a FALSE value for the first IF statement can be caused by either
a different principal allocation type or a paid off senior tranche. Add the
following shown in bold to cell BT7:
=IF(AND(LiabPrinType2="Sequential",CB6>0),0,
IF(LiabPrinType2="Sequential",MIN((N7+Q7+R7),CF6),
FIGURE 6.7 The Senior Principal section of the
Cash Flow sheet.
