Tenancy Deposit Tracker Excel - Free Template

1. Open the Instructions sheet first. Review the editable settings in B15:B18, including the £50,000 annual rent boundary, the 0.416667 and 0.5 multipliers, and the 30-day review period.
2. Go to Deposit Register and enter one unique Deposit ID per tenancy. Use rows 2 to 101 for up to 100 records.
3. Complete the tenant, property address, town or city, postcode, landlord or agent, tenancy dates, deposit received date, monthly rent and deposit amount.
4. Select DPS, TDS or MyDeposits in Protection scheme, then enter the scheme reference, date protected and information issued date where applicable.
5. Choose Active, Ended or Renewed in Tenancy status. The workbook will calculate the protection review date, days to review date and record status.
6. When the tenancy ends, enter the deposit returned date, amount returned, amount retained, dispute status and notes. Check the Deposit return check result.
7. Use Summary Dashboard to review totals, status counts, completion percentage, deposit values, scheme breakdowns and town or city values. Investigate any Action required, Overdue or Check figures result.

A tenancy deposit tracker suits landlords, property managers and letting agents who need a working record for several occupied or recently ended tenancies. It is particularly useful when deposit information sits across email, scheme portals, tenancy files and bank records rather than in one property-management system.

Landlords managing a small portfolio

A landlord with six flats can enter six rows in Deposit Register and review the dashboard during a monthly property administration session. For example, records for Oliver Bennett at 18 Wren House, Amelia Hughes at 42 Cotton Mill Court and Harry Wilson at 7 St Martin's Court include different towns, rents, deposit values and protection schemes.

The fields are specific enough to distinguish those records: Oliver's monthly rent is £4,500 and the deposit is £1,800; Amelia's figures are £2,400 and £1,250; Harry's are £2,800 and £1,100. The Summary Dashboard can therefore show total deposit value, average deposit and values grouped by London, Manchester or Birmingham.

Letting agents at the weekly review

An agent can use the register when onboarding a tenancy, after recording protection information, and again when a tenancy ends. A Friday review of the Record status column gives a focused list of records that are Complete, Overdue or Action required instead of requiring a search through every tenancy file.

The scheme reference fields also support a practical handover. A record can show DPS, TDS or MyDeposits, its reference, the date protected and the information issued date. If a dispute arises, the Dispute status list provides None, Negotiation, Scheme adjudication or Court, while Notes holds the associated administrative explanation.

Property teams coordinating locations

Teams working across towns can use the dashboard's town or city summary. Tenancies in London, Manchester, Birmingham, Leeds and Bristol are listed as dashboard categories, with further named locations available through the prepared summary area. This makes the workbook useful for a portfolio review rather than only one property.

The workbook separates editable inputs from calculated outputs. You enter source information in Deposit Register and review policy settings in Instructions; formulas then populate selected checks and Summary Dashboard metrics. This is preferable to typing a status or review date manually because changing an input updates the related result.

Policy settings and cap review

Instructions contains four editable administrative values: a £50,000 template annual rent boundary, a lower-band multiplier of 0.416667, an upper-band multiplier of 0.5, and a 30-day template review period. The cap formula compares monthly rent multiplied by 12 with the boundary, then applies the relevant multiplier to monthly rent. It returns Within template cap or Review cap.

For a clearly labelled hypothetical example, monthly rent of £2,000 produces annualised rent of £24,000, so the lower-band calculation is £2,000 × 0.416667 = £833.33, subject to the workbook's displayed precision. A £1,000 deposit would produce Review cap because it exceeds that calculated figure. Treat this as the workbook's editable policy calculation, not legal advice.

Date and status logic

Protection review date adds the Instructions review period to Deposit received date. If a deposit is received on 18/08/2026 and the setting is 30, the calculated date is 17/09/2026. Days to review date subtracts TODAY() from that result, so it changes when the file is opened on a later date.

Record status requires a Deposit ID. It returns Complete only when protection and information dates are present and no later than the calculated review date; otherwise it can return Action required or Overdue. Deposit return check tests whether Amount returned plus Amount retained equals Deposit amount, returning Reconciled or Check figures.

Input validation

Dates are validated in the tenancy, protection and return date fields, while rent, deposit and returned amounts accept non-negative decimal values. Lists restrict protection scheme, tenancy status and dispute status, reducing variations such as DPS scheme and Deposit Protection Service typed differently.

Deposit administration usually becomes difficult when one missing field affects several downstream checks. In this workbook, a blank Deposit received date prevents the protection review date from calculating, and that in turn leaves Record status as Action required rather than giving you a usable review position.

Missing dates create false uncertainty

Suppose a record contains a scheme reference and date protected but no information issued date. The row may look substantially complete, yet the formula will not return Complete. If five of 20 rows lack that date, the dashboard can show five Action required records even though the physical work may have been done. The cost is a second search through five tenancy files.

Entering a date in the wrong field creates the same problem. A protection date belongs in Date protected, not Information issued date; a return date belongs in Deposit returned date. Use the column headings and the date validation rather than copying an undifferentiated list of dates into the row.

Return figures fail to balance

The reconciliation formula is deliberately exact: Amount returned plus Amount retained must equal Deposit amount. If the deposit is £1,800, £1,500 returned and £250 retained totals £1,750, so the result is Check figures and the £50 difference needs investigation. Do not overwrite the calculated retained amount to hide the discrepancy.

Policy inputs are altered without review

The cap calculation depends on B15, B16 and B17 in Instructions. Changing £50,000, 0.416667 or 0.5 changes the result for every populated row. For example, a £2,400 monthly rent and £1,250 deposit may produce a different label after a multiplier change. Record why an approved administrative setting was changed and review the resulting rows.

Another avoidable error is using free text for controlled categories. Typing TDS with an extra space can separate it from the dashboard's TDS criterion, whereas the validation list keeps the category consistent. The same principle applies to Active, Ended and Renewed, and to the four dispute statuses.

The tracker works best when it is attached to a fixed property-management routine rather than treated as an occasional filing exercise. Set one short review immediately after your rent or property administration run, then a second review when a tenancy ends. The objective is to update source fields while the supporting record is open, not reconstruct events later.

Use a repeatable review sequence

• Start with new tenancies: create the Deposit ID and enter the rent, deposit, received date and scheme details.
• Review rows where Record status is Action required or Overdue, then complete missing dates or investigate the underlying record.
• For ended tenancies, enter return and retention figures together and resolve every Check figures result.
• Open Summary Dashboard after editing and compare the status counts with your working list.

For example, if a portfolio has 30 rows and three show Action required, make those three the first task in the weekly review. If one £1,800 deposit has £1,500 returned and £300 retained, the reconciliation result should change to Reconciled once both amounts are entered.

Protect the workbook's logic

Keep formulas in L, P, Q, S, W and X intact. Enter data only in the intended input columns, use the supplied lists, and keep dates in DD/MM/YYYY format. The freeze pane at A2 and filter across A1:Z101 help you work through the register without losing the headings.

Review Instructions before relying on the cap output, because its settings are editable administrative values. The Instructions sheet also states that arrangements can vary across the UK and that the workbook is an administrative tracker, not legal advice.

Know when to move on

There are 100 register rows. If your portfolio exceeds that capacity, several people need simultaneous access, or you require document storage, permissions and a full audit trail, move to a dedicated property-management system. Export or retain a controlled copy of the workbook so the transition does not lose deposit IDs, references, dates or notes.