Corporate and Project Finance Modeling

Chapter 42Automating the Goal Seek Process for Annuity and Equal Installment Repayments

Some project finance loans are structured with repayments that are not sculpted to cash flow, but rather are repaid in either equal installments or with annuity repayments. These styles of repayment are illustrated in Figures 42.1 and 42.2. Figure 42.1 shows a project financing transaction with equal installment payments resulting in total debt service that declines over the loan life as the interest expense is reduced from a declining debt balance. Figure 42.2 demonstrates an annuity repayment structure where the debt service is the same over the loan life. This annuity repayment structure implies that the declines in interest expense are offset by increased repayments over the loan life. If the debt commitment is an input to the model and repayments are computed on the basis of either equal installment payments or annuity payments, then the amount of the total debt capacity can be computed using the Goal Seek or the Solver to conform to the debt service coverage ratio (DSCR) constraint. Applying the Goal Seek or the Solver to resolve circular references creates many of the same problems as applying the copy and paste macros. The Goal Seek must be rerun every time you want to put in any new terms for a pricing contract or any other aspect of the transaction structure. A more elegant approach is to make the model flexible and more transparent is by writing a user-defined function to replicate ...