All Forum Posts by: Kim Hopkins

Hello!

We had a dishwasher leak in one of our AirBnBs (in Flagstaff) that destroyed the kitchen and dining room and the (ugly tile) flooring was removed in those areas. It's the same flooring throughout the common area (open concept). As such, we're replacing all the common area flooring. 

There is real hardwood in the 3 bedrooms. 

Our contractor has suggested Engineered Hardwood for the common area because it's thicker (1/2 inch thick) vs LVP and so the transition from the bedrooms to common area hallways will be smoother. He says that with LVP, we would need some sort of sub-floor installed underneath it to match the thickness of the hardwood. 

Two questions: 

1) Is it true that engineered hardwood is really subpar to LVP in durability? I hear mixed comments. Some say it scratches easily (e.g. from dogs which we allow) and will buckle if a small pool of water sits too long. Others say they've had no issues. 

2) Is there a specific type or specification of LVP we can buy that would best address the concerns of matching the height of the real wood (best here meaning lowest cost relative to durability and transition height requirements)? 

Thanks!
Kim

@Ronald Rohde

Not sure what you mean by combining into a single company. We have 20 or so LLCs... 

NNN will only take you so far. Passing a 200% increase to tenants does not go over very well.

Any ideas on how to lower premiums? 

Help! I know property insurance has gone up in certain locations such as Florida. But, our property Insurance has gone up across the board, across multiple states and in multiple asset classes including single tenant industrial, multi-tenant industrial, and retail. Our single tenant industrial properties have gone up over 200% in Portland, Oregon and over 100% for multi-tenant industrial and retail properties in Oregon, Washington, Utah, and Sedona (Arizona). We've been with our insurance broker for years. Is anyone else experiencing this? Is there anything that can be done?

Ok, so here's an attempt to answer my own question. 

First, a correction. Cap Rate is defined as NOI/FMV, not NOI/Equity which might have been unclear from sloppy writing above. Also I said Return on Asset removes all debt service, which is total garbage if you define Return as Cash Flow after debt service. Sorry. Thinking as I go here.

Second, I still think the "Return" on Equity in ROE should be defined (NOI-Debt Service)/Equity instead of Cash Flow/Equity since Cash Flow includes all one-time expenses such as lease commissions and capital expenditures. I wrote about this issue here: https://www.biggerpockets.com/...

Anyways, here's the solution I've come up with so far: 

1. First, you need to derive the FMV of a property using price per foot or other metrics that do NOT include the cap rate.

2. Once you've set your FMV, now you look at the RESULTING cap rate and compare it to market cap rates. If the cap rate of your property is low, that means you have an underperforming property in terms of NOI. In other words, you have an operational problem.

If you can increase the NOI, great. Continue to the next step below.

If you can't increase the NOI, consider selling by evaluating the returns you could get from a new property. There's no amount of debt or other financial instruments (e.g. LOC) that you can "add" to this property to make it competitive with a performing property (i.e. one that has a market NOI).

3. The next step is to move on to Return on Equity. We define ROE := (NOI-Debt Service)/Equity to remove one-time expenses like capex and leasing commissions.

We already checked that the Cap Rate was market. If the ROE is low, this gives two possible cases.

Case 1: We add some sort of leverage to the property. This could be debt or a LOC for example. If this improves the ROE, then this property is a potential keeper.

In this case, we would assume that the equity pulled out would be invested in a new property. So the total return of this case is the return of the original property (now with leverage) and the return of the new property.  

Case 2: If the ROE does not improve with the leverage, then we should evaluate selling the property and purchasing a new property to see if we can improve the returns. 

That's all I've got. I'm probably missing something huge. Welcome feedback. 

Hello! 

I've put together a portfolio KPI calculator for our properties and am now realizing that I'm unclear on the best definition of "return" to use in calculations for things like Return on Equity (ROE) and Return on Investment (ROI) .

I've always defined "Return" here as Cash Flow, where Cash Flow is defined as: 

