All Forum Posts by: Kim Hopkins

Hello! 

This is more of a spreadsheet question than a real estate theory question, but I'm asking this group since anyone who has tried to make a P&L this way will have run into this problem! 

Problem: How do you insert a calculated ROW into a pivot table in Google sheets?

In my example below, I have a pivot table from a financial database for multiple properties. The pivot table has rows for Income and Expenses by property (simplified for example), with the properties as columns.

I want to insert ROWS (not columns!) into the pivot table to calculate KPIs like Profit (= Income - Expense). (There are more complicated calculations but simplifying here for sake of example).

I know how to insert a Calculated Field, which provides a COLUMN with the specified formula. However, I can't figure out how to insert a calculated field that produces a ROW with a specified formula.

Example below.

Thank you in advance! 

Kim

Example Financial Database: 

Example Pivot Table: 

In the pivot table example above, I would want to add two calculated rows:

1. 1. The first would calculate the Net Operating Income (= Income Total - Expense Total). This would be a row inserted between rows 10 and 11 above.
2. 2. The second would calculate the Cash Flow (= Net Operating Income - Other Expense Total). This would be inserted after row 13. 

 I already outsource, but thanks!

 Hi there! Appreciate the suggestion to not use Buildium for accounting-only and to not duplicate PM entries. 

With regards to the accounting system, we can't use desktop Quickbooks bc we use a third party remote bookkeeper and our CPA team  needs access. 

Hello!

It's that time of year where we get all our books in order and curse ourselves for the end of year work we have created! 

I have multiple multi-tenant commercial properties with multiple different property managers, who send multiple reports every month. As you might guess, the account names across the reports are all entirely different. For example, one might call an account Trash, where another calls it Utilities - Trash Removal. You get the idea. 

At the end of the year, I want to look at my P&L across ALL my properties. So I have created a key/legend that translates ALLLLL of the property managers' account names into one finite set of consistent account names. It's a real pain in the a$$! 

I'm using Wave for my accounting software right now which used to work great, but now that I have it connected to 30+ bank accounts, there's some glitch that disconnects the accounts every month, and it takes an hour+ to reconnect them. Another pain in the a$$! (And no one at Wave returns messages btw - I've offered them my first born but to no avail). 

There seem to be two types of accounting software tools out there: 

1. Just accounting, e.g. Freshbooks, Quickbooks, etc.

2. Accounting and Property Management, e.g. Buildium, Rent Manager, etc.

If I use #1, I keep going with my painful end of year process. 

With #2, I either get a bunch of PM functionality I don't need, or I utilize the PM functionality by re-entering a ton of data every month that was already provided to me by my PMs. Seems like a waste of time and money! 

What is a girl to do? 

Thanks!
Kim

 I usually just stick to my own deals. Thou shalt maintain control. :)

This has been a very interesting discussion! Thank you everyone who weighed in. 

Mathematically (that means, not talking about what is actually happening in this market, but the actual math in a theoretical example): I think the consensus is that with a few caveats, I'm correct that you don't have to cash flow to investors at the rate of inflation in order to beat inflation because of the properties of (forced and organic) appreciation. 

Caveat 1 was pointed out that if there is pref with no upside, then it's a moot point since the investor does not participate in the appreciation. Regardless of what this particular podcaster had in mind, I've heard several people say the rate of return has to match inflation in scenarios which include upside participation, and that statement, I believe based on our discussion, has errors. 

Caveat 2 was pointed out that if the property depreciates, then my claim is false. However, this challenges all the hypotheses - including the one that a syndicator would even be able to match the returns with the rate of inflation. Though it's possible that the property would continue to cash flow strongly for the hold period while it also deflates, that is certainly in no way a guarantee and really takes the scope of the math problem outside of the intended assumptions. 

There's also been a lot of interesting "real world" observations on what is happening in the market right now. I will say that the differing opinions on this thread says a lot in and of itself! I will also say that personally, I would rather participate in a syndication right now where there is a conservative pref with potential upside and downside (i.e. participation in the appreciation) over a syndication with a "guaranteed" preferred return at a high % to "beat inflation" with no upside. That guarantee seems only as good as the paper it's written on, and my biggest concern is that the operator even feels comfortable offering that in this market. There are many exceptions and I know of many experienced operators who I'm confident could pull off a high pref right now, but based on the point of potential depreciation of assets, and the possibility of a recession which will surely effect commercial rents, I'm looking for conservative underwriting right now. And as Warren says, my goal right now is to "never lose money".

My only point was that I think that the statement that one must have an annual return equal to or greater than the rate of inflation in order to beat inflation is not necessarily mathematically correct.

 How is what I said incorrect? Are you claiming the property doesn't appreciate? 

Sorry, I should have clarified that this is commercial real estate I'm talking about, not houses. Commercial real estate is almost always valued as a function of its net operating income so as the rents increase (even partially) with inflation (which they certainly do in my product type where we have short term leases) and the NOI goes up in general, so does the market value of the property. (of note, while cap rates could increase and drive prices down, the typical hold period will remedy this if it's a good buy, and if it's not a good buy or if income is decreasing, you wouldn't be able to pay the pref either so that scenario is not really relevant to this question).

I recently listened to a podcast interview with a very accomplished and intelligent syndicator. He said that the annual return you anticipate for your investors needs to be at least equal to the rate of inflation in order to match/beat inflation.

For example, if inflation were 10% per year and his return structure had only a preferred return with no split, his argument would be that the pref needs to be at least 10% in order to match or beat inflation.

I question if this is a true statement.

Example: If inflation for example is 10% per year, and I have $1M in the bank, that money has buying power of $900k at the end of the year after being inflated away. 

Instead, if I invest the $1M in a real estate investment, I assume that the value of the property increases roughly with the rate of inflation. 

So for example, if I sold the property after one year, my $1M should have appreciated to $1.1M from inflation, so I'm receiving back $1.1M at least which now has buying power of $1M after inflation. 

I've preserved my capital by simply investing in real estate. I don't need any return to protect my capital from inflation. 

And for that matter, any return above 0% would mean I'm "beating inflation". 

Even moreso (thanks Hunter for this point), if I use leverage and say only put $300k down for this million dollar purchase, I've made $100k on my $300k which 3x beats inflation.

Even if the appreciation rate of the property doesn't trend perfectly with inflation, it still should be close and the point remains the same. 

Thoughts?