Property Portfolio Management Spreadsheet
Creating a Property Portfolio Management Spreadsheet
There are a huge number of 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 or not on their investment. Estate agencies will often do all these functions for them under a managed service contract, but to do this properly the estate agent needs a robust and reliable system to monitor the income and outgoings and capital growth, and to help 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
The estate agency managed a large number of properties on behalf of the landlords, and it urgently required a system to keep track of what was happening with each property so that it could report back to the landlords on how well their property portfolio was doing financially
“Excellent all round service, no complaints at all and will use again.” TCE.
Using Excel, we built the property portfolio workbook for an estate agency, to help them keep track of their clients’ property portfolios and investments. The idea was that the agent put in the necessary data, and the workbook calculated all the important indices such as ROI and net annual returns over a possible 10 year period. This could clearly save agency a lot of time, offering a very good potential ROI on the cost of the original project.
The Excel Workbook had essentially 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 re-furbishing
- 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 so that these could 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 capital growth of the property, 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 charts from the Chart Tab 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 Excel Experts created a multi-tab spreadsheet to capture all this information in an easy and reliable manner and to add filters so that the estate agent could focus on a particular landlord or a property to see the exact financial picture at a given 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. Excel has a huge number of built-in statistical functions available and some of these were used to calculate various important indices and KPIs (Key Performance Indicators).
By 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 on a regular basis.
The whole application was designed to run on Microsoft Windows, but could be easily adapted to run on an Apple Mac
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 a great deal of employee time and costs were saved through the use of this system, and the customers of the estate agency were extremely happy 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.
“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 of 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 see how they can automate and streamline your business with a custom Excel application