Your Rental Property Has Three Numbers, Not One

A few days ago I had Claude look through my bookkeeping data to help me update my transactions categorization labels. It came back with an observation that made me go hmmm: two of my rental properties were reporting zero dollars of county property tax for the year.

They had each paid about twenty-four hundred dollars through mortgage escrow.

Nothing was broken. No formula had failed, no import had crashed, no rule had misfired. After my labeling refresh, every transaction in my checking account was categorized correctly, which is what I built and described in From My Grandfather’s Ledger to the Plaid API. The problem was that this particular money had never appeared in the checking accounts that I linked to the Plaid API.

If you are building your own custom bookkeeping system ... this is the second thing you will hit. The first is getting transactions in and categorized. That part is well covered. The second is discovering that a correctly categorized ledger can still produce wrong data for your tax return. To make end of year taxes easier, like I did to my bookkeeping ... A joyous vision stirred within that thought.

The money that never touches your checking account

Three of my properties have mortgages. Every month I send the bank one payment for each of them. The bank splits it four ways: principal, interest, county tax, and homeowners insurance (PITI). The last two (tax and insurance) sit in an escrow account until the bill comes due, and then the bank pays it on my behalf.

From my checking account’s point of view one payment went out, the mortgage payment.

So my ledger recorded a mortgage payment and stopped there. The insurance the bank paid, the tax the bank paid ... invisible. Not miscategorized. Absent. And every year I would sit down with the paperwork and hand-enter them, or at least, hand-enter some of them.

Here is the part worth knowing if you are building your own bookkeeping automation (and I recommend you try it). That data is available. I didn’t know the Plaid API could pull this data, so I had written my assumption into a code comment: mortgage loan accounts return no transaction stream, so skip them. That was a few months ago, but then this week I asked Claude to check that assumption against the live Plaid API. Claude adjusted my API call brilliantly, and the loan accounts came back with eighteen transactions ... including line items reading HOMEOWNERS INSURANCE and COUNTY TAX, with exact amounts and exact dates. I was excited!

There is a general lesson in that which has nothing to do with bookkeeping. When comments in your codebase are assertions somebody made many moons ago, they should be challenged or re-tested. When you point an AI at a system you built, the highest-value thing you can ask is not “write me a new feature.” It is “check whether what I wrote down is still true.”

If you want the concrete first move, it is smaller than it sounds. I did not ask for a redesign. I asked Claude to read my workbook and my import scripts and tell me what it noticed. That is the whole prompt. It came back with the zero-dollar tax line, a stale comment, and a category that was reaching no tax line at all. None of those were things I was thinking to ask about. We can all be forgiven for not asking about a gap we do not know exists.

The second move took more planning time and thought effort, but it was worth it. I had to re-think my bookkeeping objectives through my AI designer and planner. Why I was manually booking insurance payments and then reversing them to track a payment made by escrow. Why a transfer from another household account is not income. Why one property’s spending is capital and another’s is maintenance. Some of those explanations held up. One of them turned out to be a workaround I had invented years ago and then forgotten was a workaround. You do not find that by reading your own spreadsheet. You find it by having to justify it to a person, or smart tool, that asks why.

A property has three numbers, not one

Once that escrow data started flowing, I had a second problem, and perhaps the more interesting one.

My property tabs each showed a single net number: income minus expenses. Simple. Easy to understand, but that number was really trying to answer three different questions at once.

What did this property cost me this year? That is a cash question. Every dollar that left my bank counts, including the principal portion of the mortgage.

What did this property actually earn me? That is an economics question, and it has a different answer, because paying your principal is NOT a “cost”. When I pay down mortgage principal, that money moves from my checking account to the home equity of my balance sheet. It is savings. Solely counting principal as a cash flow expense neglects the alternate view of owning more of your property’s equity.

What is deductible? That is the tax question, and it has a third answer. Interest is deductible, principal is not, and the insurance and taxes the escrow paid are deductible even though the bank writes those checks for me.

One property, three questions, three legitimately different numbers. So the tabs now carry all three, stacked, each labeled with the question it answers. Cash Flow. Economic Return. Tax Basis. Beautiful!

