Modeling Structured Finance Cash Flows with Microsoft® Excel®: A Step-by-Step Guide

CHAPTER 3

Prepayments

In Chapter 2, a notional amortization schedule was created that provided a basis for cash flowing into a transaction. This cash flow assumes that every loan in the pool will make payments exactly as they are scheduled. If it were that simple many finance professionals would be out of a job. In reality, cash flow becomes irregular when obligors choose to pay more than the scheduled amount, a prepayment, or when they do not pay at all, a delinquency, which sometimes becomes a default.

This chapter focuses on prepayments, how they are tracked, how they are projected, and how they are used in a cash flow model. First, it should be clear exactly what is considered a prepayment. A prepayment can either be “complete,” where the outstanding balance is paid off in full, or “partial,” where only a portion of the outstanding balance is paid. For example, an individual mortgage obligor might have extra money one month and decide to pay $2,000 towards her mortgage when the scheduled payment is $1,800. Since the periodic principal and interest is calculated in the $1,800 due, the only use of the additional $200 is to reduce the outstanding balance of the loan. This would be considered a partial prepayment. The other type of prepayment is where the entire balance is paid off. This can be the result of refinancing, credit-related events, and, on occasion, calculated due to foreclosure.

The latter part of this chapter features Model Builder exercises; but some of these exercises ...