A free tax lien certificate yield calculator in Excel, with a worked certificate
The face amount, the payoff rule, the crossover month and the IRR by bid and redemption month, all as live formulas: seven sheets, one fictional certificate, no macros.
The file follows one tax lien certificate from the face amount to the yield. Calder County certificate C-0412 is won on sale day at a bid of 0.25% a year, on a face amount of $3,983.75. Redeemed after two months, it pays $4,182.94, because the county's 5% minimum charge governs at that bid, not the 0.25% rate: annualised, that is a 20.3% IRR. Held the same 0.25% bid to month 12, the payoff is unchanged but the IRR falls to 4.4%, because the same gain is spread over a longer holding period. Every one of those numbers is a formula you can move.
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.
Calder_Certificate_Yield.xlsx (33 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.
Need the full project or fund? The Pro Editions go further: Development (60 months, S-curves, senior and mezzanine loans, JV waterfall), $99 → · Fund (up to eight assets, European or American waterfall), $129 →
Want every model at once?
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).
Real Estate Fund Model, Pro Edition: a closed-end fund with up to eight assets, fees, European or American waterfall, 30 built-in checks, a blank copy and a 19-page guide. $129 → · See what is inside
REPE Interview Prep Pack: 18 real estate PE interview cases solved step by step, with three Excel models. $129 →
Real Estate Acquisition and Development Model, Pro Edition: monthly ground-up model over 60 months, S-curves, senior and mezzanine loans, bulk or unit sale, JV waterfall, 33 checks, a 14-page guide. $99 → · See what is inside
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
Seven sheets, in the order the calculation runs. Inputs are blue type on a yellow fill and sit on one sheet; everything else is a formula.
Read Me
What the file is, where each sheet sits in the book, and the conventions it follows.
Inputs
Calder County's regime (the maximum rate, the bid step, the 5% minimum charge, the fee, the payment lag, the Treasury bill rate and the investor's hurdle) and the lines of C-0412's face amount, in one place.
Certificate
The face amount built up from the delinquent tax, delinquency interest, advertising and the collector fee; the payoff rule worked on five cases; and the crossover month at which interest at the bid overtakes the minimum charge.
Yield_Grid
The IRR a year for every combination of bid and redemption month, plus the same rate found a second way, with IRR() over a monthly cash-flow row rather than RATE over two dates.
Decomposition
Where the advertised 18% goes on a one-year hold: what the one-month payment lag costs in IRR, separately from what the flat $10 fee costs.
Premium
A hypothetical premium-bidding regime, in the two variants a county can run (the premium refunded without interest, or forfeited to the county), and the break-even premium for a target rate, solved in closed form.
Checks
Every figure the book prints set against the cell that computes it, with a verdict on each line: 132 checks, all PASS.
What the worked certificate returns
Measure
Value
Face amount of C-0412
$3,983.75
Paid by the investor (face plus the $10 fee)
$3,993.75
Winning bid
0.25% a year
Payoff if redeemed in month 2
$4,182.94
IRR a year, redeemed in month 2
20.34%
Payoff if redeemed in month 12 (same 5% minimum)
$4,182.94
IRR a year, redeemed in month 12
4.36%
At an 18% bid, redeemed in month 12
16.24% IRR, not 18%
Crossover month at an 18% bid
3.33 months
Break-even premium under premium bidding, for the Treasury bill rate
7.09% of face
Read the middle rows together. At a 0.25% bid the certificate pays exactly the same $4,182.94 whether it is redeemed in month 2 or month 12, because the 5% minimum charge, not the bid, decides the payoff below the crossover month.
What changes is how long the money was out: the same dollar gain spread over twelve months instead of two turns a 20.3% annualised rate into 4.4%.
The 18% case shows the same effect from the other side: a bid that looks like the yield still comes out at 16.2% once the payment lag and the fee are counted, which is the gap the Decomposition sheet prices.
How to use it on your own certificate
Open Inputs and overwrite the blue cells: your county's minimum charge, its fee, its payment lag, and the face amount and bid of your certificate.
Read Certificate first. Check which case governs the payoff: the bid rate or the minimum charge, and in which month one overtakes the other.
Use Yield_Grid to read the IRR at any bid and redemption month at once, instead of recalculating one case at a time.
Look at Decomposition if your certificate has a payment lag or a fee: it separates what each one costs in IRR from the bid itself.
If your county runs premium bidding, use Premium to find the break-even premium before you bid above the maximum rate.
Questions people ask about it
Is this tax lien yield calculator 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: the payoff, the crossover month and every IRR are computed directly, with no cell that feeds back into itself. Iterative calculation is switched off in the file. It recalculates in Excel, LibreOffice or Google Sheets.
Why is the IRR so much higher than the bid?
Most tax sale counties charge a minimum on redemption, whatever the bid: here 5% of the face amount. A certificate won at 0.25% still collects that 5%, and redeemed after only two months that turns into a 20.3% annualised rate. Read the bid as a ceiling on what the county can charge the owner, not as the yield you will earn.
Can I use it for a real tax lien certificate?
You can reuse the structure once you replace the county's rules with your state's actual statute and your own certificate's numbers. Calder County, the certificate and every figure in this file are fictional, and nothing here is legal, tax or investment advice.
The rest of the files
This template is one of the companion files for The Tax Lien Investor: the full set adds
the redemption record and sixty-certificate portfolio (Calder_Redemption_Portfolio.xlsx), a tax deed workbook (Wexley_Tax_Deed.xlsx),
and blank templates for your own certificates (Blank_Templates.xlsx). 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.