← All prebuilt solutions
Property

Letting Agent Client Money Reconciliation

Client ledgers, three-way reconciliation, deposits, NRL tax and landlord statements – for letting and property agents

£20
Buy nowAsk a question

Secure checkout. Your download link is sent as soon as payment clears.

Letting Agent Client Money Reconciliation

Overview

If you collect rent, hold deposits or pay contractors for landlords, that money is client money. It isn't yours, it must be kept in a designated client account, and you must be able to show at any moment exactly whose it is. This workbook does that. You record every movement on the client bank account once, in the Cash Book. The workbook then builds a separate ledger for every landlord and every tenant, reconciles the bank statement, the cash book and the total of all those ledgers each month (the 'three-way reconciliation'), and flags anything that needs action.

Price
£20
Who it’s for
Property
Works with
Microsoft Excel for Windows and Mac
Buy now

See how it works

A walk through the main sheets

The sheets you will use most, in the order you will use them – shown with the sample data every workbook comes with.

Step 1

Landlords

One client ledger per landlord with the year-to-date figures, balance, fee check and status. What you do: add landlords and opening balances.

Landlords
Tenants

Step 2

Tenants

One ledger per tenant: deposit held, deposit protection, deposit cap and rent arrears. What you do: add tenancies, deposit details and protection dates.

Step 3

Cash Book

Every movement on the client account and every transfer between ledgers. What you do: enter transactions and cleared dates.

Cash Book
Reconciliation

Step 4

Reconciliation

Bank v cash book v client ledgers for the report month, outstanding items, sign-off and a 12-month log. What you do: enter the bank statement balance and sign-offs.

Who it is for

Made for people like you

  • Example – text
  • Letting and managing agents – A client ledger per landlord and per tenant, monthly reconciliation with sign-off, and printable landlord statements.
  • Property managers and block managers – A clear audit trail of money in, money out and money held, ready for your CMP scheme or accountant.
  • Agents with overseas landlords – Non-Resident Landlord (NRL) tax worked out by landlord and quarter, with HMRC payment dates.
  • Owners and compliance leads – A dashboard, an exceptions list and a controls checklist showing whether client money is safe.

Quick start

Up and running in minutes

  1. Example – text
  1. Settings – Enter your agency details, CMP and redress schemes, the tax year, the first day of your year (normally 1 April) and the report month. Set your default management fee and check the policy settings.
  2. Opening balances – Enter the reconciled cash book balance at the end of the previous month, plus any NRL tax, suspense or interest held then. Enter each landlord's balance b/f on the Landlords sheet and each tenant's deposit held b/f on the Tenants sheet. The 'Opening difference' on Settings must show £0.00.
  3. Landlords and Tenants – Add one row per landlord (with an ID such as L001) and one row per tenancy (with an ID such as T001 and the landlord's ID).
  4. Cash Book – Enter every movement on the client account in date order: date, reference, type, tenant or landlord ID, description and amount. Enter the date each line cleared on the bank statement.
  5. Reconcile – At each month end, set the report month on Settings, type the bank statement balance into the monthly log on the Reconciliation sheet and check all three figures agree.
  6. Clear the exceptions and sign off – Work through the Exceptions sheet, then record who prepared and who approved the reconciliation in the log.
  7. Send statements – Pick each landlord on the Landlord Statement sheet and print or save it as a PDF.

Key features

  • A client ledger for every landlord (150) and tenant (400) built from one client account cash book of 2,000 lines: rent, fees and VAT, repairs, landlord payments, deposits and NRL tax.
  • Monthly three-way reconciliation – bank statement v cash book v the total of every individual ledger – with outstanding items, sign-off and a 12-month log.
  • Red flags for overdrawn ledgers, unidentified receipts, balances held over 12 months, unprotected or over-cap deposits, rent arrears and fee differences.
  • Printable landlord statements for any month, NRL tax by quarter and landlord, fee income, an interest policy check, a compliance checklist and a dashboard.

What’s included

  • Dashboard – Client money at a glance
  • Settings – Agency, period, policies, opening balances
  • Landlords – One ledger per landlord
  • Tenants – Deposits, protection and arrears
  • Cash Book – Every client account movement
  • Reconciliation – Three-way reconciliation and sign-off
  • Landlord Statement – Print a statement for any month
  • NRL Tax – Non-resident landlord tax
  • Monthly Summary – Rent, fees and money held
  • Exceptions – Everything needing action
  • Compliance – Controls and interest policy
  • Tax Rates – NRL rate and VAT
  • Guide – How to use this workbook

How buying works

Your file is only released once payment has cleared.

1

Click Buy now

You are taken to our secure checkout.

2

Pay securely

Checkout takes a minute and you get a receipt by email.

3

Download your file

Your download link arrives as soon as payment clears.

Explore the range

Trial Balance to Accounts Mapper

Trial Balance to Accounts Mapper

Turning a trial balance into statutory accounts means mapping every account to the right heading, posting year-end adjustments, and then building a profit and loss account, balance sheet and notes in the format the Companies Act requires – for this year and last year.

£20
View details →
Staff Appraisal & Performance Tracker

Staff Appraisal & Performance Tracker

Good appraisals are fair, consistent and joined up: objectives agreed at the start of the year, regular 1-to-1s, honest ratings against a clear competency framework, a calibration meeting so one manager's…

£20
View details →
Practice Timesheet & WIP

Practice Timesheet & WIP

An accountancy practice sells time, but most of its fees are fixed.

£20
View details →

Need it tailored to your business?

We can adapt any prebuilt solution or build something bespoke, with a lifetime bug-free guarantee.

Custom spreadsheetsTalk to us

Part of AML Finance Group

Need an accountant too?

Our sister firm, AML Accountancy Solutions, is an award-winning accountancy practice helping businesses and individuals across the UK with their accounts, tax, bookkeeping and payroll.

Visit AML Accountancy Solutions
AccountsTax compliance and planningBookkeeping and VATPayroll and CISCloud accounting softwareBusiness growth and planning

Get In Touch

We're open Monday to Friday, 9am – 5pm. Call our Plymouth office on 01752 42 72 82 or send us a message below, whether it's about consultancy, spreadsheets or our document templates.

Services
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Quick Links

Business ConsultancyCustom SpreadsheetsPrebuilt Spreadsheet SolutionsBusiness Document TemplatesAbout UsTestimonialsInsightsFAQsContactAccountancy – AML Accountancy Solutions

Company information

Offices
Plymouth
First Floor, Unit D2, 4 Neptune Park, Plymouth, PL4 0SJ
Phone Number
01752 42 72 82
Email
info@amlconsultancysolutions.co.uk
Opening hours
Monday to Friday: 9:00 am to 5:00 pm

Legal

Privacy PolicyCookies PolicyDisclaimer PolicyTerms and ConditionsComplaints ProcedureAccessibility Statement

AML CONSULTANCY SOLUTIONS LTD
Registered Address: First Floor, Unit D2, 4 Neptune Park, Plymouth, United Kingdom, PL4 0SJ. Company Number: 12747461