96 MODELING STRUCTURED FINANCE CASH FLOWS WITH MICROSOFT EXCEL
2.The Liability Interest Type for a given debt tranche can be one of three types:
■Floating, when an index is used.
■Fixed, either a fixed bond or swap rate.
■Custom, a rate that changes over time based on the user or a third party.
Custom is also used because rating agencies often derive and make available
their own stressed interest rate curves.
In the chapter on asset amortization, the range named lstIntType was already
created for these three types of interest. Go to the Inputs sheet and create a
data validation list in cell D24 using lstIntType as the range. Name cell D24
LiabIntType1. Repeat this in cell D25, but name that cellLiabIntType2.
3.Cells E24 and E25 need to produce the name of the curve that is being used if the
debt is floating rate. This will be done exactly as was done for the interest rates
on the asset side. Create a data validation list in cell E24 using lstInterestRates
as the range. Name cell E24LiabLoanIndex1. Repeat this in cell E25, but name
that cellLiabLoanIndex2.
4.The fixed rates will be stored in cells F24 and F25 for each tranche. Name cell F24
LiabFxdRate1and F25LiabFxdRate2. Keep both of these cells empty for now.
5.The loan margin is the final input that is needed for this section. Name cell
G24LiabMarg1and G25LiabMarg2.Enter1.00%as a value for cell G24
and0.00%for cell G25. The Inputs sheet should now look like the screen in
Figure 6.4.
6.The next step is to switch over to the Cash Flow sheet and calculate the debt
interest. A standard structured transaction will pay the most senior debt interest
first. In Project Model Builder columns AR:AX will be used for the Senior Debt
Interest. Column AQ will be left blank as a separator.
The first piece of information needed is the correct annual interest rate for the
period. Enter the labelNote Interest Ratein cell AR4. While step 2 created the
possibility of three types of interest rates, there are actually only two options for
storing the rates: on the Inputs sheet or on the Vectors sheet. This is important
to know because a formula needs to know when and where to look for a specific
data point. The simplest situation for rates is when a fixed rate is used, which is
stored on the Inputs sheet. To account for that possibility, begin the formula in
FIGURE 6.4 The liability interest rate section complete on the Inputs sheet.
