Formula for Calculating Car Stamp Duty

[nathanlacey]

I am trying to find out an excel formula that will automatically calculate car stamp duty for NSW.

Stamp duty is 3% up to 45,000 and 5% above that. It is calculated on the next $100 though. So if it is $23,204 then the stamp duty is actually calculated on $23300.

Can anyone help me with this?

 

[BadgerRoad]

If your car value is in cell A1 then the following:

=MIN(ROUNDUP(A1/100,0)*100,45000)*0.03+MAX(0,ROUNDUP(A1/100,0)*100-45000)*0.05

 

[BadgerRoad]

If your car value is in cell A1 then the following:

=MIN(ROUNDUP(A1/100,0)*100,45000)*0.03+MAX(0,ROUNDUP(A1/100,0)*100-45000)*0.05

 

[dermie_72]

See if this works.

=IF(ROUNDUP((A1/100),0)*100<45000,(ROUNDUP((A1/100),0)*100)*0.03,(45000*0.03)+(((ROUNDUP((A1/100),0)*100)-45000)*0.05))

Forgot to round up to the nearest hundred across the formula.

 

[nathanlacey]

Thanks guys. Very helpful.