ZorantaGuides
Letvy

Letvy › Guides

Rental Income and Expenses Spreadsheet: A Year-Round Layout

A rental income and expenses spreadsheet works best with four tabs: an income log, an expense log, a monthly summary per property and an annual summary that follows the UK tax year (6 April to 5 April). Every figure on the summaries is a formula that reads from the two logs, so you only type each payment once.

Below is the layout, a cash flow calculator for the monthly view, and the records HMRC asks landlords to keep.

A year of rental income and expenses in a folder

Rental income and expenses spreadsheet: the four tabs

TabOne row isColumns
IncomeOne payment receivedDate, property, unit, tenant, amount, type (rent, deposit, other), reference
ExpensesOne cost paidDate, property, category, supplier, amount, receipt reference, capital? (yes/no)
MonthlyOne property, one monthRent received, expenses, mortgage payment, cash flow
AnnualOne property, one tax yearTotal income, total expenses by category, capital costs, finance costs

Record deposits as their own type and leave them out of rental income. A deposit held for the tenant is not your income, and mixing it in will overstate the year.

Give the receipt reference a simple pattern, such as the date plus a number ("0314-1"), and use the same reference as the file name of the receipt photo. Then anyone can find the paper behind a line in seconds.

Letvy

Letvy's home screen shows the month's rent collected, expenses, loan payment and net cash flow, worked out from what you record. Get it on Google Play

Monthly summary and property cash flow

On the Monthly tab, each cell adds up the log for one property and one month, for example =SUMIFS(Income!E:E,Income!B:B,$A2,Income!H:H,B$1) where column H on the Income tab holds the month. Do the same for expenses, then subtract.

Cash flow is what is left after everything, including the mortgage payment. It is the number that tells you whether a property pays for itself each month. Try your own figures:

Property cash flow calculator

Cash flow per month-
Cash flow per year-

In the example, £1,100 rent less a 4% allowance for empty time is £1,056. Take off £180 of running costs and a £520 mortgage payment and £356 a month is left, or £4,272 a year.

Cash flow and taxable profit are different numbers. The full mortgage payment reduces your cash, but for tax only the interest is relevant, and it is treated in a restricted way for residential lets. Keep the interest and capital parts of the mortgage on separate lines.

Set the annual summary to the tax year

UK landlords report rental income by tax year, which runs from 6 April to 5 April. Build the Annual tab on those dates rather than January to December, so its totals match what goes on your Self Assessment return.

Useful columns for each property:

If you own a property jointly, keep the full figures in the spreadsheet and add a column for your share. HMRC's guidance on working out your rental income explains how different types of ownership are treated.

Landlord record keeping: what to keep and for how long

GOV.UK's page on records for rental income says to keep details of:

Records can be on paper or digital. How long you must keep them is set out on GOV.UK's how long to keep your records page. Keep records for capital costs for as long as you own the property, since they can matter when you sell.

Making Tax Digital for Income Tax requires some landlords to keep digital records and send updates to HMRC using compatible software. Whether and when it applies depends on your income; use HMRC's Making Tax Digital checker to find out.

This is general information, not tax advice. Thresholds, dates and record-keeping rules can change, so check the current guidance on GOV.UK or with an accountant.

Year-end checklist

  1. Check that the Income tab's total for each month matches the rent that reached your bank account.
  2. Look for expenses without a receipt reference and find the paperwork while it is still easy to get.
  3. Move anything that is an improvement rather than a repair into capital costs.
  4. Split the mortgage into interest and capital using the lender's annual statement.
  5. Save a copy of the finished year as a PDF or separate file, and back it up somewhere other than your computer.
  6. Start a fresh Income and Expenses tab for the new tax year, keeping the same columns.

If you hand the year to an accountant, send the Annual tab plus the two logs. A clear breakdown by property and category usually means fewer questions and a smaller bill.

Letvy

Let the totals add themselves up

Letvy records rent and expenses against each property and can produce PDF statements and CSV exports for your accountant.

Get it on Google Play

iPhone version coming soon

More guides

All guides

Sources

Published by Zoranta, the developer of Letvy.