There are many private landlords in the UK, and they all need to keep track of how their properties are doing and whether they are making a profit on their investment.
Estate agencies will often do all these functions under a managed service contract. Still, to do this properly, the estate agent needs a robust and reliable system to monitor the income and outgoings and capital growth and explain any discrepancies in the financial numbers.
The Excel Experts recently built a custom Excel spreadsheet system for an estate agency offering management services to private landlords. If you need something similar please do contact us.
The Brief: Create a Robust Tracking and Financial Reporting Tool
The estate agency managed a large number of properties on behalf of the landlords. It urgently required a system to keep track of what was happening with each property to report back to the landlords on how well their property portfolio was doing financially.
Using Excel, we built the property portfolio workbook for an estate agency to keep track of their clients’ property portfolios and investments.
The idea was that the agent put in the necessary data. The workbook calculated all the important indices such as ROI and net annual returns over a possible 10 year period. This could clearly save the agency a lot of time, offering an excellent potential ROI on the original project’s cost.
The Finished Spreadsheet
The completed Excel Workbook made a huge difference to the efficiency and reporting capabilities of the estate agents. The easy to use solution has 5 Tabs – Income, Rent, Total Returns and 2 sheets of charts.
The Income Tab
On the Revenue side the spreadsheet needed to keep track of the following for each property for a landlord:
- Rental income received
- Agency management fees
- Agency charges, e.g. finding a tenant
- Outgoings such as repairs and refurbishing
- Ground rent and insurance
- Compliance charges, e.g. gas safety certificate and electrical safety checks
- Void costs
- Service charges and insurance
- Monthly net payments to the landlord.
It also kept track of the length of the tenancy length and inspection visits to be planned for when required or when the tenancy was coming to an end.
The Capital Tab
On the Capital side, a separate sheet or tab kept track of the property’s capital growth, showing the capital gain in each year, compared to the original cost of the property, and taking into account the one-off costs like stamp duty and conveyancing.
The Total Returns Tab
This tab brought everything together into a summary and showed:
- Rental Earnings
- Total One-Off Costs
- Capital Gain (including One-Off Costs)
- Net Annual Returns
- Total ROI%
The supporting tabs for Income and Capital provided an audit trail to show how these summary figures were calculated.
The Total Returns tab also duplicated the Chart Tab charts so that a visual picture was instantly available.
The Chart Tab
Charts were incorporated in the spreadsheet on a separate tab to show the information visually either by 12 months, rolling 12 months, or historical or forecast years using the following data:
- Gross Rental Yield Per Property Per Year
- Capital Gain Per Property Per Year
- Total ROI (Return on Investment) per property per year
The Application
The Excel Experts created a multi-tab spreadsheet to capture all this information easily and reliably and add filters so that the estate agent could focus on a particular landlord or a property to see the exact financial picture point in time for a given property portfolio.
Important indices were calculated, such as the ROI percentage (Return On Investment), and using Excel’s forecasting tool, showing net annual returns over a possible future 10-year period.
Using specific filters on the spreadsheet data, the estate agent could easily produce meaningful and informative reports on property portfolios to be emailed to the landlord regularly.
The whole application was designed to run on Microsoft Windows and can easily be adapted to run on an Apple Mac.
Conclusion
Thanks to the Excel Experts and the ingenuity of their team, a robust and informative system was put in place that satisfied all the client’s requirements.
The big advantage for the estate agency was that many employee time and costs were saved through this system’s use. The estate agency customers were delighted with the timely and accurate reporting that this system produced on their property portfolios.
A robust platform like this application could easily be developed further according to future client requirements and changes.
Client Feedback
“The Excel Experts has helped me with two projects. The experience of working with the expert assigned to my project has been excellent. The patient asking questions, the completion on time and the follow up have all been highly professional.” N.C
“Excellent all-round service, no complaints at all and will use again.” TCE.
Contact The Excel Experts with your requirements and we’ll help you to automate and streamline your business with a custom Excel application.
