[bcoz]

UPDATE (2-SEP-09):
The Calculator has been updated to include some format/cosmetic changes to make it easier to follow. It also includes a new Discount Price column for some "what if" comparisons.

DOWNLOAD INSTRUCTIONS:

1. When prompted, select SAVE to save to your PC (do not Open/Run at this stage).
2. Rename the file to "Car Price Calculator-2.xls" (ie. delete the "-REMOVE.DOC" extension)
3. Run/Open the "Car Price Calculator-2.xls" file (using Excel)

Note: This is an Excel file (.xls) in disguise as a Word file (.doc) as this site does not accept .xls uploads (and the compressed file (.zip) does not work).

ORIGINAL TEXT:
As I can rarely work out how some dealers come up with their car quotes, particularly the LCT component, I have written a quick LCT Calculator which has morphed into a Drive-Away Price Calculator.

The calculator is designed to calculate the GST, LCT for either $57180 or $75000 thresholds, and Stamp Duty for NSW (non-NSWers can modify the SD table or just enter the value manually), and accepts input for Car List Price (ex GST), Car Options, Dealer Delivery, LCT Type, Rego and CTP.

Briefly, the LCT Threshold includes the GST, so GST must be added to the car supply price to determine LCT eligibility. If applicable, LCT is then charged on the GST-exclusive portion of the price that exceeds the threshold (ie. the GST is stripped back out before applying LCT so that the GST is not double taxed). I know it sounds a bit convoluted, but for a good explanation refer to the Stratton Finance website...
http://www.strattonfinance.com.au/ca...explained.aspx

The calculator is written in Excel 2007 but saved in Excel 97-2003 format (.xls) for backward compatibility (people using older versions of Excel may find some minor variations in colours/shadings). It has 2 worksheets:

1. Details - for data entry and detailed calculations
2. Summary - may be printed out

As it was written over several hours with only basic testing, it is best considered "beta" grade and may not work correctly in all situations. Therefore the usual disclaimers apply -- it is provided free of charge on an "as is" basis without warranty of any kind, express or implied -- etc, etc.

Feel free to download it and try it out (90 KB unzipped).

EDIT: There was a problem with the .zip file attachment. Although the original .zip file works, the downloaded file gets an error when opening. I tried re-zipping the file using WinRAR but still get the same problem.

As the site does not accept .xls uploads, I have renamed the file and uploaded it as a .doc file. To download the new attachment, please refer to the Instructions above.

Cheers,
John

[bcoz]

Due to a problem with the previous .zip file, the attachment has been changed.

If anybody previously got an error, then try downloading again using the updated instructions in the original post.

[scheps]

nice thanks m8 wonder how i can change it to be used for qld :P

[bcoz]

Originally Posted by scheps

nice thanks m8 wonder how i can change it to be used for qld :P

NSW stamp duty is a tiered rate based on market value (3% up to $45,000 and 5% on the value over $45,000).

As per the Qld OSR website, it looks like Qld duty is based on type of vehicle in addition to value. Also, duty is payable for each $100, or part of $100, of the value.
Hybrid & Electric - $2.00 (~2%)
1 to 4 cylinders - $3.00 (~3%)
5 or 6 cylinders - $3.50 (~3.5%)
7 or more cylinders - $4.00 (~4%)

To automate it properly, you'd have to add another Variable for Vehicle Type and a Table for the rates, and round up the value to the nearest $100.

However, for a quick and dirty solution, just change the Stamp Duty formula (Cell B30) as follows:
=B28*0.035
the above would work for a 6 cyl car and could be out by up to $3.50 (use *0.04 for 8 cyl, etc)

I hope that helps.

[scheps]

hey m8, thanks very much. Amazing!

[systech]

Originally Posted by bcoz

Due to a problem with the previous .zip file, the attachment has been changed.

If anybody previously got an error, then try downloading again using the updated instructions in the original post.

In case it is not abovious the file is not a Word file so you need to rename the file by deleting the .doc extension after downloading. It's an Excel file.

[bcoz]

The Calculator has been updated to include some format/cosmetic changes to make it easier to follow.

It also includes a new Discount Price column for some "what if" comparisons.

eg. for a $100K car, a 5% discount applied to the List Price translates into a 5.9% discount on the Drive-Away Price because of the disproportionate effects of LCT (33%) and NSW Stamp Duty (applied to the GST+LCT inclusive market value).