Property Portfolio Management Spreadsheet
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 Property Portfolio Income Tab
The first tab you see when opening the workbook is The Income Tab. This tab, where you input information about the properties in your portfolio such as:
- Cost of Property
- Date bought
The Income table then computes values such as:
- Net Income
- Gross Yield.
- Total Outgoing
- Total one-off costs ( Stamp duty/Conveyancing, HMO and refurb_estimate)
- Recurring annual costs (service charge/ground rent; insurance/void costs)
The Property Portfolio Capital Tab
In the Capital tab we calculate
- Asset Value
- In year Capital Gain
- Accumulated costs for each property year on year
The Total Returns Tab
- Rental Earnings
- Total One Off costs
- Capital Gain (inc. One off costs)
- Net Annual Returns
- Total ROI%
The Property Portfolio Charts Tab
The Property Portfolio charts show:
Gross Rental Yield Per Property Per Year
Capital Gain Per Property Per Year (Plus one off costs)
and Total ROI per property per year
Excel has very powerful graphing and visualisation tools, and this project offers a foundation for far reaching development and analysis in the future.