Detailed Holding Costs Excel File

[davea]

Hey guys,

just thought id share this with you all.

Runs through holding costs for year 1-15 for IPs.

Majority formula based so use the front page for most of the cals.l If you want to add something particular in go to the IP Calulations Page and put it in under the year and correct spot. It will automatically calculate the value for the following year, so you will need to delete all the cells after that in the row if you want.

PERSONAL USE ONLY. IF YOU WANT TO USE IT IN A BUSINESS/PERSONAL DISTRIBUTION PLEASE PM ME

Any questions/quaries/mistakes pm me but its pretty straight forward

Thanks to MichaelWhyte and Mark C for the initial concepts.

Download here http://www.somersoft.com/forums/showpost.php?p=281864&postcount=16

 

[Prakman]

Hi Dave,

Great work!!

Thanks a heap for this, I can really see what a loss I am making!

 

[buballo]

thanks

To all involved in the setup of the spreadsheet, thanks and well done. It really helps the novices like myself.

Regards,
buballo

 

[WinstonWolfe]

Hi Dave,
Thanks for the massive effort.
A few Qs...

Am I overlooking a depreciation schedule, or are you referring to a manually processed one?

Are your duty tables up to date for all states? If so, that's a big effort in itself.

Thx Again

 

[davea]

Hi Dave,
Thanks for the massive effort.
A few Qs...

Am I overlooking a depreciation schedule, or are you referring to a manually processed one?

No there is no depreciation schedule, so manually entering in one. Im not sure how to value the items correctly (even to give a resonable estimate) i was thinking of putting a building construction estimate then running atleast the capital claim

Maybe if depreciator reads this he could give a general rule that i could input.

Are your duty tables up to date for all states? If so, that's a big effort in itself.

Thx Again

I need to check if they are upto date, if someone gets a wrong answer please let me know (or even a site that has all the duties in one spot). I can write the forumla for it, its just the time to find all the data

 

[TheCamel]

One thing is a bit unclear to me...

In the "years" spreadsheet, for the first year, I entered Owner1 salary at 60K.
But under revised, it changes Owner1 Salary to 56K

Is it supposed to do this. looking at the formula, I can't quite work out how or why.

Can someone explain it to me ?

 

[davea]

One thing is a bit unclear to me...

In the "years" spreadsheet, for the first year, I entered Owner1 salary at 60K.
But under revised, it changes Owner1 Salary to 56K

Is it supposed to do this. looking at the formula, I can't quite work out how or why.

Can someone explain it to me ?

Revised deals with your income after the property loss. (Gross salary minus negative gearing)

If someone completes a full sheet (with real data, mine is all made up), i would love to see it so it gives me a better indication.

PS. This would be good at forcasting purchases made this year, it could be used for already pruchased propertys but the tax rates are going to be out.

 

[TheCamel]

the other thing that might be cool, is for the Owner1/Owner2 income on OriginalInput, to increase... i don't think many people expect their income to stay static for 10+ years...


It's heaps neat though, i'm pluggin all sorts of figures through it now

 

[davea]

that can be done quite easily but again what figures do we use, i can have it increase by inflation

in cell c37 copy this
=(b37*(1+b40))

then copy and paste this formula across to year 15.

c38
=(b38*(1+b40))

if you want it to grow more or less than inflation i can change it if desired. (ive already updated it on mine so i can upload the new one)

 

[ralph wiggum]

Thanks for posting such a brilliant spreadsheet. My question is regarding the loan amounts. In the overview section the 'loan outstanding' part remains the same for the 15 yrs. I assume this is for interest only loans, but what do you do with P&I loans?
Cheers

 

[jingo]

Thanks Davea, This is an excellent spreadsheet.

Regards Jingo

 

[davea]

Thanks for posting such a brilliant spreadsheet. My question is regarding the loan amounts. In the overview section the 'loan outstanding' part remains the same for the 15 yrs. I assume this is for interest only loans, but what do you do with P&I loans?
Cheers

hmmmm yes it was for interest only loans... i have a P&I spreadsheet which is no where as near detailed which can help you with interest charges for each year and you can plug it in to this one.

However i have recieved feedback regarding things i could update a bit more so when i get some spear time i might do a P&I section as well

The other thing would be taxable position if property sold just inital section for once off costs.

 

[davea]

Gday guys, done some more of the updating to it

New File Here

Still havent done the P&I Loan part, as the problem gets with the interest chrage gets a bit difficult, as you need to take the interest weekly (or how ever often the payments are)

if i think of a good way to do it ill post it up. I can do it but i cant keep the filesize reasonable so im still doing some brain thinking...

Once again feedback is welcome.

 

[ChrisOs]

Mortgage Stamp Duty

Hiya Dave,
Just a query regarding the Mortgage Stamp Duty (cell K3 in work sheet Original Input). If I key the formula =Duties!B10 into this cell, which is what I reckon is meant to happen, it creates a circular reference.

Is this a "feature" and we need to key in the amount manually or is this an error?

Great work Dave. Not only is the spreadsheet useful but I am learning more about the features of Excel as well.

Regards,
Chris.

 

[davea]

Hiya Dave,
Just a query regarding the Mortgage Stamp Duty (cell K3 in work sheet Original Input). If I key the formula =Duties!B10 into this cell, which is what I reckon is meant to happen, it creates a circular reference.

Is this a "feature" and we need to key in the amount manually or is this an error?

Great work Dave. Not only is the spreadsheet useful but I am learning more about the features of Excel as well.

Regards,
Chris.

chris, thank you for this pointing out... it something i hadnt realised yet but now makes alot of sense....i think i have figured out a way around it but need to test it abit more... will post back if i have any updates....

 

[davea]

newest version is up again...

this one deals with the mortgage insurance issue... so far im am calculating it on the all costs except mortgage insurance, this should only make a few $$s different so dont worry 2 much....

edit: is there any way to delete the intial link i put in? i cant seem to edit it??

 

[Mr. Fabulous]

Thanks Dave, champion effort mate!

Mark

 

[TheCamel]

I seem to be having great difficulty with version 13...
A lot of things don't seem to update correctly, if at all.

case in point:

If i change the asking value from 300K as default to anything else, the "loan amount" at B16 stays the same. The Total cost stays the same.
If i change the "initial costings" F/G18 etc, it doesn't update the year initial costings, and if i try changing the rent to 500PW, it still only shows 250 on the Years diagram.

Am I doing something wrong ? I've tried macro settings on both low and medium, same result.
And same result between my laptop here, and the workstation at work. Both downloaded at different times, so not using the same Zip.

In actual fact.. it doesn't even ask me about macros when I open it..

 

[davea]

for v13, try hitting F9 when ever you make a change to the cells, its got manual calculations for the numbers, not automatic

v14 i have updated this so it shouldnt be a problem anymore though...

 

[TheCamel]

for v13, try hitting F9 when ever you make a change to the cells, its got manual calculations for the numbers, not automatic

v14 i have updated this so it shouldnt be a problem anymore though...

thanks - i worke out that Alt-F-S is also a workaround, although not wholey appropriate