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.
Download the template
One Excel file, no macros, nothing locked. No account, no email address.
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. There is no sign-up, no email address and no paid version.
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.