REIT-TIREMENT - REIT Investing & Personal Finance

A blog about REIT investment & personal finance

Friday, April 26, 2019

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.

11 comments:

  1. Thanks for sharing the ideas

    ReplyDelete
  2. Thank you for sharing the spreadsheet and your ideas to evaluate reits. I really enjoy your blog.

    ReplyDelete
    Replies
    1. Happy to know that u enjoy reading my blog. Please continue to support

      Delete
  3. Thanks for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with extra information? It is extremely helpful for me. inventory template

    ReplyDelete
    Replies
    1. What kind of extra information are you looking into ?

      Delete
  4. Great write-up, I am a big believer in commenting on blogs to inform the blog writers know that they’ve added something worthwhile to the world wide web!.. monthly budget templates

    ReplyDelete