# The Tax Lien Investor: companion files

By Julian R. Sterling. These files are served at julianrsterling.com/tax-liens. No sign-up, no email is asked for.

## What is in the pack

| File | What it is | Where in the book |
|---|---|---|
| `Calder_Certificate_Yield.xlsx` | Certificate C-0412: the face amount line by line, the payoff rule with the 5 percent minimum and the 0 percent exception, the crossover months, the IRR a year for every bid and redemption month, where the 18 percent goes on a one-year hold (the lag and the fee), and premium bidding in both variants with the break-even premiums. | Chapters 2 and 3, the premium chapter |
| `Calder_Redemption_Portfolio.xlsx` | The engine of the book: Calder County's redemption record by class, the timing shape, the expected cash flow of one certificate month by month from sale day to month 37, the IRR grid by class and bid, the lowest bid for the hurdle and for the Treasury bill, the sale list and the funnel, the sixty-certificate portfolio and its cash by quarter, the lemons and the stress test. | Chapters 4 to 6, 8 and 12, the worthless-parcel and stress-test chapters |
| `Wexley_Tax_Deed.xlsx` | The deed branch of one Calder County class B certificate (the opening bid, the owner who redeems late, the parcel nobody bids for), and a house bought at a Wexley County tax deed sale: the cost stack, the profit, the surplus, the maximum bid for a margin, the sensitivities and the redeemable deed. | Chapters 9 and 10 |
| `Blank_Templates.xlsx` | Your own working files, blank: a certificate ledger with the payoff and the IRR on every line, a bid sheet by class, a redemption-record tally by class and month, and a portfolio cash calendar with its IRR and its quarters. Every input is empty; a Checks sheet counts what is still to fill. | Chapters 2 to 4, 6, 12 and 15 |
| `Working_Documents.pdf` | Five forms to print and fill in: the statute worksheet, the parcel diligence sheet, the bid sheet, the certificate ledger and the deed-sale checklist. | Chapters 6, 8 to 10, 14 and 15; Appendices B and C |
| `Forty_Questions.xlsx` | Forty questions across the chapters, numeric and multiple choice, marked as you type. Sheet 2 counts your faults chapter by chapter; sheet 3 gives every answer and the reason for it. | Chapters 1 to 15 |

## Three figures to reproduce first

**The rate is not the yield.** C-0412, face $3,983.75, won at a bid of 0.25 percent and redeemed in month 2, pays $4,182.94: an IRR of 20.3 percent a year. Held to month 12, the same payoff earns 4.4 percent. At 18 percent held a year the IRR is 16.2 percent, not 18. (Calder_Certificate_Yield.xlsx, sheets Certificate and Yield_Grid.)

**The portfolio.** Sixty certificates for a sale-day outlay of $229,300, expected later outlays of $72,358, expected inflows of $321,668: an expected profit of $20,010 and an IRR of 7.54 percent a year, below the investor's own 8 percent hurdle. (Calder_Redemption_Portfolio.xlsx, sheet Portfolio.)

**The deed.** A house bought for $92,000 at a Wexley County deed sale makes $51,406 after every cost; the $85,150 bid above what was owed belongs to the former owner and lienholders, and the maximum bid for a 20 percent margin is $106,306. (Wexley_Tax_Deed.xlsx.)

## How the pack maps to the book

| Chapter | Use |
|---|---|
| 1. What you are buying | PDF form 1 |
| 2. The face amount and the payoff | Calder_Certificate_Yield, sheet Certificate; Blank_Templates sheet 1 |
| 3. The rate is not the yield | Calder_Certificate_Yield, sheets Yield_Grid and Decomposition |
| 4. Who redeems, and when | Calder_Redemption_Portfolio, sheets Timing, Classes, Class_IRR; Blank_Templates sheet 3 |
| 5. How the sale works | Calder_Redemption_Portfolio, sheet Classes (the sale list) |
| 6. How low to bid | Calder_Redemption_Portfolio, sheets Class_IRR and Bid_Floor; Blank_Templates sheet 2; PDF form 3 |
| The premium chapter | Calder_Certificate_Yield, sheet Premium |
| 8. Diligence: the funnel | Calder_Redemption_Portfolio, sheet Classes (the funnel); PDF form 2 |
| 9. From certificate to deed | Wexley_Tax_Deed, sheet Deed_Branch; PDF form 5 |
| 10. Buying the deed | Wexley_Tax_Deed, sheets Deed_Deal, Max_Bid, Sensitivity, Redeemable; PDF form 5 |
| The worthless-parcel chapter | Calder_Redemption_Portfolio, sheet Lemons |
| 12. Building the portfolio | Calder_Redemption_Portfolio, sheets Portfolio and Quarterly; Blank_Templates sheet 4 |
| The stress-test chapter | Calder_Redemption_Portfolio, sheet Stress |
| 14. Reading your state's statute; Appendix C | PDF form 1 |
| 15. Tax, structure and the owner; Appendix B | Blank_Templates sheet 1; PDF forms 2 to 5 |
| All chapters | Forty_Questions.xlsx |

## Using the workbooks

Yellow cells with blue type are inputs; grey cells are formulas. Change an input and every sheet follows. The workbooks contain no macros, no circular references and no external links, and nothing is locked, protected or hidden. They recalculate when opened in Excel, LibreOffice or Google Sheets.

Each of the three worked workbooks ends with a Checks sheet that sets every figure the book prints beside the live cell that computes it, with a tolerance of half a unit of the last printed digit, and a set of logic checks: the IRR at each lowest bid clears its target and one step lower does not, the IRR at each break-even premium equals its target, the margin at each maximum bid equals its target. Every check reads PASS as delivered.

Months are counted from the sale (month 0). A certificate redeemed in month m pays its face plus the greater of interest at the bid for m months and 5 percent of the face (nothing at a 0 percent bid); the county pays one month later. The IRR is the monthly rate that makes the money paid out and the money received back, at their dates, worth the same, compounded to a year as (1 + monthly)^12 - 1.

To check the blank template against the book, type C-0412 into the ledger (minimum charge 5 percent, fee $10.00, lag 1; face $3,983.75, bid 0.25 percent, redeemed in month 2): the line returns $4,182.94 and an IRR of 20.3 percent.

Calder County and Wexley County are fictional, and so is every party in them. Their rules are the book's assumptions, and Calder County's redemption record is an illustration, not a market statistic.

## No warranty

The files are provided as they are, without warranty of any kind, express or implied. They are teaching material, not legal, tax or investment advice. Your state's statute, the county's sale terms and your own counsel decide.