Sign in or start a free trial to avail of this feature.
9. Operating Cash Flows
Operating cash flows are the cash flows received by the investor each year from rental income. Cash flows can be positive or negative, depending the on expenses incurred.
Operating cash flows
- Operating cash flows are the cash flows received by our client during the life of the investment
- To calculate operating cash flows, subtract debt service and taxes from net operating income
- Note: If early cash flows are negative, the investor may need to pump additional cash into the asset
CTRL + C: Copy selected item(s)
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
In our model, our two remaining unknowns, Capital gains tax and the Sale Price relate to the final sale of the asset.
As a result, we can use what we've already calculated in our model to find the operating cashflows of the asset, which are the cashflows generated from the rental income minus the expenses.
To calculate these cashflows, we start with Net Operating income and subtract our debt repayments.
This will leave us with cashflows before tax.
We then subtract the income taxes to give us the final after-tax cashflows.
So let's go to Excel where I've entered the necessary rows for calculating After-Tax Cash Flows.
Let's start by linking the Net Operating Income to the value in the previous section and the debt service will simply equal to the Total Debt Service figure in the Mortgage section.
The Before-Tax Cash Flow would simply equal to the sum of these two values, and the Income taxes would simply equal to minus the Value of the Income taxes paid in row 78.
The After-Tax Cash Flow will then equal to the Before-Tax Cash Flow less the Income taxes paid.
I can copy these cells across with Alt + E S F.
From the model, we can see that we have negative cash flows for the first couple of years, before a rising net income eventually creates some positive cash flows in the later years.
This means that our client will need to invest additional money after buying the asset to cover these cash losses.
When we mentioned this to Joe, he tells us that he does not want to put any cash in to cover losses, and asks us to find out what's causing the problem.
As we can see from our cash flow calculations, the loan repayments are simply too big for the net operating income.
To reduce our loan repayments, we have a number of options.
If we look on our dashboard, we can see that the loan amount can be reduced, the interest rate could be reduced, and the loan term could be increased.
When we run our assumptions past Joe, he agrees with our conservative stance on interest rate and duration, and so the only remaining option is to decrease the loan amount.
If we decrease the loan amount, this means that our repayments will be lower, but the amount of money that Joe contributes to buying the asset initially, will have to be higher.
I'm going to decrease the Loan amount to say $850,000.
When I now return to my financial model, I can see that the After-Tax Cash Flows are now positive, and Joe won't have to contribute any more cash after buying the asset.
When you're building a model, you'll often have to make changes like this when the first set of results emerge.
Life can be made a lot easier if the changes can be made in the Control Panel and not by writing new Excel code.
And that's where learning to structure models correctly is so important.