Cash Flow := NOI - Debt Service - Other Expenses.

Here, Other Expenses (or perhaps better named "One Time Expenses") include non-operating expenses such as Leasing Commissions, Capital Expenditures, and Tenant Improvements. 

My definition of ROE has always been: 

ROE := Cash Flow / Equity. 

But if I want to use ROE as a measurement of the property's general performance and to help inform potential buy/sell decisions, using Cash Flow in the numerator doesn't make a lot of sense. It's including the Other / One Time Expenses. So if I replaced a roof or had a large new lease, it could drastically lower the ROE in a given year, making it look like an underperforming property whereas that typically might not be the case. 

Instead, I think we should define ROE as either: 

ROE := NOI/Equity

OR

ROE := (NOI - Debt Service) / Equity.

Investopedia disagrees with all three definitions above and says to use Net Income in the numerator which deducts things like depreciation which makes zero sense for our purposes of analyzing property performance since one good cost seg study would wipe out your income all together. 

Why can't I find this discussion anywhere? What do you think is the correct answer?

Hello! 

So I finally finished our property wide portfolio analysis. Here's an example of the 2022 KPIs I'm measuring for a few of the properties in the portfolio. 

A couple important notes on calculations and definitions: 

• Equity is defined as the FMV estimate less the loan balance (if there is a loan). FMV estimates use market knowledge and appraisal estimates when available.
• Cap Rate is the 2022 NOI divided by the FMV estimate. I actually used this number to adjust the FMV estimate where needed, if I saw a cap rate that was obviously too high or low for the market.
• Return on Capital. This is the 2022 Cash Flow (CF) divided by the total capital (cash) invested in the deal, included original down payment, closing costs, capex, etc. In year 1, this would be the cash on cash.
• Return on Equity. This is the 2022 CF divided by the total Equity in the property as defined above. 
• Return on Asset. This is the 2022 CF divided by the FMV for the property as defined above. 
• Profit Margin. This is the 2022 NOI divided by the 2022 Income for the property. 
• Expense Ratio. This is the 2022 Operating Expenses divided by the 2022 Income. 
• Leverage Ratio. This is the loan balance as of 2022 divided by the FMV as defined above. 

Also it is important to note that it should be assumed for this discussion that these properties are operating at peak performance, i.e. all value add has already been implemented. We're talking about financial performance here.

Question: How do you decide which properties are underperforming financially?

1. At first I thought the only metric I really needed to pay attention to was the Return on Equity (ROE). After all, this is telling me the current return on my cash invested. But then I realized a few problems with this... 

2. First, the definition of "Return" in this case means Cash Flow. Cash Flow is after things like Tenant Improvements, CapEx, and Leasing Commissions. So if the property got a new roof or had a major new tenant lease in the given year, this could wildly reduce your ROE. I mean, if it got a new roof, it certainly didn't perform well that year from a cash flow perspective, so it is a good metric, but not the full picture if you're analyzing for a buy/sell decision.

This can be fixed by looking at the NOI instead of the cash flow, which excludes the non-operating expenses listed above. This is the Cap Rate KPI, by definition.

However, a low Cap Rate isn't necessarily only because NOI is low. It's also a function of the market. Markets that command higher prices will have lower cap rates. So does a low Cap Rate necessarily mean you should sell?

3. The next problem with Return on Equity is that it's ignoring the option of adding debt to debt-free properties (for example, Properties C & E above) or refinancing others. Return on Equity may increase by adding leverage to a debt-free property. This brings us to the Return on Asset metric which removes the debt factor across the board and compares return of all properties based only to their FMV.

So the question here is how do we combine these metrics of Return on Equity, Cap Rate, and Return on Asset to make a uniform consensus on the financial performance of a given property compared to others? 

And what other financial metrics should be taken into consideration that we might be missing? 

Well, @Jeff S. and @Mohammed Rahman, thanks for your interest in this project. You'll be happy to know, or perhaps not care at all :D, that I finally finished this project! 

