yet 1 more property holdings spreadsheet

[BV]

Hi

I needed an easy to understand spreadsheet to show my bank manager so I sat down and created 1.

Sheet 1 is showing properties held in my name,
Sheet 2 is showing properties held in my Self Managed Super fund.
Sheet 3 is showing Personal + SMSF properties combined.
Sheet 4 (risk minimisation) was added to make my bank manager feel better.

The spreadsheet is not locked so you can change it to suit your needs.
Feel free to correct any mistakes or to make suggestions

Please note: the property values, incomes, rents and street names are fictitious. As you add more properties you can replace simple addition & subtraction functions with formulae but for this number of IP's it's not necessary.

Enjoy

 

[JoshyBoi]

Thanks BIll!
Always good to see hwo others are doing it =)

 

[Roachy]

Beautiful.....will definitely be using this one. THANKS

 

[gooram]

No worries, but remember that depending on your tax bracket you may have to adjust the % of tax in B37,B40 & B41 for property 1, C37,C40 &C41 for property 2 etc

I noticed you miscalculated the interest when taking into account the offset funds. Should be a '+' not a '-'.

Apart from that... top notch... cheers

 

[BV]

I noticed you miscalculated the interest when taking into account the offset funds. Should be a '+' not a '-'.

Apart from that... top notch... cheers

Thanks Gooram
I've fixed it.
Cheers

 

[gooram]

Also curious about the tax saving... why do you use Tax Duductions * 0.38?

Instead of using Income Tax - Revised Income tax?

 

[BV]

Also curious about the tax saving... why do you use Tax Duductions * 0.38?

Instead of using Income Tax - Revised Income tax?

I did this because the income tax is averaged at 30%.
Wheras the tax deductions are applied to the top portion of your salary so the saving is at your top marginal rate of 38%.

 

[Sheryn]

BV

Practical spreadsheet - and informative makes me think I should buy property in our SMSF with a loan.

Thanks for sharing.



Kind regards
Sheryn

 

[BV]

I've now coloured the input cells green (where we enter our data).
This will make it easier when you enter your income and IP details.

I've also added an extra sheet with a 2009/2010 tax calculator. This does not change the tax savings in our example because we remain in the 38% tax bracket but it will make the spreadsheet accurate for the lower tax brackets as well.

There is no need to fiddle with the tax so I've made the TAX sheet hidden. If in the future there are tax cuts and our tax brackets change or you would like to add more IP's to the spreadsheet you'll need to modify the tax sheet. Don't worry there is no password and you can make the TAX sheet visible again by going to FORMAT/SHEET/UNHIDE and choosing the TAX sheet

 

[BV]

BV
Practical spreadsheet - and informative makes me think I should buy property in our SMSF with a loan.
Kind regards
Sheryn

Hi Sheryn.

I actually started it because I wanted to see what my after tax holding costs were but it also gives me a good picture of my portfolio so I can use it to convince my other half or my bank manager that the next IP I have in mind won't be an issue.

Its scary when you have a couple of mil $ loans but its not so bad when you show them that the properties aren't costing you much to hold.

Regarding property in a SMSF, they are not for everyone but for me its an additional method of investing. I'm at a stage where I don't want any more IP's outside super.

Cheers

 

[FormerLurker]

Nice

Great sheet Bill, however I have 1 recomendation - Add in LL insurance as an expense @ $300/yr

Cheers.

 

[steveadl]

Minor point, but you might want to change the marginal rate from the old 38% rate to the new 37% level.

 

[The_Bludger]

Brilliant! Nice and easy to follow. Thank you.

 

[BV]

Great sheet Bill, however I have 1 recomendation - Add in LL insurance as an expense @ $300/yr

Cheers.

Ok done

Minor point, but you might want to change the marginal rate from the old 38% rate to the new 37% level.

Ok modified it for the 2010/2011 financial year

 

[Bolero]

Hi BV Thank you for this one, will defenetely use it.

 

[gronk007]

Hi BV,

Great spreadsheet. Just wanted to ask something:

The two properties purchased using the SMSF, are the interest rates on them higher because the vehicle used was an SMSF, or just because that was the interest rate cycle at the time.

How are you finding SMSFs as a tool? Scale-wise I think I'm about 5 years away from using my SMSF properly.

Thanks for any feedback

 

[BV]

Hi BV,
The two properties purchased using the SMSF, are the interest rates on them higher because the vehicle used was an SMSF?.

Yes this is actually the standard variable rate.
Our other loans are discounted.

How are you finding SMSFs as a tool?

As with any investment there are positives and negatives.
I started a SMSF because I wanted my super to work harder.

With my 28% deposit and assuming minimum property price increases of 5% pa my SMSF's long term return should be close to 18% pa. If property appreciates by an average of 7% the return will be over 20%

I don't expect to get rich from the SMSF but I see it as another investment vehicle and if things go well it could suplement our pension.
But it's not all about returns.
Our other assets are in my name and if something goes wrong we could lose everything so a SMSF provides us with some form of asset protection.

 

[Rickardo]

Hi BV,

Great spreadsheet, thanks for sharing.

One point I will add, you have labelled cell A51 'Yearly gain or loss" of which the sum is property cash ins minus property cash outs (interest and expenses) plus tax savings. I would not define this figure as Yearly Gain or loss - to me this should be calculated using a Cash Flow Statement.

The incoming side should include:

• Rent
• Tax benefit

The outgoing side should include:

• Cash outlaid towards purchase, renos or improvements
• All other costs paid in cash (whether deductible or not)

By doing this you can work out how much a property is precisely costing you per period and this will give an accurate guide as to yearly gain or loss.

Two examples which may change the yearly gain or loss but where I can't see a function to calculate in your spreadsheet are:

• If you pay deductible expenses from a LOC, they will not cost you any cash except interest, but they will provide you with incoming cash in the form of increased tax deductions against other income.
• If you purchase a capital item and outlay cash in one year, but the item is depreciable over a number of years, the tax saving can be captured under depreciation of plant but where would the cash outlaid be accounted for in yearly gain / loss?

 

[BV]

Hi Rick

Thanks for your input.
Can you give me an example please using some $ figures?

I find it hard to understand the benefit of capturing such expenditure when you haven't done your tax return yet so you don't get an immediate benefit.

cheers

 

[WinstonWolfe]

See if this revision clarifies in any way. I've only done the personal worksheet but easy enough to revise the lot.

Changes made include:
- set out
- fix mistake in tax table.
- delete hidden worksheet for tax calcs
- include performance of PPR.
- option for capital gain contribution
- other minor adjustments.