SUMPRODUCT: Property tax calculations

Try

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

in Name Manager

Name: Tier_Calc

Refers to:

=LAMBDA(amount,tier,mRate,SUMPRODUCT((amount>=tier)*(amount-tier),mRate))

It could be done using VBA where the calculation is done in the VBA code and it simply returns the desired result.

It cannot be done via a formula.

This is similar to the personal income tax (PIT) in my country; it's a progressive tax with different brackets.
Since these brackets remain fixed for a long time and rarely change, we can hardcode the amounts for each threshold.
For ex, with simple If:

1st home

Please Login or Register  to view this content.

Simila to 2nd home

Please Login or Register  to view this content.

Please Login or Register  to view this content.

=PurTax(pPrice,1)

where pPrice is named range and "1" is for 1st home: change to "2" for 2nd home

The calculation can be further automated to calculate the taxes (Stamp Duty) when the Property Price is entered/changed.

Please Login or Register  to view this content.

See attached: change value in B4 and see Stamp Duty in B5:B6

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.