Modeling Structured Finance Cash Flows with Microsoft Excel

(John Hannent) #1
Asset Cash Flow Generation 25

FIGURE 2.2 A representative line can be created from the individual loans by summing the
principal balances and calculating the weighted average rate and term.

FIGURE 2.3 The five loans presented here have diverse principal balances, rates, and terms.

By summing the balances and calculating the weighted average rate and term,
a single representative line could be made. This would create a single line of
information that provides a relatively good summary of the pool of loans as shown
in Figure 2.2.
This single line is much easier to create cash flows from then having to create
five separate cash flows for each loan in the pool. However, subjective analysis is
required when making representative lines because the more diverse a pool of assets
is, the more distorted the cash flows are when making a single representative line.
Imagine having the data tape shown in Figure 2.3.
In this example, the rates and terms between assets are very different, which
means that the aggregate interest and periodic cash flows are distinctly different than
Free download pdf