Rental Property Spreadsheet With a Capex Line

Hey folks, it's Ren here.

I keep a small notebook for the rental my partner and I let, and for the first year it told me a lovely lie.

Rent in, a few bills out, a tidy gap left over.

Then the hot-water system died in one expensive week and the whole year's gap vanished. The notebook had never made room for the cost I knew was coming one day.

That is the line a good rental property spreadsheet never forgets.

"The rent is not the return." — Ren

🏠 Why a generic budget fails a rental

A generic budget fails a rental because it tracks the regular bills and misses the occasional big ones. Rates and insurance are easy to remember. The roof is not, until it leaks.

A rental's real return only appears after you set money aside for the costs that arrive every several years rather than every month. Skip that, and your spreadsheet flatters you all the way up to the repair that wipes the year.

Please do not feel caught out if this has happened to you. The first big capex bill teaches almost every landlord the same lesson.

• Capital costs like a roof or oven are not monthly, so they get forgotten
• Rent gets recorded as expected rather than actually received
• Costs across two properties merge into one fuzzy number
• Yield is quoted on the gross rent, before any expenses

📊 What a rental property spreadsheet should track

A rental property spreadsheet should track every dollar in and out for each property, then show the net and the yield. The structure is simple once the capex line is in it.

Here is the core of each property's tab:

Line	What it is
Rent received	Actual rent that landed this month
Running costs	Rates, insurance, management, repairs, interest
Capex set-aside	A monthly amount for big one-off items
Net per month	Rent minus costs minus set-aside
Yield	Annual net divided by property value

The capex set-aside is the line landlords forget, and it is the one that decides whether a property is genuinely profitable or just profitable until something breaks.

🛠️ How to set up one tab per property

You can set this up in an evening, one tab per property, and let it run month to month. Take the steps in order so the net figure ends up honest.

1. Give each property its own tab. Keep one property per tab so rents and costs never blur together.
2. Log rent as it lands. Record the rent received each month, not the rent you hope for, so vacancies show up honestly.
3. Capture every running cost. Enter rates, insurance, management fees, repairs and interest as they occur.
4. Add a monthly capex set-aside. Put aside a fixed amount each month for the big one-off items like a roof or a hot-water system.
5. Calculate net and yield. Subtract costs and the set-aside from rent to get true net, then divide annual net by the property value for yield.

⚠️ Mistakes to sidestep

• No capex line. Fix it: set aside a fixed monthly amount for big one-off repairs.
• Logging hoped-for rent. Fix it: record only rent actually received, so vacancies are visible.
• One merged number for two properties. Fix it: one tab each, compared side by side.
• Quoting gross yield. Fix it: calculate net yield after every cost, including the set-aside.

If you also do your own books, a 1099 expense tracker spreadsheet keeps deductible costs ready for tax time.

🎯 Your action steps this week

• Make one tab per property.
• Enter this month's actual rent and every running cost.
• Set a monthly capex amount and start the set-aside now.
• Calculate net and yield, and slot it beside your small business budget template.
• Review the yields side by side and decide what to hold.

⚡ Quick answers

What is a rental property spreadsheet?

It is a sheet that tracks income, expenses and yield for each property you let, so you can see the real return after costs rather than guessing from the rent alone.

Why include a capex line?

Big one-off costs like a roof, hot-water system or oven are not monthly, so they ambush landlords who only track regular bills. A small monthly set-aside spreads them out and keeps your net figure honest.

How do I calculate rental yield?

Take the annual net income after all costs, divide it by the property value or purchase price, and multiply by one hundred. Net yield after the capex set-aside is the figure that survives a big repair.

Should I use one tab per property?

Yes. One property per tab keeps rents and costs from blurring, and lets you compare each property's yield side by side instead of seeing one merged number.

Is this different from a normal business budget?

Yes. A generic business budget misses the property-specific lines, especially capex set-asides and per-property yield, which are the numbers that tell a landlord whether each property is actually worth holding.

The notebook is retired now. The hot-water system has a quiet line of its own, filling up month by month, so the next expensive week will not be a surprise.

To your financial freedom,
Ren

This article is for general information only and is not financial advice. It does not take into account your personal situation, needs or objectives. Please consider speaking with a qualified financial adviser before making financial decisions.