Through a lot of discussions in other spreadsheet forums as well as long conversations with ChatGPT, I was able to set up a Google Sheets template (without using AppsScript!) that creates a dynamic P&L and KPI analysis dashboard for each property as well as across the entire portfolio. 

Here are the four main reports it produces: 

Here is also a video tour of the template in case anyone is curious. Some final thoughts: 

- I'm sure there are much fancier SAAS programs out there but even with the fancy ones, I have found the portfolio wide KPI analysis like this to be lacking. 

- Additionally, the accounting programs often summarize data differently than how an investor wants to see it (for example, including depreciation in expenses and excluding interest payments). Many of them also do not contain the property wide analysis functionality. 

- I think this is a really nice way for an Asset Manager (NOT a property manager) to see the annual and YOY performance of their portfolio, without having to use a huge property management software that is both expensive and redundant if they have third party property managers. 

I hope this post helps other people who may look for this type of solution in the future. 

Cheers,

Kim 

We use Ryan Group to protest it every year. They work on contingency (only a fee as percentage of savings if successful) and we're really happy with them. But I agree with you, it was totally insane this year!! Ridiculous!

 Hi @Jeff S., yes I experienced exactly what you described! ChatGPT would give me a formula I didn't believe, and I would ask if it "was sure about that answer" and it would apologize and say its syntax was wrong and give me another answer. Very interesting, albeit you are correct, it was frustrating in this case since it went on and on with several wrong answers until I fact checked and realized it indeed was completely incorrect and had no idea how to approach this problem. 

I'm currently still trying to figure out a workaround. The basic question, in case anyone reading this is interested, is how to create formulas dependent on rows of data in a database, which can live update when new rows of data are added. 

I figured out a very complicated way to do it by adding a column for calculated amounts and adding rows at the top for calculated Accounts (e.g. NOI). The new calculated amounts column calls in formulas based on the calculated Account (e.g. NOI vs CFBDS vs ROE etc) but now I'm stuck on how to call in formulas in a clear manner.

ChatGPT is stuck on this as well, so I'm back to Stack Overflow and asking the "human" gurus. 

As an aside, I can't believe it's this hard to create a simple  P&L with KPI analysis in Google Sheets. (The complexity is coming from the need for it to auto update when new rows of financial data are added). And if anyone has a better "system" for creating such a spreadsheet, I'm all ears! 

Hey @Jeff S.!

So, FANTASTIC suggestion to check out ChatGPT for this. Love that idea. Thank you so much for taking the time to plug in my question and share the response. 

Unfortunately, I've learned after about 4 hours of brain pain that ChatGPT isn't always correct on such matters. 

First, its suggestion to insert a Profit column := Income - Expense doesn't make sense as written, because the Incomes and Expenses are represented on different ROWS in my database, not in COLUMNS as it is assuming. 

I managed to workaround that using SUMIFS formulas to calculate the Profit by Property in each row. As you can see, it repeats the profit for the given property each time the property appears in a row. (I got that idea from ChatGPT so thanks again there!).

I then tried to create a pivot table using steps 4-8 that you shared from ChatGPT. As far as I can see, its instructions are wildly inaccurate here. I can't figure out any way to pivot the data to have Profit appear as a row with the profit amounts lined up under the property's other income and expense rows.

Moreover, Step 5 says to click on the "drop down arrow next to Rows to create pivot group". There is no such drop down arrow, and the pivot group function doesn't work in the way ChatGPT suggests. Additionally there's no "formula" section, and if by this, it means a "calculated field", then we're back where we started with the issue of calculated fields producing only columns instead of rows. 

Like you, I've also done a lot of programming in VBA and Google Scripts and would be happy to nerd out any time. In particular, if you have another idea on how to arrange this database correctly, or how to tweak ChatGPT's instructions, I'm all ears! Snippets below.

Thank you again,

Kim

Database with ChatGPT's Suggested Profit Column Added: 

 Nonsensical Pivot Table Using ChatGPT's Database: