Buy-to-Let Cash Flow Excel - Free Template

1. Open the Instructions sheet first. Read the guidance on input cells and leave the formula cells unchanged.
2. Add or amend each rental property on the Properties sheet. Give every property a distinct Property ID and complete the rent, mortgage, insurance, service charge and letting agent fee fields that apply.
3. Go to Monthly Cash Flow and enter a period and the relevant Property ID. The property address, expected rent and selected recurring figures calculate from the property register.
4. Enter the rent actually received, any other income, repairs and maintenance, other expenses and a useful note. Record income and costs in the month they were received or paid for cash-flow reporting.
5. Check Total Income, Total Expenses, Net Cash Flow, Cash Flow Margin and Cash Flow Status before moving to the next record.
6. Use Other Expenses for costs such as service charges, ground rent, void-period costs, safety checks and sundry items not shown in separate columns.
7. Review the Dashboard after updating the monthly rows. Check the monthly summary, shortfall count and expense composition before making portfolio decisions.

A buy-to-let owner uses this workbook when rental money and property costs are moving through different bank transactions but need to be viewed property by property. It suits a landlord with a small portfolio, a couple managing jointly owned rentals, or a property manager preparing a clear monthly internal review.

The key point is timing. The Instructions sheet directs you to record income and expenses in the month they are actually received or paid, so the record answers a practical cash question: what did this property add to, or take from, available cash this month?

One property with a late rent payment

Consider the illustrative BTL-001 record. The Properties sheet holds monthly market rent of £1,150, a £720 mortgage payment, £38 insurance allowance and a 10.00% letting agent fee. If £1,150 is received and there are no repairs or other expenses, total expenses are £873 and net cash flow is £277.

If only £900 arrives in a later month, the letting agent fee calculated from rent received becomes £90, while the mortgage and insurance remain £720 and £38. Total income is £900, total expenses are £848 and the row shows £52 net cash flow; it remains positive, but the margin is much tighter.

A portfolio with different cost structures

Different properties need separate records because their recurring costs are not interchangeable. The illustrative Leeds flat, BTL-002, has £975 monthly market rent, a £645 mortgage payment, £30 insurance allowance, £145 service charge or ground rent and a 12.00% agent fee.

With £975 rent received, its calculated agent fee is £117. Total expenses are £937, leaving £38 before any repairs or further costs. A £185 repair would turn that month into a £147 cash flow shortfall, which is much easier to spot when it is attached to the correct Property ID.

Useful points in the month

Update the row after rent clears and again when invoices or direct debits have been paid. The Dashboard then combines the monthly records into total rental income, total other income, total expenses and net portfolio cash flow.

Use image 2 for the property register, image 3 for monthly entries and image 4 for the portfolio review. Keep the purpose narrow: this is a cash-flow tool, not a substitute for advice on VAT, tax or investment decisions.

The workbook separates stable property details from month-specific cash entries. This is the right design for a small portfolio: enter the recurring reference data once on Properties, then use the Monthly Cash Flow sheet for actual receipts and costs without repeatedly typing addresses, mortgage figures or fee percentages.

Reference fields and live inputs

Each monthly row begins with Period and Property ID. The address, Expected Rent, Mortgage Payment and Insurance are retrieved with VLOOKUP from the Properties range, while Rent Received, Other Income, Repairs & Maintenance, Other Expenses and Notes are entered for that individual period.

For example, selecting BTL-002 retrieves expected rent of £975, mortgage payment of £645 and insurance of £30 from the illustrative property register. You then enter the actual rent received rather than overwriting the expected amount, preserving a useful comparison between plan and cash.

Income and expense calculations

Total Income is calculated as Rent Received plus Other Income using SUM. Letting Agent Fees are calculated as Rent Received multiplied by the fee percentage held for the selected property; £975 at 12.00% produces £117.

Total Expenses is the sum of mortgage payment, letting agent fees, repairs and maintenance, insurance and other expenses. If the £145 service charge is entered in Other Expenses for BTL-002, then £645 + £117 + £0 + £30 + £145 gives £937 total expenses.

Net Cash Flow is Total Income less Total Expenses. Cash Flow Margin is Net Cash Flow divided by Total Income, with an IF formula returning 0 if total income is zero, which avoids a divide-by-zero error when no income has been recorded.

