Calculating your cash flow forecast

Our readers may have noticed that with my property stories I have been including a snippet of yield/cash flow calculator analysis. This is the tool I want to talk about, as it's a very handy little tool to help me determine the offer price, yield as well as forecasting cash flow when the property settles.

The cash flow calculator allows me to achieve the following:
1. Determine the gross rental yield of a potential deal (to see if it ticks my yield box)
2. Determine pre-tax cash flow forecast of the deal
3. Risk assessment — how much the property will cost to hold at a higher interest rate
4. Determine the acceptable offer price

The one I use looks like this:

Let's cover each topic one-by-one.

1. Determine the gross rental yield of a potential deal

Gross rental yield as a percentage can be calculated very easily. The formula is:

Annual Rent (Weekly Rent x 52 weeks)
----------------------------------------------------------- x 100
Purchase Price of Property

In the cash flow calculator, this is determined by putting in the purchase price and the lower and upper rent (all highlighted in yellow) as part of the process. Then the yield on purchase (highlighted in green) will be calculated automatically.

For my deals in Logan to-date, I have been focusing on yield that are over 6 per cent at a minimum. Using the cash flow calculator it provides a clear vision for me on how much lower, upper rent and purchase price combination I need in order to achieve the yield I wanted.

2. Determine the pre-tax cash flow forecast of the deal

This is where the cash flow calculator really comes in handy, as it allows a projection of the pre-tax cash flow once the property has settled.

The factors that affect the estimated cash flow are:
1. Loan amount
2. Estimated expenses such as council rates, water rates, insurance, etc.
3. Estimated rent

These fields are highlighted in yellow, with output in grey:

For me, it's easier to calculate annual expenses. That's why I input the figures in annually instead of monthly or weekly.

For example, almost all water rates are around $150 a quarter, so multiply that by four and I'll just put in $600 as the annual amount. Same thing with building and landlord insurance, as you're usually quoted on annual rate basis.

Once these figures have been put in you won't need to change them, unless you're moving into a new state or different council which may have different rules.

As these are indicative only, it doesn't have to be exact to the dollar. The idea is to be able to quickly determine the projected cash flow by entering the purchase price (which determines the loan amount) and the estimated rent.

If the forecast cash flow is positive then the number will be black, otherwise it's shown as red. In this example as you can see a $300,000 property at 88 per cent loan to value ratio and 5 per cent interest rate will need to be rented at approximately $380 a week in order to break even.

3. Risk assessment: How much will the property cost to hold at a higher interest rate

Related to point two above, from the risk assessment perspective, an investor may want to know how much will this property cost to hold if interest rate goes up. Again, this is where the cash flow calculator comes in handy.

This can be done by updating the annual repayments field formula and change the percentage from 5 percent to 6.5 per cent or 7 per cent.

Below is what happens when I change the formula to simulate 7 per cent interest rate, with no change to existing rent:

As you can see the weekly, monthly and annually cashflow is now affected and yearly repayment can be severely impact the cash flow, especially if the loan amount is closer to the $1 million mark!

If interest rate goes up, there will also be an upward pressure on the rent as landlords will be passing some of the interest rate rises onto the tenant.

However, to be conservative for the worst-case scenario, I usually leave the rent as is to see how much annual repayment it will be. This figure helps forming a portion of the total cash flow impact when you have a property portfolio.

For example, when interest rate goes up to 7 per cent, for a $264,000 loan and a $325 weekly rent, you'll need to fork out approximately $8,167 a year before tax as your holding cost.

4. Determine the acceptable offer price

If we put all this information together, it forms as part of the due diligence I use to work out the optimal offer price. The detailed steps are:
1. Research all recurring expenses, such as council and water rates, strata fees, insurance. You can usually get these from sales agents, property managers or sometimes from the sales listing.
2. Call several property managers to confirm the achievable rent of the target property. This will feed into your lower rent and upper rent.
3. Input all the data collected into the cash flow calculator
4. Add in the asking price, and confirm loan amount, to get the yield on purchase
5. Tweak the purchase price until you hit the optimal yield you want to achieve. This will be your optimal offer price for the agent
6. Assess the estimated weekly, monthly and yearly cash flow position before tax for the proposed property and perform risk assessment at a higher rate if required
7. Submit your optimal purchase price to the sales agent. If the offer is rejected, don’t be disheartened — simply move on to the next opportunity! Otherwise congratulations – the property is now under contract to you. Time to move on to organising finance and building and pest inspections, if applicable!

Share