
| Financial Model, Loans & Lending Businesses, Real Estate |
| Calculator, Excel, Loans, Mortgage, Refinancing Scenario, Sensitivity Analysis |
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.
With this comprehensive 5- or 10-year monthly tool, investors can assess the viability of setting up... Read more
The Mobile App Financial Plan Template in Excel allows you to develop financial projections when lau... Read more
The Food Truck Financial Model helps entrepreneurs, founders, business owners, consultants, and anal... Read more
The Green Hydrogen from Wind Financial Model aims to comprehensively forecast a horizon of 40 years ... Read more
Protect your business secrets with ease using our Simple Mutual Non-Disclosure Agreement Template. S... Read more
The Solar Energy Financial Model Spreadsheet Template in Excel assists you in preparing a sophistica... Read more
Starting a restaurant without a financial plan is like driving a car blindfolded. You wouldn´t do i... Read more
You must log in to submit a review.