Cash Flow Spreadsheet

Discussion in 'Loans & Mortgage Brokers' started by Realist35, 2nd May, 2017.

Join Australia's most dynamic and respected property investment community
  1. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    Hi guys:),

    I'm about to get an 8 yr IO loan for a property I just purchased. I'd like to know what my repayments will be after the IO period expires. I understand I can use online bank calculators and just adjust time of the loan to 22yrs, but there are other factors at play too. Rent will increase over the next 8 years making interest repayments lower, as I pay down principal the interest will decrease but the principal will go up (hence less negative gearing benefits) etc.

    Would anyone be able to kindly share a spreadsheet that can simulate all this? I think @euro73 and @Colin Rice mentioned they may have something like this.

    My risk management strategy is to accumulate enough cash so when the IO expires I can cover principal repayments over the next 7 years. That would mean I can hold the property for at least 15 years which should be at least 1 cycle. My rough calculation says that for the next 7 years after IO period expires, I'd need roughly 75k to cover the principal repayments (assumptions: 450k loan, 3.6% yield, 6% interest rate).

    Thanks:)
     
  2. Zoolander

    Zoolander Well-Known Member

    Joined:
    15th Dec, 2016
    Posts:
    668
    Location:
    Sydney
    David Shih and Realist35 like this.
  3. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    Hey thanks:).

    The link doesn't include the spreadsheet, just the snippet? There's not much mention about the principal after IO period and future yearly projections..
     
  4. Peter_Tersteeg

    Peter_Tersteeg Mortgage Broker Business Member

    Joined:
    18th Jun, 2015
    Posts:
    8,130
    Location:
    03 9877 3000
    You can use any loan repayment calculator to figure out the repayments over a 22 year amatorisation period.

    Fairly easy to figure out the rent will be in 8 years depending on what your assumptions are on the rental increase.
     
  5. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    Thanks Pete.

    With regards to the rental increase, is it more realistic to assume:
    1. The rent will increase at the inflation rate (say 2.5%), or
    2. Assume constant rental yield on the property value that grows at a certain rate (say 4% py)?
     
  6. Peter_Tersteeg

    Peter_Tersteeg Mortgage Broker Business Member

    Joined:
    18th Jun, 2015
    Posts:
    8,130
    Location:
    03 9877 3000
    Hard to say, but in Melbourne rental yields haven't even been close to keeping up with property growth. I'd assume the inflation rate.
     
    Realist35 likes this.
  7. Colin Rice

    Colin Rice Mortgage Broker Business Member

    Joined:
    9th Jul, 2015
    Posts:
    3,183
    Location:
    Perth
    Mortgage Choice have a great little app called "Mortgage Choice Loan Helper" I use erryday. Available for android and apple for free.
     
    Ethan Timor, Archaon and Realist35 like this.
  8. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    Thanks:).

    Just did a bit of modeling in excel and was a bit surprised with the result. At 6% IR, my weekly after tax costs would double after 8 yr IO period expires. That's a bit different to what I've read on PC that they would increase by 40-50%.
     
  9. Colin Rice

    Colin Rice Mortgage Broker Business Member

    Joined:
    9th Jul, 2015
    Posts:
    3,183
    Location:
    Perth
    Sounds about right.
     
    Realist35 likes this.
  10. Anthony Brew

    Anthony Brew Well-Known Member

    Joined:
    18th Feb, 2017
    Posts:
    1,176
    Location:
    Australia
    Yeah I was looking at P&I figures also and since they do not take into account offset, the amounts after X years when it reverts to P&I are significant - and that is with 70-80% LVR!

    If you put a lot of the offset money into the loan, it will reduce it, but then your buffer is gone. So I am thinking better to just plan for the need to use some of the buffer say 10k/yr on top of rental income and realise that the 10k is going towards the principle anyway and you have your buffer for a longer period. Over 5-10 years until it is positive without the buffer, that is only 50-100k (which went to the principle) and would be a moderate portion of the offset (if you are saving very very aggressively and already have a bunch in the offset now that is).

    For other people who don't save so aggressively as myself or who don't have a very significant offset, you need to plan your situation carefully so you don't end up **** creek without a paddle when you are switched to P&I.
     
    Chris Au and Realist35 like this.
  11. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    I thought you guys might find this spreadsheet useful. You can simulate quite a few things here. Column Q (monthly after tax cost) is what I was interested in.

    As an example, at 6% IR, 12% LVR, 500k purchase, 3.6% rental yield, 25% marginal tax rate:

    - Current weekly after tax repayments on IO period are $226,
    - In 8 years, after IO period expires and reverts to I&P, after tax weekly repayments are $442 and remain pretty constant until the end of 22 yr period. That is a 100% increase in repayments!
     

    Attached Files:

    Last edited: 2nd May, 2017
  12. euro73

    euro73 Well-Known Member Business Member

    Joined:
    18th Jun, 2015
    Posts:
    6,125
    Location:
    The beautiful Hills District, Sydney Australia

    300K at 6% I/O is $1500 per month, or $18,000 per annum
    300K at 6% P&I over 25 years ( following 5 years I/O ) is $1933 per month, or $23,196 per annum. This is a 28.9% increase over the I/O repayments of the first 5 years .
    300K at 6% P&I over 22 years ( following 8 years I/O ) is $2050 per month, or $24,600 per annum. This is a 36.7% increase over the I/O repayments of the first 8 years .

    These are the "actual" differences. of course, on a lender calculator these are further sensitised as the loan is being repaid over a shorter period of time, which is where the higher figures quoted in previous threads, come from...

    Screen Shot 2017-05-02 at 9.06.37 pm.png Screen Shot 2017-05-02 at 9.06.15 pm.png
     
  13. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    Hey Euro,

    You are not taking into account rental income, holding costs (PM fees, rates etc.) and the tax benefits of negative gearing. What I was talking about is how much an investor has to pay each month, as in after tax weekly/monthly repayments, after the rent and tax benefits. See the spreadsheet I attached above. It is a 100% increase.
     
  14. euro73

    euro73 Well-Known Member Business Member

    Joined:
    18th Jun, 2015
    Posts:
    6,125
    Location:
    The beautiful Hills District, Sydney Australia
    Yep...I'm just demonstrating holding costs from interest above. The other non interest holding costs have all kinds of variables, depending on rates of inflation for insurance, PM fees, rates, etc.... and of course your personal marginal tax rates, etc. But they would have those variables whether you were on I/O or P&I, anyway, so its not the I/O or P&I thats affecting those costs.

    If for example rental inflation kept pace with the inflation rate of all your non interest costs, your pre tax expenses shouldnt double. The only difference in your pre tax holding costs should be the higher cost of P&I over 22 years, versus the I/O holding costs of the first 8 years. But even that is only true if the rates are identical ie 6% . If rates are lower in 8 years than today, then your P&I repayments may not be 29-37% higher than your I/O repayments . If rates are higher, the increase to your repayments may be far greater than 29-37%.

    And of course, the opposite is also true. If rents dont keep up with the inflation associated with the other costs, you'll see your non interest holding costs increase as well...

    Best case scenario- if rates dont change, and if rental inflation is the same as all other inflation, your pre tax position should only vary by the I/O v P&I differences

    Worst case scenario. rates climb as you revert to P&I , and rental inflation doesnt match other inflation - then you'll have higher pre tax costs for interest expenses and non interest expenses.
     
    Realist35 likes this.
  15. Anthony Brew

    Anthony Brew Well-Known Member

    Joined:
    18th Feb, 2017
    Posts:
    1,176
    Location:
    Australia
    Do you mean $226 and $442 after rental yield is accounted for - meaning out of pocket ?
    If so, yes that is a LOT of money coming out of your salary!
     
    Realist35 likes this.
  16. neK

    neK Well-Known Member

    Joined:
    18th Jun, 2015
    Posts:
    2,844
    Location:
    Sydney
    @Realist35 did you build this spreadsheet yourself?
     
  17. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    That is correct! Which is basically what us investors are interested in - how much will be my out of pocket costs each month, can I handle it and how much buffer I will need to accumulate before IO reverts to I&P.

    For my own circumstances, I will need to accumulate 200k over the first 8 years so I can comfortably hold 2 properties over the next 7 years (15 year hold period which I think is long enough as it should easily be one cycle). I dont need to worry much about my third property as it is on a 15 yr IO loan. And this is if interest rates average 6% over the next 15 years. Hm, maybe I should increase that..
     
  18. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    No, the spreadsheet was built by someone else, I just tweaked it a bit to suit my needs..
     
  19. Pentanol

    Pentanol Well-Known Member

    Joined:
    20th Feb, 2017
    Posts:
    448
    Location:
    Sydney
    How did you manage to get a IO loan for 15 years? Care to share the product? :)
     
  20. Realist35

    Realist35 Well-Known Member

    Joined:
    1st Mar, 2016
    Posts:
    1,652
    Location:
    WA
    Sure, it is CBA wealth package :)
     
    Pentanol likes this.