portfolio management spreadsheet

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.

Portfolio management spreadsheet - main page

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)

Property portfolio page 2

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

Property Portfolio page 3

The Total Returns Tab

  • Rental Earnings
  • Total One Off costs
  • Capital Gain (inc. One off costs)
  • Net Annual Returns
  • Total ROI%

Property Portfolio page 4

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

Conclusion

This could be a very useful format for future projects in the sector.  Extra functionality can easy be added via automation using VBA

Excel has very powerful graphing and visualisation tools, and this project offers a foundation for far reaching development and analysis in the future.

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