Saturday, May 11, 2019

How to Come Out a Basic REITs Analysis Summary

After previous post about data collection, now I would like to share out how to use those data to generate a Basic REITs Analysis Summary through Google Spreadsheets. I will use 3 REITs examples, which are SPH REIT from previous post, First REIT and Ascott Residence Trust.

This summary would require consolidation of a few google spreadsheets that I have shared previously. Below are the post of spreadsheets used, I would suggest you to go through first before you continue this post:
i) How to Create Your Own Basic REITs Screener
ii) Relative Valuation for REITs.
iii) How to DIY a REITs Data Collection Spreadsheet
We have data now, time to make it into information
This summary require more formula work, therefore I have added SREITs Basic Screener as overview page for formula link up. I have added a few more columns as compared to previous version, which are:
i)  Shortname - For formula to link up with specific sheet name
ii) Dividend Ex-Month - To indicate rough ex-dividend month, more useful to semi-annual type
iii) Latest Financial Period - Result quarter is by formula; annual report to be updated manually.
iv) Support / Distribution % - To know whether particular REITs come with income support

Here is the link for this google sheet. You could refer below for the overview:
Once you completed particular REIT data collection, you could copy formula from the any of 3 examples and paste into empty cells. Make sure you name the tab according to short name in order for formula to work. If you find any formula showing N/A, try delete and undo it to refresh.

Next, let's look at the analysis summary.
You would need to make a copy so that you could select the REIT counter from cell A1. Once you select, it would summarized all information from particular REIT data page. You could see some graphs where data range is at cells A61 to V72. You could print this 2 pages summary to pdf or hardcopy, I have already set the page break.

Feel free to make a copy, modify it and suit to your own use. Note that I am not a good designer on the layout, so for those who can re-design the summary into nicer layout, please feel free share with me. This is the first google spreadsheet that come with links between different tabs, you may require a bit more time to digest and familiarize. Let me know if you find any error or need any help on this spreadsheet.
And now, I have a bad news
One bad news would be, my work which is on project field, is at peak period since 2 months back. This would required me to work long hour weekday and sometimes weekend which expected to last for another 2 months. I would still try to squeeze time for writing, but I couldn't do that on weekly basis as of now. For all the readers, really thanks for your support all the way, if you have anything would like to contact me, I would definitely reply you.

Friday, May 3, 2019

Consolidation of SREIT Related Sites

When I start investing in SREITs, I could not find a place that consolidates all the SREITs related sites. Even today, I still could not find such site. I believe this is frustration that REIT investors are facing.
Now, instead of waiting, I have decided to consolidate one. Since a lot of sites cover topics more than REITs, I would consolidate based on the following criteria:
i) Have a dedicate "REIT" page to link with REIT posts
ii) Contain at least 5 REIT posts
iii) Latest REIT post is within 6 months

1) Dollars and Sense - REITs
2) Financial Horse - REIT & Fixed Income
3) Heartland Boy - REITs

REIT Data/Screener:
1) REITScreener (Subscription)
2) Yield Savvy (Subscription)
3) My Stocks Investing Journey - REITs

Hope the above consolidated links helpful. If you find any websites that met with the 3 criteria or if you would like me to add your blog, contact me here.

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.