Property Portfolio Management Spreadsheet

Using Excel Dashboards, we built the property portfolio workbook for an estate agency, to help them to 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.

The Excel Workbook has 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

This could be a very useful format for future projects.

The only VBA was used to print pdf files of the charts – but this whole application could be easily adapted to run under Mac.