Here is the part a reader can reuse. Your human brain makes the connections that output a new good idea. Claude did not propose it. I said I wanted to know how much each property was costing or profiting me overall, separately from what my accountant needs, and the three-view structure fell out of taking that sentence literally. The design work was in refusing to collapse two different questions into one number because a spreadsheet row can only hold one. Thinking outside the box, literally.

If you build something like this, the tooling will happily give you a number. Deciding which question the number answers is 100% yours. Then seeing it get built is a wonderful payoff!

I had Claude label each block with its question directly on the sheet. The label sits above the number, so a version of me in eighteen months can easily remember why they are there.

Where to put the thing a spreadsheet cannot compute

Mortgage interest is not a transaction. My banks do not send me a monthly line item that says “interest.”, maybe yours does. For me it is arithmetic performed on a balance, and it belongs somewhere that is neither your ledger nor your tax sheet.

So there is now a small sheet holding one row per loan: rate, opening balance, current balance, and the computed interest. Everything else reads from it.

Two things about that sheet are worth stealing.

The first is the method. Claude tested three ways of computing the interest against the number reported by the bank API. Averaging the opening and current balances landed within about a third of a percent. This was good enough for me because I’ll validate and correct it when I get my 1098 at the end of the year. A month-by-month amortization schedule (which sounds more rigorous) came in three times worse, because it seems my lender doesn’t publish the escrow portion of each payment to the Plaid API. The simpler “averaging” method won because my portfolio is small enough not to need an exact number for this field yet.

The second is the sheet needs to know how many months of the year have elapsed, and the obvious formula is the current month. Claude wrote it, I approved, and it was correct every single day of the year except on January first when the formula resets to 1. So it would report one month of interest instead of twelve on the morning you sit down with your 1098 to prepare the return. It now derives the months from the tax year on the return itself rather than from today’s date.

Something has to watch the spreadsheet

This is the piece I would build earlier if I were starting over, and it is the least obvious.

The Python that writes to my workbook uses a library called openpyxl, and openpyxl has a habit of turning live formulas into the number they last evaluated to. The cell still shows the right value. It just stops recalculating, forever, silently.

I found eighteen cells on my tax summary in exactly that state. Total expenses and net income for five of my six properties, all frozen, all displaying correct numbers at that moment, none of them ever going to update again.

There is now a script that keeps a manifest of every cell that is supposed to contain a formula and checks them after each sync. It runs automatically. When something flattens, it says so.

If you build a system where scripts write to a spreadsheet you also open by hand, build this second script to check the integrity of your formulas, right after you get transactions importing. It is completely undetectable by looking, and a workbook is not a thing you read closely. You glance at a total and move on. That is the entire failure mode.

What stays manual on purpose

There is a desire, once this is working, to automate everything. I have not.

Two of my mortgage lenders are smallish banks not compatible with the Plaid API (or any API that I’m aware of), so those loan balances get typed in once a year off a statement. Those cells are marked ESTIMATED, and the refresh script prints a reminder every time it runs that the number is provisional and the January tax form should replace it.

Home Depot has no categorization rule, and will not get one. During a renovation or maintenance those charges belong to the property. Once finished they are spending I manually label for that property, so those transactions land in a review queue and I decide each one.

Depreciation comes from my accountant. It stays a manual cell with his name effectively on it. Maybe someday I figure this out for myself.

The system does not need to do everything. It needs to do the parts that are mechanical and reliable, and clearly flag the parts that aren’t.

What it added up to

This week was a big upgrade to my bookkeeping automation in the third quarter of my first year of having built it. The tax summary now reports about thirty-six thousand dollars more in net income than it did last week. Not because I earned more. Because it had been double-counting the escrow, once inside the mortgage payment and again as a disbursement, and counting principal repayment as if it were an expense.

The county tax that started this ... four thousand seven hundred and eighty-one dollars across two properties. Finally correct in my automation for the first time.

My grandfather kept his books in a ledger, by hand, in his beautiful writing style. The thing he was teaching was that you check and understand. The tools have changed enormously since Grandpa Ray balanced his columns by hand, and what I can build in an afternoon now would have been science fiction for him. The habit underneath contains identical rigor, only faster.

If you are building your own and you get stuck on the escrow piece, or on where to put the arithmetic that is not a transaction, my inbox is open.