UK Stamp Duty Calculator 2019

Hi, does anyone know how to convert an excel formula to a filmmaker calculation? To be honest I'm looking for a calculation for UK Stamp Duty and although there are plenty of excel files to use, I don't know how to do this in Filemaker Pro.

There are two types of calculations depending on if it's your 1st home or 2nd Home. See below.

1st home:

=SUMPRODUCT(--(B3>{125000;250000;925000;1500000}), (B3-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})

2nd home

=SUMPRODUCT(--(B3>{125000;250000;925000;1500000}), (B3-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})+(B3*0.03)

 

Thank you

If you just want a quick calculator, you could do a simple Case() statement along the lines of:

Case (
Price > 1500000 ; ( Price - 1500000 ) * .12 + 93750 ;
Price > 925000 ; ( Price - 925000 ) * .10 + 36250 ;
Price > 250000 ; ( Price - 250000 ) * .05 + 2500 ;
Price > 125000 ; ( Price - 125000 ) * .02 
)

(the above is based on the example here.)

Or, to simulate what your Excel formula does:

Sum (
If ( Price > 125000 ; ( Price - 125000 ) * .02 ) ;
If ( Price > 250000 ; ( Price - 250000 ) * .03 ) ;
If ( Price > 925000 ; ( Price - 925000 ) * .05 ) ;
If ( Price > 1500000 ; ( Price - 1500000 ) * .02 ) 
)

However, it is not good practice to hard-code tax brackets and rates into a calculation formula, as these will change over time. A better solution would store the current brackets and rates in a preferences table or a table of their own, and lookup from there.

Please update your profile to reflect your version and OS, so that we know what you can use.

 

Edited October 12, 20196 yr by comment

Amazing, thanks for your help.

It's not working right for me yet, I'm clearly doing something wrong. But I'm sure with a little play I can get it to work.

P.S. Profile updated.