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

CHAPTER 1

Dates and Timing

The importance of dates and timing could not resonate more strongly than in the field of finance. From simple present value equations to more complex concepts such as yields and duration, time is an essential variable. Not surprisingly, dates and timing are also extremely important to cash flow modeling. Both dictate the core format of the model and permeate throughout many formulas and analyses.

For Excel-based modeling, dates and timing can be separated into their own individual categories. Dates are often in calendar format and widely used to initiate or terminate preplanned events such as rate step-ups, final maturity, and the like. Timing is typically represented in numerical format or a vector of numbers to control payment frequency and most analytics involving periodicity.

TIME PROGRESSION

Before jumping right to specific methods and examples, it is worth a few minutes to think about the structure of the transaction and whether to have time “progress” horizontally or vertically. The key to this decision is understanding Excel’s constraints within the context of the necessary payment frequency and overall length of the analysis. The designers of Excel limited the number of rows to 65,556 and columns to 256. If a transaction required modeling quarterly over a period of 25 years, it would not matter whether the payments move horizontally or vertically because only 100 (4 payments per year * 25 years = 100) columns or rows would be needed. ...