Building the Debt Amortization Schedule

A fully amortizing loan has constant total payments (principal + interest), with the interest portion declining and the principal portion increasing over time. The monthly payment is calculated using the PMT function: PMT(rate/12, term_months, -loan_amount). For our 20-unit example: PMT(6.5%/12, 360, -$1,600,000) = $10,114/month or approximately $121,370/year (we used $96,000 annual for simplicity in earlier lessons; the exact figure depends on compounding method). Each month, interest = remaining balance x monthly rate, principal = total payment - interest, and new balance = previous balance - principal. Build this as a monthly schedule even if your pro forma is annual—sum 12 months to get annual debt service and use the December balance as the year-end balance.

Many commercial loans include an initial interest-only (IO) period of 1-5 years, followed by amortization. During the IO period, the payment equals only the interest (balance x rate / 12), and the loan balance does not decrease. After the IO period, the remaining balance amortizes over the remaining term. To model this, add a conditional to your schedule: if the period is within the IO window, payment = interest only; otherwise, calculate the amortizing payment based on the remaining balance and remaining term. For variable-rate loans (SOFR + spread), include a rate assumption for each year and recalculate payments whenever the rate changes. This is critical for bridge loans where rate increases can significantly impact DSCR.

Refinance events occur when existing debt is replaced with new debt, typically to take advantage of lower rates, extract equity, or transition from bridge to permanent financing. In the model, a refinance creates three cash flow events: (1) new loan proceeds received, (2) payoff of existing loan balance, and (3) closing costs (origination fees, legal, title). The net refinance proceeds = new loan amount - existing loan payoff - closing costs. If positive, this represents a cash distribution to equity. After the refinance, the amortization schedule starts fresh with the new loan terms. Model the new loan with its own assumptions (rate, term, amortization, IO period) and update the debt service figures in the cash flow projection from the refinance date forward.