sheetsmith

Templates › Home and property

Rental Property Income & Costs

Made for landlords with one to ten properties.

A workbook for a small portfolio: one row per property with its rent and mortgage, one row per payment in or out against a property, and a summary showing income, costs and the position for each property and overall. The summary carries a chart that redraws itself as you type.

Tabs
3
Columns
21
Fill themselves in
27

£7one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 1,240 rows.

Properties

One row per property you rent out, with the monthly rent coming in and the monthly mortgage going out. The totals on the right fill in from the Payments tab. Room for 40 rows.

ColumnTypeWorked out as
Property Reftext
Addresstext
Typechoice (list)
Statuschoice (list)
Monthly Rentcurrency
Monthly Mortgagecurrency
Monthly SurpluscurrencyIF(ISBLANK([monthly_rent]), "", [monthly_rent] - [monthly_mortgage])
Money In To DatecurrencyIF(ISBLANK([property_ref]), "", SUMIF([Payments!property], [property_ref], [Payments!money_in]))
Money Out To DatecurrencyIF(ISBLANK([property_ref]), "", SUMIF([Payments!property], [property_ref], [Payments!money_out]))
PositioncurrencyIF(ISBLANK([property_ref]), "", SUMIF([Payments!property], [property_ref], [Payments!money_in]) - SUMIF([Payments!property], [property_ref], [Payments!money_out]))
Payments LoggednumberIF(ISBLANK([property_ref]), "", COUNTIF([Payments!property], [property_ref]))

Payments

Every payment in or out, logged against one property and one category. Put rent and deposits in the Money In column and all costs in the Money Out column. Room for 1,200 rows.

ColumnTypeWorked out as
Datedate
Propertychoice (from Properties › property_ref)
Categorychoice (list)
Descriptiontext
Methodchoice (list)
Money Incurrency
Money Outcurrency
NetcurrencyIF(AND(ISBLANK([money_in]), ISBLANK([money_out])), "", [money_in] - [money_out])
YearnumberIF(ISBLANK([date]), "", YEAR([date]))
Month NonumberIF(ISBLANK([date]), "", MONTH([date]))

Summary

The position across the whole portfolio, plus the figures for the current calendar year. Every line reads straight from the Properties and Payments tabs.

MeasureWorked out as
Properties on the booksCOUNTA([Properties!property_ref])
Properties currently letCOUNTIF([Properties!status], "Let")
Total monthly rent expectedSUM([Properties!monthly_rent])
Total monthly mortgageSUM([Properties!monthly_mortgage])
Monthly surplus before costsSUM([Properties!monthly_rent]) - SUM([Properties!monthly_mortgage])
Average monthly rent per propertyIFERROR(AVERAGE([Properties!monthly_rent]), 0)
Total money in, all timeSUM([Payments!money_in])
Total money out, all timeSUM([Payments!money_out])
Overall position, all timeSUM([Payments!money_in]) - SUM([Payments!money_out])
Money in this yearSUMIFS([Payments!money_in], [Payments!txn_year], YEAR(TODAY()))
Money out this yearSUMIFS([Payments!money_out], [Payments!txn_year], YEAR(TODAY()))
Position this yearSUMIFS([Payments!money_in], [Payments!txn_year], YEAR(TODAY())) - SUMIFS([Payments!money_out], [Payments!txn_year], YEAR(TODAY()))
Position this monthSUMIFS([Payments!money_in], [Payments!txn_year], YEAR(TODAY()), [Payments!txn_month], MONTH(TODAY())) - SUMIFS([Payments!money_out], [Payments!txn_year], YEAR(TODAY()), [Payments!txn_month], MONTH(TODAY()))
Repairs and maintenance this yearSUMIFS([Payments!money_out], [Payments!category], "Repairs", [Payments!txn_year], YEAR(TODAY())) + SUMIFS([Payments!money_out], [Payments!category], "Maintenance", [Payments!txn_year], YEAR(TODAY()))
Costs as a share of money inIFERROR(SUM([Payments!money_out]) / SUM([Payments!money_in]), 0)
Strongest property by positionIFERROR(INDEX([Properties!property_ref], MATCH(MAX([Properties!net_position]), [Properties!net_position], 0)), "")
Weakest property by positionIFERROR(INDEX([Properties!property_ref], MATCH(MIN([Properties!net_position]), [Properties!net_position], 0)), "")
Payments logged with no propertyCOUNTIFS([Payments!property], "")
Payments logged in totalCOUNT([Payments!net])