calculating formula with percentage of total

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

Im trying to make a formula that calculates a housing tax.

If the purchase price is:

Up to £125,000 - 3%
£125,001 - £250,000 - 5%
£250,001 - £925,000 - 8%
£925,001 - £1.5m - 13%
over £1.5m - 15%

However it is calculated  by first applying 3% to the first 125,000, 2% the next 125,000, then another 5% to the next range etc. 

Sometimes a house will be split into more than 1 unit so the calculation is based on the purchase price of the whole building divided by the no of units and then totalled up again at the end.

I tried something along these lines but I guess my syntax is wrong.

var Number sdcalc = [Purchase Price] / [#Units] ;

if ($sdcalc < 125000, $sdcalc * 0.03, $sdcalc,

$sdcalc > 125000, $sdcalc * 0.02, $sdcalc,

$sdcalc > 250000, $sdcalc * 0.02, $sdcalc,

$sdcalc > 925000, $sdcalc * 0.05, $sdcalc,

$sdcalc > 15000000, $sdcalc * 0.02, $sdcalc);

When I get the sdcalc price here i will just then multiply it again by no of units to get the full tax.

Any ideas?

Photo of Michael Graham | Insight Global UK

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 630 Points 500 badge 2x thumb
Try this formula:

var Number sdcalc = [Purchase Price] / [#Units] ;

var Number TaxTierAmountOne = If ($sdcalc <= 125000, $sdcalc, 125000);

var Number TaxTierAmountTwo = If ($sdcalc <= 125000, 0, If ( $sdcalc <= 250000, $sdcalc - 125000, 250000 - 125000) );

var Number TaxTierAmountThree = If ($sdcalc <= 250000, 0, If ( $sdcalc <= 925000, $sdcalc - 250000, 925000 - 250000) );

var Number TaxTierAmountFour = If ($sdcalc <= 925000, 0, If ( $sdcalc <= 1500000, $sdcalc - 925000, 1500000 - 925000) );

var Number TaxTierAmountFive = If ($sdcalc <= 1500000, 0,  $sdcalc - 1500000);

var Number TaxForOneUnit


$TaxTierAmountOne * 0.03

+ $TaxTierAmountTwo * 0.05

+ $TaxTierAmountThree * 0.08

+ $TaxTierAmountFour * 0.13

+ $TaxTierAmountFive * 0.15;

var Number TotalTax = $TaxForOneUnit * [#Units];


$TotalTax


====

We first calculate sdcalc the same you did, with the [Purchase Price] divided by [#Units].

Then we calculate the amount of sdcalc that falls within each tax tier.  If sdcalc is below the lower limit of that tier, then it returns 0 (i.e. there is no amount in that tier), otherwise, it'd be somewhere between the lower and upper limits of that tier.  I put in amounts such as "250000 - 125000" to illustrate the upper and lower limits of that tier.

Next, in TaxForOneUnit, we multiply the amount in each tax tier by the corresponding tax rate.  I'm assuming that the tax rate applies for the entire amount in that tax tier.  i.e. 8% applies for the entire amount of sdcalc between £250,001 - £925,000.

Finally, TotalTax is simply TaxForOneUnit multiplied by [#Units].
Thanks Xavier, that works perfectly!!
Photo of Xavier Fan

Xavier Fan, Champion

  • 630 Points 500 badge 2x thumb
Great!