Join our investing community

Rent vs Buy Spreadsheet

Discussion in 'General Investing Discussion' started by broadscott, 9th Mar, 2010.

  1. broadscott

    broadscott Active Member

    Joined:
    15th Oct, 2008
    Posts:
    40
    Location:
    Melbourne
    Hi all,

    After looking for ages for a good rent vs buy spreadsheet for the Australian market, I finally gave up and modified one I downloaded from another website.

    The spreadsheet basically compares buying a PPOR vs investing the theoretical difference between rent and mortgage in shares. I have altered it to include:
    • leveraging your investment in shares
    • re-investing the dividends
    • buying a second PPOR at a point 10 years in the future
    • calculate present values
    Anyway, here it is for you all to enjoy. Please post back any changes/comments you think are required to make the spreadsheet more accurate.
     

    Attached Files:

  2. Johny_come_lately

    Johny_come_lately Well-Known Member

    Joined:
    1st Jul, 2009
    Posts:
    703
    Location:
    SE Queensland
    Thanks broadscott

    for that spreadsheet.:D

    I put in my own values and a house price of $250,000. After trying various tax and leverage values, I was suprised to find renting will better for me than owning a house. Very interesting.



    Johny.
     
  3. broadscott

    broadscott Active Member

    Joined:
    15th Oct, 2008
    Posts:
    40
    Location:
    Melbourne
    I have updated the spreadsheet. I removed the house "upgrade" (which was hardwired in and was a bit clunky). If anyone can think of a better implementation, that would be great (I think to be fair there really needs to be at least one home move in the math).
     

    Attached Files:

  4. broadscott

    broadscott Active Member

    Joined:
    15th Oct, 2008
    Posts:
    40
    Location:
    Melbourne
    Can I ask if anyone has any feedback on the spreadsheet? I would be amazed if I got all the maths correct the first go round ....
     
  5. Simon Hampel

    Simon Hampel Co-founder Staff Member

    Joined:
    9th Jun, 2005
    Posts:
    4,619
    Location:
    Sydney, Australia
    Haven't had a chance to look through it in great detail yet.

    Couple of questions though:

    Cell C4: why do you subtract 40,000 from the deposit?

    Cell C6: what is the relationship between property value and rent?

    Cell C12: is 1% a resonable cost for home ownership? If brand new I guess, but my experience shows costs are typically much higher than this, depending on the age of the property.

    ------------------------------

    Where do you include the cost of interest in the cost of home ownership?
     
  6. broadscott

    broadscott Active Member

    Joined:
    15th Oct, 2008
    Posts:
    40
    Location:
    Melbourne
    C4: $40,000 subtracted for stamp duty (hard coded atm - working on using a formula for next version)

    C6: The difference between the mortgage repayment and the rent is invested into the market portfolio. There is currently no relationship between the value of the house purchased and the amount of rent paid. A good suggestion for a future version!

    C12: I'm not sure having never owned a property - it's just a value I picked up from reading articles on the net
     
  7. broadscott

    broadscott Active Member

    Joined:
    15th Oct, 2008
    Posts:
    40
    Location:
    Melbourne
    Hi all,

    Here is the latest version of me rent vs buy analyser.

    I have fixed up some errors in previous versions, as well as now linking the amount of rent paid to the value of the home you would be living in if you had purchased (though if you prefer to enter a weekly rent it is pretty easy to change). I have also made it more flexible to enter the details of a house move (you enter the year you intend to move and the present value of a house that you would like to move to). It can only handle one move though.

    Can anyone tell me the average return for the property and share markets have been over the last 50 to 30 years? It would be handy info for the spreadsheet.
     

    Attached Files:

  8. Billv

    Billv Getting there

    Joined:
    15th Jul, 2007
    Posts:
    1,796
    Location:
    Sydney, NSW
    Rent Vs Buy?

    There is no comparison IMO because us humans are undisciplined lot and
    we won't stick to a savings program so the best thing for most of us would be to have a mortgage because it's a way of enforced savings.
     
  9. KateMelb

    KateMelb Active Member

    Joined:
    17th Apr, 2010
    Posts:
    33
    Location:
    Melbourne, Victoria
    Billv, not all people are undisciplined. Some people have achieved great success by constructing a sound investment plan and sticking to it. Others perhaps lack the vision and hunger to see things through. For some it might simply be a lack of education or exposure, especially in their late teens and 20s. For others, certain vices get in the way and they just can't make the savings required to get ahead.

    Whatever the case, I truly believe if people want to be successful investors, they can do so by doing wide research and due diligence, using common sense and making sacrifices. It's certainly worked for me!
     
  10. Billv

    Billv Getting there

    Joined:
    15th Jul, 2007
    Posts:
    1,796
    Location:
    Sydney, NSW
    Hi Kate,

    I agree, People don't have to buy investment properties in order to get ahead in life. In fact buying at the wrong time or buying the wrong property can make things worse but property serves a particular purpose (we all need to live somewhere), and with gearing our money is working harder.

    Ofcourse the biggest benefit with buying our own place is that any capital gain is tax free.
     
  11. broadscott

    broadscott Active Member

    Joined:
    15th Oct, 2008
    Posts:
    40
    Location:
    Melbourne
    Thanks for the comments so far guys, but does anyone have any feedback on the calculator?
     
  12. Billv

    Billv Getting there

    Joined:
    15th Jul, 2007
    Posts:
    1,796
    Location:
    Sydney, NSW
    Entering a deposit value in C1 changes the property value in C5.
    Is that meant to happen?

    This can easily be fixed by removing the formula at C5 but what is your thinking behind this? IMO the property value should be an independent entry, should it not?

    Also, adding some text to the spreadsheet would be beneficial.
    I think it needs some explaining as to what it does, where to enter values etc. I'd use different colours for the entry fields.
     
  13. broadscott

    broadscott Active Member

    Joined:
    15th Oct, 2008
    Posts:
    40
    Location:
    Melbourne
    I have done this since for your first home you are likely to borrow up to your maximum limit before penalty (ie. 80% LVR). This can obviously be changed as you suggest.
     
  14. Saskatoon

    Saskatoon Well-Known Member

    Joined:
    17th Oct, 2005
    Posts:
    67
    Location:
    Aldgate, SA
    spreadsheet

    Last year I downloaded this spreadsheet from one of the Aust. forums, but didn't try it. You may be able to compare the assumptions, formulae etc. (I haven't tried yours either!).
     

    Attached Files:

  15. ag sage

    ag sage New Member

    Joined:
    14th Jun, 2011
    Posts:
    1
    Location:
    new york
    You took a short cut when you converted the payment from monthly to weekly. Just dividing by 4 leads to a compounding error over the years.

    The calculation on the D7 of the first home sheet (probably the second home needs fixing too) should be

    =-(PMT(D10/52;D2*52;D4))

    Otherwise a 30 year mortgage is paid off in something like 25 years. Not that there's anything wrong with that in the real world.

    Thanks for the spread sheet btw. Very useful.