# The Construction Lender: companion files

Julian R. Sterling

## What is here

- **Construction_Lender_1_Sizing_and_Sources.xlsx** (Chapters 4, 5 and 11; Workbook 1 of Appendix A): sheets Sizing, Sources and uses, Projection, then Checks. The budget and the sources and uses table, the loan sized at the in-house loan-to-cost ($39,940,000) and tested against the value upon completion, and the loop of fee and interest reserve ($2,353,000) run pass by pass on the Projection sheet.
- **Construction_Lender_2_Draw_and_Retainage.xlsx** (Chapters 6, 8, 9 and 10; Workbook 2): sheets Draw 11, Retainage, then Checks. Draw No. 11 from $2,890,902 requested to $2,488,902 approved, retainage over the whole job ($4,547,491 held at completion), equity first to the first loan advance, and the pro rata comparison.
- **Construction_Lender_3_Reserve_and_In_Balance.xlsx** (Chapters 11, 12 and 14; Workbook 3): sheets In-balance, Interest reserve, Re-projection, then Checks. The in-balance test of month 14 and the rebalancing deposit of $2,162,000, the interest reserve month by month, and the extension test at initial maturity.
- **Construction_Lender_4_Conversion_Test.xlsx** (Chapter 15; Workbook 4): sheets Conversion, Largest loan, then Checks. The conversion test (DSCR, debt yield, loan-to-value), the largest loan that passes and the paydown of $939,824.
- **Construction_Lender_Toolkit.docx**: the 7 check-lists of Appendix B, editable (Bank limits pre-screen; Sponsor and project; Budget review; Closing conditions; Monthly draw; In-balance test and rebalancing; Extension and conversion).

## How to use the workbooks

Blue type on a light blue background is an input, black type a formula, green type a link to another sheet. On every sheet the inputs come first, then the calculations, then the outputs. Change one input at a time, note the output, and set the input back before the next test: the Checks sheet of each workbook compares the live cells with the figures printed in the book and reads ALL CHECKS OK at the base case. Of the 10 exercises of Appendix A, nine move one input of a workbook; exercise 8 is worked by hand.

The model has two loops: the loan, the fee and the interest reserve depend on one another, and so do the rebalancing deposit and the interest re-projected after the overrun. The workbooks contain no circular reference: each loop is unrolled into a fixed number of passes, shown one under the other, and the last pass feeds the rounded result.

Two inputs to try first:

1. In Construction_Lender_3_Reserve_and_In_Balance.xlsx, on In-balance, set the delay to 4 months: the deposit barely moves, but occupancy at initial maturity falls to 26.5 percent and the extension condition is not met.
2. In Construction_Lender_4_Conversion_Test.xlsx, on Conversion, set the achieved rent to the pro forma rent of $2,450: all three tests pass and the paydown falls to $0.

The workbooks are separate files with no link between them: a figure from another workbook is entered as an input, and its label names the workbook it comes from.

## A word of caution

Merrivane Bank, N.A., Sabletree Landing Owner, LLC, Thistlecombe Development, LLC, the city of Rowanmere and every party in the case are fictional. Rates, values, rents, loss rates, policy limits and the other loan terms are teaching assumptions, not market terms, regulatory requirements or the policy of any real bank. Supervisory criteria and capital rules must be read in their current text and confirmed with the bank's credit, capital and accounting staff and counsel. Nothing here is legal, accounting or regulatory advice.
