Hi, all. I am in the midst of setting up a spreadsheet for myself to calculate payments etc for a home loan. I wish to include stampduty and LMI ( if needed ). At present I can only calculate NT stamp duty as the formula was pretty easy. All other states are almost a nightmare.
Has anyone accomplished anything like this before? I know stamp duty etc is available on the net, but I want to incorporate all into my spreadsheet, so at the end of the day, I know how much I need to come up with for a property, and what the repayments are.
I am using the PMT function in Excel, and believe I will soon need to use IF function sooner or later.
Hopefully someone a bit more brainy than me can enlighten me.
Cheers
“Looking forward to the day when I can tell the boss where to go”
Evenin Myydral
I am using the PMT function in Excel, and believe I will soon need to use IF function sooner or later.I tried this a while back but the limitations with excel is that you can only use up to 7 “if” functions before the program has cittens. Unfortunately, from what little I understand you’d have to set up Macros and Uno’s and Thingo’s that I haven’t had the time to master as yet. I know you can ‘record’ a macro – where yuo click on the “record” button and do what you want that macros to do (ie: insert a row, then to tal all entries in a column, then colour the top cell pink, etc) and once it is saved all you ave to do is then click on that macros button (which you can rename to something fancy) and whatever you recorded will be done before your eyes in a fraction of a second.
Sorry that’s all I can give you. The rest you’ll have to get some one else to show you, or you buy someones spreadsheet! Could be tax deductible – or an income spinner for you!
Cheers
C@30
Hi Mydral,
Easier way is to add around 5% to the purchase price and you’ll cover all purchase and borrowing costs.
Why worry about the small stuff?
Derek
[email protected]Property Investment Support Available. Ongoing and never stopping. PM welcome.
Thanks to one member I now have the WA rates in the spreadsheet, but looking at the formula and the other states formulas for calculating stamp duty – could be a while before this monstrosity works out.
And Rob, with the spare time I have on my hands for the next 4 months, monitoring rates will not be hard
Cheers
“Looking forward to the day when I can tell the boss where to go”
Okay for those that are interested in my project this time of the morning ( it’s 0530 where I am [sleepyanim]) I have now got three states’ stampduty working on the spreadsheet – WA, SA and NT.
I shall keep you updated – after Stamp Duty comes LMI – jeez can’t make it easy for myself can I?
Cheers, be back tonight again ( back at work [whistle] )
“Looking forward to the day when I can tell the boss where to go”
Hi
You need to start delving into VBA. Load up Excel, hit Alt-F11 and get scared
Seriously though this lets you write a custom function (say, called stampduty that accepts 2 parameters, the state and the property amount.). It spits out a number with the stampduty value.
You can then call this from within an excel cell and the parameters can be pased as the raw numbers or cell references.
You will find that at a certain point its simpler to hit the learning curve of vba that struggle with complex vlookups and nested if’s..
Next time I have the ez-rent source open, I’ll see if my stamp duty function works in excel. If it does then I’ll post the code to my website.. I have to modify it anyway to deal with the WA govt changes.
Paul
[email protected]EZ-Rent. The free tax and cashflow simulator for Australian property investors. Version 2 out now!
http://www.ez-rent.comI have a fairly tricky excel spreadsheet for a game I played that I made early last year (with the bulk of the tricky stuff done by a friend). To get about the multiple IFs the CHOOSE and INDEX commands were used. You could probably nest IF statements within that to get around large selections.
The simplest way would be to just have the person type in the value of the property and have a heading for each state and have each state calculate the values for you. All you have to do is then look under the relevant state then… its messy but it gets the sheet to do what you want. You can always ‘pretty it up’ later.
Thanks for that, is there a rough estimate ie 5% or similar, that I could use?
“Looking forward to the day when I can tell the boss where to go”
Hi all, after using the IFs just for the NT, I think I may take sizzling_duck’s ( luv the name ) advice and use a table for all states.
I am not a “formula guru” in excel. Working out the three IFs for the NT hurt my head enough [confused2].
A table it is then
“Looking forward to the day when I can tell the boss where to go”
Myydral
I have a detailed investment property spreadsheet that may help with formula setup. Im not sure about the accuracy of the stamp duty info as some of the rates have changed in the last few years (although, you can alter them on the “Data” sheet). If you want a copy to play around with email me at [email protected] and write “IP Calculator” in the subject bar.
Good Luck
MarkAfter a fairly late night last night, I finally got all states to work correctly. All except ACT on a price of $500,000 – go figure. Thanks to Mortgage Advisor and all other’s for the help.
Next task is a rough LMI price.[blink]
Thanks Mark, an email is on the way. Being not very experienced with excel nuances ( ooh big word ) I would like to see how I went.
“Looking forward to the day when I can tell the boss where to go”
You must be logged in to reply to this topic. If you don't have an account, you can register here.