Dashboard checks

The Dashboard totals rent received across rows 2 to 11, separately totals other income and expenses, and calculates portfolio net cash flow. It also uses COUNTIF to count rows marked Cash flow shortfall, rather than relying on you to scan each month manually.

The monthly summary uses SUMIF against the Period column to group total income, total expenses and net cash flow by month. Do not type over calculated cells: amend the Properties sheet for recurring reference figures or the monthly input fields for actual cash movements.

Buy-to-let cash flow records usually fail through small classification and timing errors rather than difficult arithmetic. A workbook can calculate perfectly from incorrect inputs, so the most valuable check is whether every amount belongs to the right property, period and cost category.

Recording expected rent as received rent

Expected Rent is a lookup from the Properties sheet; Rent Received is a separate monthly input. Treating the £1,150 expected rent for BTL-001 as if it had arrived masks part-payments, arrears and void periods.

In a hypothetical month where £0 is received but £720 mortgage and £38 insurance still leave the account, Total Income should be £0 and Total Expenses should be £758 before other costs. The margin formula returns 0 in that case, while Net Cash Flow is negative and Cash Flow Status reports a shortfall.

Putting recurring charges in the wrong place

The property register contains a Monthly Service Charge / Ground Rent field, but the monthly cash flow calculation includes an Other Expenses input rather than automatically retrieving that figure. This requires a deliberate monthly entry, which is appropriate because cash-flow reporting should reflect when the charge was paid.

If the BTL-002 £145 charge is omitted, calculated expenses fall from £937 to £792 in the example with £975 rent. That makes net cash flow appear as £183 instead of £38, overstating available cash by £145 for that month.

Breaking the reference link

A Property ID must match the register entry exactly for the lookup formulas to return the relevant address and recurring values. Adding a new property first on Properties, then selecting its ID in Monthly Cash Flow, is safer than manually copying a property name or mortgage payment into each row.

The supplied Properties range currently covers the listed entries in rows 2 to 4. If you extend the portfolio beyond that illustrated range, test the lookup references and dashboard ranges before relying on the totals; otherwise, a new record may not be included as intended.

Ignoring notes and status

Use Notes to explain a meaningful movement, such as a one-off repair or delayed rent. A £185 repair is more useful when its reason is visible than when it appears as an unexplained change in the repairs column.

Finally, investigate every Cash flow shortfall row rather than netting it off mentally against a stronger month. A portfolio total can remain positive while one property is repeatedly consuming cash.

This spreadsheet works when it becomes part of the rental-money routine, not a retrospective annual tidy-up. Set one fixed update point after regular rent receipts and direct debits have cleared, then add a second short review after repair invoices or letting-agent statements arrive.

Use a two-stage monthly close

Enter the Period and Property ID first, then record the cash movements you can verify. For example, on a £975 rent month for BTL-002, enter the £975 rent receipt when it arrives, then add the £145 charge and any repair cost once paid rather than guessing the final total in advance.

At the end of the month, check the calculated Total Expenses and Net Cash Flow against your bank activity. A difference of £145 may be a missing service charge; a difference of £117 on the illustrative flat may point to the calculated 12.00% letting agent fee being compared with the wrong payment.

Keep the register controlled

• Use one stable Property ID for each rental and do not recycle an ID for another address.
• Update monthly market rent, mortgage payment, insurance allowance and agent fee percentage on Properties only when the recurring reference figure changes.
• Add a concise note for unusual amounts, including void-period costs, safety checks or major repairs.
• Review the Dashboard after all current-period rows are complete, not while half the costs are still missing.

The supplied Dashboard summary covers 10 monthly periods from January to October 2026. If you need a longer reporting period or more rows, extend formulas, SUMIF ranges and chart sources carefully, then test totals with a known example before using the revised file.

Know when to move on

A spreadsheet remains practical when one person can keep the property register and monthly entries current, and the review takes minutes rather than hours. Move to a dedicated system when you need automated bank feeds, many users, a larger volume of properties or reporting beyond this workbook’s maintained ranges.

Until then, the strongest habit is simple: enter actual cash, retain notes for exceptions and investigate shortfall statuses every month. That gives you a dependable operating view without confusing cash flow with taxable profit.