This worked fine initially and I was quite proud. Lately, I noticed that the chart doesn't actually answer a simple question: How much money am I making? or losing? or wasting?
I realized that this is because in my home-made Excel template I treated CapEx the same way as all my operating expenses. I would just subtract capital expenses out of the gross. Because of this, I couldn't tell if the property was profitable on a day-to-day basis or not.
I went through a painful thinking process and came to a simple conclusion. If I put my data into conventional financial statements: "balance sheet" and "income statement", I can see much clearer how the business is doing. I can also see how every improvement I make (capital expense) affects my net income for years to come.
For example, if I get a new roof for 10K this year for cash. The "10K" disappears from Cash on the balance sheet under assets and re-appears under "CAPEX Roof" still on Assets side. Income statement for the same year doesn't include an operating expense for the roof at all. This was counter-intuitive to me initially because I was the one who personally gave away precious dollars to the roof guy. Now, I think of it as if my "10K" is still with me and re-incarnated as a shiny roof.
Notice that I also pay taxes on the 10K as if my income actually included that cash this year and it wasn't spent on business needs.
However, roofs aren't eternal, so over the next 10 years, I will be deducting 1K "roof expense" on my income statement. This is called depreciation. Depreciation will decrease my income by 1K every year over the next 10 years, and decrease my net income and tax respectively as well. So I make up for the tax I paid.
Now, the benefit of this exercise is that I can see how my properties are doing on a day to day basis given just operating income and expenses. I also know that I have to plan better and have 10K set aside for all upcoming roofs and other big improvements.
Working through depreciation examples on the income statement helped me realize that CapEx for the roof (10K) is a big chunk of money. Even if I set money aside over 10 years, 10K / 10 = 1K is a large portion of my gross. The reality is I can't pay "fake" money to the roof guy or ask him to give me an interest-free 10 year loan. It's on me to come up with the real money.