How to DIY a REITs Data Collection Spreadsheet?

A journey of a thousand miles begins with a single step, looking back, I have posted for total 12 topics about REITs, which you could find in REITs Investing page on top section of my blog. And now maybe is time to consolidate all ideas and put in action for data collection.

Idea + Action = Result

Before we start, let's summarize what data to be collected:
i) Debt Profile of REITs
- Gearing
- Interest Cover Ratio
- Cost of Debt
- Fixed Debt %
- Unsecured Debt %
- Weighted Average Debt Maturity
- Highest % of Debt Maturity in Same Year

ii) Lease Profile of REITS
- Occupancy
- Revenue per Available Unit
- % of Hedged/Derived Currency in SGD
- Weighted Average Lease Expiry
- Highest % of Lease Expiry in Same Year
- Weighted Average Land Lease Expiry

iii) Diversification Profile of REITs
- Major Sector Contribution
- Top Geographical Location Contribution
- Top Property Contribution
- Top Tenant Contribution
- Top 10 Tenants Contribution

iv) Basic Financial Metrics of REITs
- Distribution per Unit (DPU)
- Net Asset Value (NAV) per Unit
- Property Yield

v) Uncommon Financial Metrics of REITs
- Distribution over Management Fee
- Distribution over Capital
- Distribution Margin
- Income Support over Distribution

vi) Outstanding Shares of REITs
- Dilutive Share Increase
- REIT's Manager Shareholding

vii) Relative Valuation of REITs
- Quarter Average Price

viii) Growth Information of REITs
- NAV Growth
- DPU Growth
- Interest Cover Ratio Growth
- Property Yield Growth
- Distribution over Capital Growth
- Distribution Margin Growth

ix) Other Information
a) Income/Cash Flow/Distribution Statement
- Gross Income (Property Gross Income  + Interest Income + Realized Income from Derivative Financial Instrument/Foreign Exchange + Distribution from Associate/Joint Venture)
- Net Property Income
- Management Fees (Base + Performance)

- Financial Engineered/Income Support

- Distribution from Associate/Joint Venture

- Distribution to Unitholders

b) Balance Sheet/Other Statement
- Properties Valuation
- Interest in Associate/Joint Venture
- Net Asset Value to Unitholders, NAV
- Perpetual Securities & Preferred Stock
- Total Debts
- Outstanding Share

After list out all required data above, let's group it and put it into Google Sheets. I use SPH REIT as example for template below. Feel free to make a copy and modify for your own use through this link.

Green columns are for you to keep in data, where blue columns are formula.You could try re-arrange or modify it to suit your own use. You could also hide column C after you complete your data collection to make it look more tidy.

Note that some of the values I added formula for simple average while some are not. For those without average value, I find that latest value is more useful, for e.g. average gearing ratio or average WALE are not meaningful. This template use simple average, you could modify it to weighted average to suit your own analysis. I have added "override" column in case you find average value is not realistic.

Feel painful to collect data ?

It would be a painful process initially as you would require to collect years of data from quarter results and annual reports. But once you completed the first round, you should be able to update it every quarter within 10 minutes. Let me know if you find any error in the spreadsheet above. Hope it is helpful.