CRE Loan Prepayment Penalty Calculator: Yield Maintenance, Defeasance and Step-Down Cost by Payoff Month (Excel)

Estimate what it costs to pay off a fixed-rate commercial mortgage early under yield maintenance (two methods), defeasance and step-down structures, for any payoff month, with a cost-by-month chart and Treasury-yield sensitivity.

, ,
, , , , ,

Refinancing or selling a property with a fixed-rate loan? The prepayment structure can cost more than the rate saving. This Excel calculator estimates the payoff cost under the three common structures and shows how it changes month by month.

What is inside

  • Inputs: loan amount, note rate, amortization (or full interest-only), IO period, term, payoff month, Treasury yield, YM spread and floor, defeasance Treasury yield and fees, a 10-year step-down schedule and open period.
  • Summary: balance at payoff, yield maintenance (PV-of-payments and interest-differential methods, with floor), defeasance premium plus fees, step-down penalty, cheapest structure, each as % of balance.
  • Sensitivity: yield maintenance at Treasury yields from -150 bp to +150 bp.
  • Timeline: cost of all three structures for payoff after months 1-120, with a chart.
  • Schedule: 360-month amortization with IO and balloon, discount factors and PV columns, fully formula-driven.

Conventions: monthly 30/360, payoff right after the scheduled payment, balloon at maturity. Methods are documented on the Start Here tab, including the limits: actual payoff quotes follow the loan documents and the servicer or defeasance consultant.

Built with AI assistance and checked against an independent calculation. Estimating tool only; not legal, tax or investment advice.

What the model is used for

Estimating and comparing the early-payoff cost of a fixed-rate commercial mortgage before a refinance or sale, at one payoff month and across the loan’s life.

Tabs and structure

  • Start Here: purpose, methods, conventions, limits.
  • Summary: side-by-side cost and sensitivity table.
  • Timeline: 120 payoff months and a line chart.
  • Inputs: blue cells on yellow, plus derived payments.
  • Schedule: monthly amortization that feeds every result.

Key inputs

23 input cells: six loan terms, four yield maintenance settings (including method A or B), two defeasance settings, ten step-down percentages and the open period.

Outputs

Balance at payoff, remaining months, loan year, open-period flag, both yield maintenance methods, total defeasance cost, step-down penalty, cheapest structure, total payoff including yield maintenance, and an input check.

How to use it

Enter the loan and payoff month, enter Treasury yields, fees and the step-down schedule, then read Summary and Timeline.

File and limitations

One .xlsx workbook, no macros. Sample figures are fictional. Built with AI assistance and checked for formula errors. Not investment, legal or tax advice.

You must log in to submit a review.