A free mortgage note pricing model in Excel: one non-performing loan, then a tape
Total debt, the foreclosure floor on judicial and non-judicial timelines, reinstatement, modification and deed-in-lieu paths, and an eight-loan tape priced loan by loan against the seller's ask: six sheets, no macros.
The file prices Harlow Street, a fictional first lien 19 months delinquent: $165,600 of unpaid principal, $190,100 of total debt, on a house worth $212,000. On the judicial foreclosure path, with the sale at month 30, the most a buyer can pay and still earn 20% is $69,750, or 42% of principal. Non-judicial, the same loan supports $114,800. Then it prices the Calloway tape, eight loans offered at $435,240. The floors add up to $413,483; bought at the ask the pool returns 17.7%, and the book's bid of $333,499, dropping the loans with a negative floor, returns 30.2%.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Mortgage_Note_Pricing_Model.xlsx (263 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from The Mortgage Note Investor. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Six sheets. Blue cells on Inputs are typed assumptions; everything else is a formula.
Inputs
The loan, taxes, insurance, costs and timeline for Harlow Street, the required IRR, and the terms of every path and of the tape.
Check
Every figure the book prints against the live formula: 26 lines, all OK.
Harlow
Payment, principal, advances and accrued interest to total debt, then the floor as a share of principal, value and total debt.
Engine
The foreclosure path, one column per scenario: sale month, legal cost, possession, REO sale, total debt at the sale and the NPV at the required IRR.
Paths
Reinstatement, four modification designs and the modification at the budget rate, each resold to a yield, with the maximum bid and the IRR at the floor bid.
Tape
Eight loans side by side: lien, judicial or not, principal, rate, delinquency, value, occupancy, timeline, the floor on each route, the ask, and the pool priced at the ask and at the bid.
What the worked case returns
Measure
Value
Unpaid principal balance
$165,600
Total debt at purchase
$190,100
Floor, judicial path (20% IRR)
$69,750 (42.1% of UPB)
Floor, non-judicial path
$114,800
Reinstatement path
$129,000
Modification, 5% over 480 months
$94,490
Deed in lieu path
$120,278
Calloway tape: ask
$435,240
Calloway tape: sum of floors
$413,483
Pool IRR at the ask
17.7%
Book's bid and its IRR
$333,499, 30.2%
Read the tape loan by loan, not as a pool. At the ask, the buyer overpays $104,467 on the loans where the ask is above the floor and is carried by $82,710 on the two loans where it is below. Take those two loans, C-1 and C-5, out of the purchase and the rest, bought at the same share of principal, returns 5.6%: the pool return is carried by two loans.
How to use it on your own tape
Enter your loan on Inputs: principal, rate, payments made, months delinquent, value, taxes and insurance.
Set the timeline for your state: judicial or non-judicial, sale month, legal cost.
Read the floor on Harlow first: it is the most you can pay if every workout fails.
Compare the paths on Paths, then bid between the floor and the best realistic path.
For a pool, fill one column per loan on Tape and compare each floor to the ask before agreeing a blended price.
Questions people ask about it
Is this mortgage note pricing model really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference. Every path is a monthly cash flow discounted at the required IRR, and the floors are their present values.
What is the floor value of a non-performing note?
The highest price at which the foreclosure path alone still earns your required return. It is the bid that protects you if the borrower never pays again, which is why the model computes it before any workout.
Can I use it on a real tape?
You can reuse the structure. The loans and every figure in them are fictional, foreclosure timelines vary by state, and nothing here is investment or legal advice.
The rest of the files
This template is one of the two companion workbooks for The Mortgage Note Investor: the other works through two more notes, Mesquite Lane and Oriole Court. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.