Forums / Property Investing / Help Needed! / Spreadsheet help. It looks too good, what am I missing?

Viewing 20 posts - 1 through 20 (of 22 total)
  • Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

    Hi all,

    I am hoping for someone to take a look at these figures and see if they seem reasonable and also if I have missed anything off the spreadsheet that I really should be considering.  I would like to have put it in the box below as I usually see it but it didn't transfer as I usually see the spreadsheet but the information is in there none the less.

    All of the figures were based just on a sample property and rough research based on a house at around year 2000 construction. I used BMT for the depreciation shedule and other calculators for the other costs that I didn't know for sure like stamp duty and lenders mortgage insurance.  Without the depreciation schedule the property is negatively geared, its only when I add the estimated minimum from BMT for the depreciation schedule based on my marginal rate of tax at 37% that the property gives me any returns.  

    I am finding a lot of properties that are similar, ie they are negatively geared until the depreciation schedule is added and then they become quite attractive which makes me think I have missed something.  As you can see I have calculated the interest at 5% and I know that I won't get a rate as good as that on a fixed rate but when I enter 5.5%, although it calculates using 5.5% the spreadsheet still shows the figure 6% in the box so I left it as 5% just for the reason of getting it up here without confusion.  

    I appreciate any tips you have.

    House Value   Council rates Land Tax Water Rates Letting Fees Depreciation Shedule Costs Lenders Mortgage Insurance  
    320000                
      Weekly 24.98076923 0.384615385 0 0 0 0  
    Deposit Monthly 108.25 1.666666667 0 0 -255.9166667 0  
    16000 Annual 1299 20 0 0 -3071 0  
    Stamp Duty                  
    10865                
    Lender Mortgage Insurance                
    11187                
    Total cash outlay   Management Fees Landlord Insurance Strata Levvies Building Insurance Renovation Costs Vacancy Factors  
    38052             in Finding new Tennants  
    Loan amount Weekly 28 0 0 0 0 13.46153846  
    304000 Monthly 121.3333333 65 0 0 0 58.33333333  
    LTV % Annual 1456 780 0 0 0 700  
    95                
    Interest rate   Income Mortgage Interest Only Outgoing with Profit / Loss with Property Appreciation Capital Gain  
    5% Weekly 350   IO Mortgage IO Mortgage Initial price 1st year  
    Loan period yrs Monthly 1516.666667 1249.315068 1346.315068 170.3515982 320000 16000  
    30 Annual 18200 14991.78082 16155.78082 2044.219178 1st year 3rd year  
    Yeild           336000 48000  
    5.6875     Mortgage Repayment Outgoing with Profit / Loss with 10 years 6th Year  
    Cash on cash return       Repayment Mortgage Repayment Mortgage 480000 96000  
    47.41989693 Monthly   1631.9519 1728.9519 -212.2852337 Property Appreciation %age per year 10 years  
    Bedrooms Annual   19583.4228 20747.4228 -2547.422804 5 160000  
    3           Yearly profit (CG + Rent) 18044.21918  
                     
                     

    Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

    Ok so that didn't display as I wanted it to.  I'll try again here.  …..

    Its not going well, anyone know how to get it here for display?

    Profile photo of FreckleFreckle
    Blocked
    @freckle
    Join Date: 2012
    Post Count: 1,681
    Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

       Thanks for that. Lets see if this wprks then.  It looks like it didn't allow the formulas to come over as well so it will be simply for looking at rather than playing with I think.

    https://docs.google.com/spreadsheet/ccc?key=0Ar-bWgqS5OBDdHFtZUQ1ck5wSkEtcTRINkh1ejRWMlE&usp=sharing

    Profile photo of Tony FlemingTony Fleming
    Participant
    @the-dark-knight
    Join Date: 2008
    Post Count: 396

    Looks better than the one i use lol :)

    Tony Fleming | Triumphant Property Group
    http://www.triumphantpropertygroup.com.au
    Email Me | Phone Me

    NSW Buyer's Agent specialising in Western Sydney-Blue Mountains-Orange-Albury

    Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

    It has some very pretty colours too in the flesh.. lol   but the imprtant thing I am trying to work out here basically is, am I missing something when I am calculating my cash flow forcast on specific properties.  It seems I can find quite a few using my spreadsheet that are negatively geared until I add the depreciation schedule after which they become positive.  I have heard many many times that although they do exist, cash flow properties are very hard to find so I am sure that with my in-experience I would not find them as easily as I have been which makes me certain that I must have mis calculated something.  I am happy to email it out to people to have it picked through.  

    Someone on here once did that and reconstructed it putting in some great stuff, but the problem with that was that I didn't understand how the figures were calculated so therefore could not verify them and that meant I could only go on trust that they were correct.  I did ask for them to be explained and questioned a couple of other things in the spreadsheet similar but I feel I may have offended the guy by asking at all as I got no response from him after that.

    I see that in the first link above, inflation has been factored in which is something I hadn't thought about and of course, any investment needs to beat inflation at the very least or you are actually losing money.

    Profile photo of Matt_ArnoldMatt_Arnold
    Participant
    @matt_arnold
    Join Date: 2006
    Post Count: 142

    Hi Patterson

    I am a big fan / user of Excel…   but to achieve the above, why not just spend $245 on software that is updated regularly  and does all of the calculations that you're looking to achieve with your spreadsheet (plus a heap more)'

    http://www.somersoft.com.au/piafpu.htm

    Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

    Ok, well that would be great but I fear it still may leave me in the same camp in that I would not understand how the formulas are calculated so I would not be going in armed with anything better than trust in something I don't understand, plus I don't have that cash at the moment either as Im saving a deposit for PPOR.

    Profile photo of BennyBenny
    Moderator
    @benny
    Join Date: 2002
    Post Count: 1,376

    Hi Patterson,

      Well that second attempt was a lot more readable !!  Re "What have you missed", I can say that the "Outgoing with IO Mortgage" seems to have a +ve value, while the "Outgoing with Repayment Mortgage" has a -ve value.  Based on the figures, they should be be one or the other.  

      Also, I see Depreciation showing as $3071 per year – the norm would be to have a large deduction in year 1, followed by lessening values for years 2, 3, etc.   Also, is that figure of $3071 the claimable amount?  If so, this would be a deduction to your Income, and you would get your Marginal Rate of Tax on that amount paid back to you.  

      I note $0 for LMI, but your loan as 95% – thus I think LMI should be included.

      FWIW

    Benny

    Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

    Hi Benny. Thanks for your feedback.  

    The figures you are looking at are the profit and loss figures based on a repayment mortgage and interest only mortgage.  Very feasable for these figures to be on +ve and one -ve as the payments for the two different mortgages would of course be very different. The "outgoing" figures you are referring to are in the column to the left and these are simply the total outgoings based on repayment and interest only rather than any profit or loss.

    The depreciation figures you are looking at are based on my using BMT calculation tool online with a $300k property I think it was built in 2000.  The figure is what I expect I would get back based on the minimum figure they gave me on that property, I took that figure, divided it by 100 then multiplied it by my tax rate which I think is 38%.  ( I am a high earner and a colleague tells me this is my rate)  The initial figure was about $8000 I think.

    The LMI has two places to record it and this is an error on mine.  You have seen an empty box that I had forgotten Ihad in there. LMI is in ther and it is on the left hand side.  I add the LMI, the stamp duty and the deposit together to get total outlay and use that figure against capital gain and profit to give me my cash on cash return figure.

    Profile photo of CatalystCatalyst
    Participant
    @catalyst
    Join Date: 2008
    Post Count: 1,404

    Hi, when calculating whether a property is REALLY cash flow positive I count ALL costs to me. I borrow 105% so calculate that. But even if you haven't borrowed all the money you need to calculate lost earnings.

    i.e. You have paid $38,052 out of your pocket (does that include solicitors costs, building reports etc)? This should all be included. 

    If you had that $38,052 in the bank you would be getting interest on it (say $1500) so you need to deduct that from you "profit".

    Then that gives you a TRUE representation of the change in your circumstances from before and after purchase. And that is what makes the difference. How much better or worse off are you if you make the purchase?

    Some people pay a big deposit then kid themselves that they are CF+.   Numbers don't lie but your interpretation of them can. Good on you for questioning. A lot of people would just go "how great am I" and jump in. LOL

    Profile photo of Tony FlemingTony Fleming
    Participant
    @the-dark-knight
    Join Date: 2008
    Post Count: 396
    Catalyst wrote:
    Hi, when calculating whether a property is REALLY cash flow positive I count ALL costs to me. I borrow 105% so calculate that. But even if you haven't borrowed all the money you need to calculate lost earnings.

    i.e. You have paid $38,052 out of your pocket (does that include solicitors costs, building reports etc)? This should all be included. 

    If you had that $38,052 in the bank you would be getting interest on it (say $1500) so you need to deduct that from you "profit".

    Then that gives you a TRUE representation of the change in your circumstances from before and after purchase. And that is what makes the difference. How much better or worse off are you if you make the purchase?

    Some people pay a big deposit then kid themselves that they are CF+.   Numbers don't lie but your interpretation of them can. Good on you for questioning. A lot of people would just go "how great am I" and jump in. LOL

    Agree 100% with deducting the interest that you would be earning. I always try to and pay the smallest deposit and use LMI(tac deductible and possibly the best way of growing your portfolio in a short period of time)

    Tony Fleming | Triumphant Property Group
    http://www.triumphantpropertygroup.com.au
    Email Me | Phone Me

    NSW Buyer's Agent specialising in Western Sydney-Blue Mountains-Orange-Albury

    Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

    I agree. It does sound like a lot of people are too eager to throw their money away.  I think maybe people forget the end goal is to make money and get blinded by it all.  It's easy to do and I have been guilty of it and will continue to be until I have been doing this a while.  At the moment I have not even invested in a property apart from renting out my UK house when I moved out of it so I can't be too quick to judge.

    Thanks for your reply and input but I do disagree with you regarding deducting what you could have earned from the money if you had put it into the bank.  That is why I calculate all the figures ( that I am aware of ) as a total then work out what the cash on cash return would be.  I then compare that figure with what I could have gotten if I invested the money into my high interests savings account or term deposit account. One thing I have not factored into this spreadsheet is the cost of any borrowing to get the deposit through probable equity release. That has to be factored in too and that is probably why the figures look good at the moment.  Ah, I see what you mean now about the loss of any earnings. You are referring to if I had saved the cash..  You did say but the penny only just dropped. Its a good point that you make.

    Wow cash flow positive properties really must be hard to find.

    Profile photo of Tony FlemingTony Fleming
    Participant
    @the-dark-knight
    Join Date: 2008
    Post Count: 396

    Cash Flow positive properties are easy to find if you target the right area and know the market!!! Low socio economic areas are highly cashflow positive with current interest rates and a lot of these properties have been sitting on the market for months(room to negotiate massively). If you are scared of bad tenants or rent arrears call a local property manager. As long as its not a family business or the property manager has an interest in the company they will tell the truth. I've called about numerous housing commission/bad areas. They tell you straight up bad area/don't do it/stay away or its borderline/good strata managers/postives about it. They don't want you to buy a dud because its more work for them/they have to deal with the tenants/grumpy vendor. Hope this helps

    Tony Fleming | Triumphant Property Group
    http://www.triumphantpropertygroup.com.au
    Email Me | Phone Me

    NSW Buyer's Agent specialising in Western Sydney-Blue Mountains-Orange-Albury

    Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

    It does and I appreciate you taking the time to post.  I am so determined to make a success of this when I am ready to go for it.  A good friend of mine died yesterday at 38 years old and I am a 36 year old FIFO worker. Those two elements alone are enough to drive me to ensure that I get it right, get myself financially independent and start to truly live the life I want to be living, spending quality time with my family whenever we want to rather than missing birthdays etc etc.  I'll make mistakes along the way like I;m sure everyone has but I'll learn from them and get it right with experience.

    Thanks again

    Profile photo of BennyBenny
    Moderator
    @benny
    Join Date: 2002
    Post Count: 1,376

    Hi Patterson,

     

    Quote:
    Wow cash flow positive properties really must be hard to find.

       One of the points often overlooked is that a negative-geared property can give a +ve cashflow – for that very reason (depreciation, borrowing costs, etc leading to Tax Relief). 

       The other thing (again often overlooked) is that fluctuations of Interest Rates when not Fixed can have a place bobbling around – sometimes negative geared, sometimes positive geared.  Now none of this can be quantified ahead of the game, but do make allowances (and have a strategy, even an exit strategy, for when things start to get out-of-hand).  You mention you are on a good wage, which is great.   And investing can work like "enforced savings" toward a goal (and that's also great).  

      But when Rates hike upward (think Election 2007 and the months after) the impact can be huge.    e.g. The media, RBA, etc all talk about a 0.25% increase, or a 0.5% increase.    

      But work it out – if Interest Rates move up a total of 1% from a starting figure of (say) 5% up to 6%, is that a 1% increase in your mortgage repayments (IO of course)?  

      No, it is not – it is a 20% increase. Can your wage cover it?  How long could you keep going if another 1% added another 20% to your costs on top of the first 20%?   Can you raise your rents by 20% (just try it!!!)    So, one to watch when Interest Rates do get moving again.   Most Banks do impose a "Qualifying Rate" (I think that is the name) where they ensure you can handle a 1.5% increase in Rates – and of course, this is way MORE than 1.5% in reality…..

      In short though, keep on spread-sheeting.   That was my "confidence builder" when I was starting out.   The figures didn't lie as long as I covered all of the expected costs.   And future projections can blow your socks off.   You really CAN get wealthy if following the plan, but keep a weather eye out for "storms", and plenty of water under your keel.

    Benny

    Profile photo of waydo77waydo77
    Participant
    @waydo77
    Join Date: 2011
    Post Count: 155

    I am only starting out also but I agree with benny, factor in a 2% or more above variable interest rate and even double your vacancy rate in your calculations – e.g 2%, enter 4%.

    I would almost go as far as to leave out depreciation from the initial calculation and factor that in later, consider it a bonus. Im pretty sure any depreciation also gets taken off your cost base of the property thus increasing the amount of capital gains tax you pay…I think this is right

    so in theory if you depreciate 20,000 from a property that cost 200,000, your cost base would be 180,000, meaning that 20,000 is also now subject to any CGT aswell as any other capital gains made.

    I will factor these in my magical spreadsheet also….

    Profile photo of Paterson00Paterson00
    Member
    @paterson00
    Join Date: 2013
    Post Count: 65

    Both excellent nuggets of advice and reassurance that I am at least heading somewhat in the right direction.  Always nice to know, as you said, Fear is a disease of which knowledge is the cure, Investing is not risky, being uneducated is.  Fear that you are fooling yourself is cured by reassurance from good advisors and mentors so I really do appreciate it.

    Profile photo of FreckleFreckle
    Blocked
    @freckle
    Join Date: 2012
    Post Count: 1,681
    waydo77 wrote:

    so in theory if you depreciate 20,000 from a property that cost 200,000, your cost base would be 180,000, meaning that 20,000 is also now subject to any CGT aswell as any other capital gains made.

    Asset depreciation is on the building only not the land then you have plant and equipment depreciation. In the scheme of things depreciation is peanuts and only a brief bonus of any account. CG is always speculative and while many include it in SS's it should never be more than trend.

    Too many people look too far into the future with SS's. It's crystal ball gazing and a distraction form real events happening now. You can easily lend too much weight to decisions base on what may happen in the future.

    SS's are a what-if tool (and most are poorly designed as well as poorly understood). Beware of what they tell you. You can make them sing a sweet song and they can dupe you when you least expect it.

    Profile photo of shelbyduckshelbyduck
    Participant
    @shelbyduck
    Join Date: 2014
    Post Count: 11

    Hi Paterson00 and Freckle

    Your spreadsheet looks really good.  Would you mind email me a copy?  or where I can find something like this?  

Viewing 20 posts - 1 through 20 (of 22 total)

You must be logged in to reply to this topic.