Holding Costs Spreadsheet

[Mark C]

As promised attached is a spread sheet I put together a few years ago to demonstrate holding costs on negatively geared property.

I’ve updated it to 2007 tax rates.

Because I have used a macro to work out the tax to run this file you will need to set your macro security to Low. To do this go to Tools / Options / Security / Macro security and set it to Low. Exit Excel and reopen for this change to take effect.

I have also protected the whole spreadsheet from being edited only the yellow tabs can be changed. There is no password and if you want to edit it just turn off the protection. Tools / Protection

Have fun,

Mark

 

[Tizzy]

Thank you Mark that is very generous to share your spread sheet like this. I'm really green with Excel. If I put my own figures in, does the pie diagram change too?

 

[geoffw]

Mark

I'd be interested in seeing what the macro does- often, a formula can do the same. You have password protected the macro though. Is that what you wanted?

Edit: BTW, I prefer to set security to medium. It then askes you, when you open a file which has macro, if you wish to enable the macros in that spreadsheet (or Word Document etc).

 

[geoffw]

Thank you Mark that is very generous to share your spread sheet like this. I'm really green with Excel. If I put my own figures in, does the pie diagram change too?

Try it. Yes it does.

 

[Norwester]

Mark,

That is really good of you to share that.

I'll probably never meet you but I owe you some beers.

I have been trying to find a way to demonstrate to Mrs Nor an answer to her never ceasing question *what's it gunna cost and where's the benefit* The pages of chicken scratches just weren't quite cutting it I'm afraid.

I've just showed her this and she's like *wow look at that*.........and *well when are we buying the next one*. Now I'm scared.............

Of course she now just wants to kick me off the 'puter and start playing with it herself.................


ciao

Nor

 

[sailor]

Love the spreadsheet....thanks Mark. I'm having fun using it.

 

[coolie21]

but entering figures brings up a compile error, and some cells (like rental) are locked....

 

[Tizzy]

Thats really good!! Just need to figure out the tax bit next, but I'm going to get one of the kids to work that out. Tis now saved to desktop.

 

[WinstonWolfe]

Hi Mark,

I'd be interested to see the calcs behind your macro too. Here's how I'd stick 2007 into a formula and do away with a macro

=
IF(F12>150000,47850+(F12-150000)*0.45,
IF(F12>75000,17850+(F12-75000)*0.4,
IF(F12>25000,2850+(F12-25000)*0.3,
IF(F12>6000,(F12-6000)*0.15,
IF(F12<6000,0)))))

for 150,000 the above gives tax of 47,850

which ignores 1.5% medicare levy and 1% surcharge though there are so many variables on these, one may as well round off by adding something like

+IF(F12>16000,F12*0.015,0)+IF(F12>50000,F12*0.01,0)


http://www.ato.gov.au/individuals/content.asp?doc=/content/12333.htm&mnu=5053&mfp=001

 

[jingo]

Hi Mark,

Thanks for this sreadsheet. It is generous of you to put it on the site for us to use.

Regards Jason.

 

[geoffw]

Hi Mark,

I'd be interested to see the calcs behind your macro too. Here's how I'd stick 2007 into a formula and do away with a macro

=
IF(F12>150000,47850+(F12-150000)*0.45,
IF(F12>75000,17850+(F12-75000)*0.4,
IF(F12>25000,2850+(F12-25000)*0.3,
IF(F12>6000,(F12-6000)*0.15,
IF(F12<6000,0)))))

for 150,000 the above gives tax of 47,850

which ignores 1.5% medicare levy and 1% surcharge though there are so many variables on these, one may as well round off by adding something like

+IF(D80>16000,F12*0.015,0)+IF(F12>50000,D80*0.01,0)


http://www.ato.gov.au/individuals/content.asp?doc=/content/12333.htm&mnu=5053&mfp=001

Thanks Winston.

While I love VBA and what it can do, its use in disseminating information can be limited as a result of virus problems in the past.

The first problem I ever encountered in a Word document macro was in a document sent to a government department from a department secretary. (That was the virus Microsoft labelled as the "concept" virus". Cunning). So much for trusted sources.

So for a simple solution I would rather a complex and hard to debug formula rather than an easy VBA program.

I've used macros extensively in an intranet. And I find them fantastic in my business situation.

But I cannot really use them in a general internet environment. A pity.

 

[davea]

hmmm i have one question, how did you put the print preview button in?

but great worksheet

 

[danel]

Cheers mark! We can really use this!!!!!!

 

[luce.rocks]

I'm going to feel like a real loser if this is a silly question but, how come the cash out and cash in cells are both positive values?

 

[GeeVee]

Thanks

Hi Mark,
Thanks for the spreadsheet. I will look forward to playing with it. Seems easy enough.

 

[arg821]

So for a simple solution I would rather a complex and hard to debug formula rather than an easy VBA program.

Does anyone have the knowledge to replace the macros with formulas?

Andrew.

 

[Mark C]

Thanks for the nice comments you should see the one I have on land subdivision (sorry that one's not a giveaway or for sale).

For those of you who have had some problems with the spread sheet, reread the instructions and make sure that you use a negative value when typing in the loan amount.

There are about 100+ ways of doing the same calculation in excel, some are better than others. I use what I'm most comfortable with, feel free to modify and post updates of this spreadsheet as you see fit.

Cheers,

Mark

 

[geoffw]

Mark

I would like to modify the tax stuff if I could please- can you post a copy with the VBA unlocked? Or post a password?

Thanks

 

[luce.rocks]

For those of you who have had some problems with the spread sheet, reread the instructions and make sure that you use a negative value when typing in the loan amount.

ah-ha! that's what i was doing wrong

 

[davea]

Does anyone have the knowledge to replace the macros with formulas?

Andrew.

yes i have done this already.

I hope mark doesnt mind but i have taken his concept (off the graph and the set out) and worked it into a much more detailed spreadsheet, this will cover holding costs for 15 years, with all costs increased by either capital growth or inflation. (got this idea from MUH in the spreadsheet thread)

however due to the heap of work invloved i still have about 2 more days before i finish the spreadsheet to my satisfactory level.

Once im finished i will post up a copy for individual use by members.

My hope is to finish it by thursday night.