Strategy – how to determine best offer price and cashflow forecast via cashflow calculator

Hey guys! You may have noticed that with my property stories I have been including a snippet of yield/cashflow calculator analysis. This is the tool I want to cover today as it’s a very handy little tool to help me determine the offer price, yield as well as forecasting cashflow when the property settles.

I think as an investor this is an absolute minimum tool to have. At the end of the day investing is all about using money to make money – but before you can make any equity you should understand how much the holding costs are per week, month or year in order to determine whether this is an investment property you can hold onto long term! This is especially important for people who are looking to buy and hold in Sydney as the average yield is very low across the board.

The cashflow 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 cashflow 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

And 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 cashflow calculator, this is determined by putting in the purchase price and the lower & 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% at a minimum. Using the cashflow 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 cashflow forecast of the deal

This is where the cashflow calulcator really comes in handy – allows a projection of the pre-tax cashflow once the property has settled.

The factors that affect the estimated cashflow 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 times that by 4 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/update unless you’re moving into a new state or different council which may have different rules. Also 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 cashflow by entering the Purchase Price (which determines the loan amount) and the estimated rent.

If the forecast cashflow is positive then the number will be black, otherwise it’s shown as red. In this example as you can see a $300K property at 88% LVR and 5% interest rate will need to be rented at approx $380 a week in order to break even.

---

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

Related to 2 above – from 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 cashflow calculator comes in handy.

This can be done by updating the Annual Repayments field formula and change the percentage from 5% to say, 6.5% or even 7%. Below is what happens when I change the formula simulate 7% 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 cashflow especially if the loan amount is closer to the 1 million mark!

Usually 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 (worst case scenario) I usually leave the rent as is to see how much annual repayment it will be and this figure helps forming as a portion of the the total cashflow impact when you have a property portfolio.

In this example it illustrates the point that when interest rate goes up to 7%, for a $264,000 loan and a $325 weekly rent, you’ll need to fork out approx $8,167 a year (before tax) as your holding cost.

---

4. Determine the acceptable offer price

If we put all these 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, etc. 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 cashflow 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 cashflow 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 and if the offer is rejected, don’t be dishearten – simply move on to the next opportunity! Otherwise congratulations – the property is now under contract you. Time to move onto organizing Finance and Building & Pest (if applicable)!

A copy of the cashflow calculator that I currently use can be found here.

As always, if you have any questions about the above content or how to use the calculator feel free to leave a comment or contact me directly.

Cheers,
David