DeniseL

New Member
I have a nested if formula which is supposed to calculate stamp duty on a sliding scale and I just cannot get it to work (must be a super dumb day).

Cells have different numbers based on other calculations but cell B8 = 67,320 and the answer should be 1068.1

The formula is
=IF(BA8<14000,BA8*0.0125,IF(BA8>14000<30000,(BA8-14000)*0.015+175,IF(BA8>30000<80000,(BA8-30000)*0.0175+415,IF(BA8>80000<300000,(BA8-80000)*0.035+1290,IF(BA8>300000<1000000,(BA8-300000)*0.045+8990,IF(BA8>1000000,(BA8-1000000)*0.055+40490))))))

Any help with syntax and/or other errors I have made would be much appreciated.

Thanks, DeniseL

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

DonkeyOte

MrExcel MVP
As a heads up -- there are a couple of issues here

An IF will work along the lines that each test will be executed until such time as test is TRUE at which point IF will stop, eg:

IF(A1=1,1,IF(A1=2,2,IF(A1=3,3,4)))

So for A1=2 I do not need to test that A1 <> 1 as if it did the IF would already have stopped and returned 1 as it was the first test. Make sense ?

Thus it follows a lot of your "tests" aren't required as they've already been checked...

If you did need to do a "double check" (bad term) ie A1 <> 1 and A1 > 2 then you need to use the following syntax

=IF(AND(A1<>1,A1>2),result if true, result if false)

and if it were an OR test

=IF(OR(A1=1,A1=3),result if true, result if false)

Jack Sheet

New Member
Slightly different approach, but you might find this easier to follow:

=MAX(0,BA8-1000000)*0.01+MAX(0,BA8-80000)*0.0275+MAX(0,BA8-30000)*0.0025+MAX(0,BA8-14000)*0.0025+BA8*0.0125

DeniseL

New Member
Thanks lasw10. Forgive me for being a bit slow but I don't really understand how, if I apply what you have said, the formula would determine which range (eg, >14000<30000 or >30000<80000) BA8 would fall within and then apply the stamp duty rate applicable to that particular range.
Thanks again, DeniseL

DeniseL

New Member

Thanks Jack Sheet. Another dumb question. Does using your 'max' formula look at the range (eg, >14000<30000) or does it look at everything <30000?
Thanks again, Denise L

barry houdini

MrExcel MVP
I think Jack's suggestion misses one of the levels [300000]. Here's a similar approach

=BA8*5.5%-MIN(1000000,BA8)*1%-MIN(300000,BA8)*1%-MIN(80000,BA8)*1.75%-MIN(30000,BA8)*0.25%-MIN(14000,BA8)*0.25%

...or here's another way to get the same result

=SUMPRODUCT(--(BA8>{0,14000,30000,80000,300000,1000000}),BA8-{0,14000,30000,80000,300000,1000000},{1.25,0.25,0.25,1.75,1,1})/100

This follows the method outlined here

DeniseL

New Member

Many thanks Barry Houdini. How would I include in your formula the flat rate amount applicable to each range?
Thanks and regards, DeniseL

barry houdini

MrExcel MVP
How would I include in your formula the flat rate amount applicable to each range?

You don't need to, that's already accounted for. For example, in your formula if BA8 = 20000 you are calculating

6000*0.015+175 = 265

Both formulas I suggested will give the same result

Edit: following lasw10's suggested approach your original formula would become

=IF(BA8<14000,BA8*0.0125,IF(BA8<30000,(BA8-14000)*0.015+175,IF(BA8<80000,(BA8-30000)*0.0175+415,IF(BA8<300000,(BA8-80000)*0.035+1290,IF(BA8<1000000,(BA8-300000)*0.045+8990,(BA8-1000000)*0.055+40490)))))

Check the results, all 3 should give the same......

Last edited:

DeniseL

New Member
Barry Houdina - a living legend! Thank you so much.
DeniseL

Jack Sheet

New Member
Good catch Barry, I missed a range. So it should be:

=MAX(0,BA8-1000000)*0.01+MAX(0,BA8-300000)*0.01+MAX(0,BA8-80000)*0.0175+MAX(0,BA8-30000)*0.0025+MAX(0,BA8-14000)*0.0025+BA8*0.0125

DeniseL

My formula looks at each range from 0 up within each MAX() function.

To summarise, the rate table appears to be this:

Tranche--------Marginal rate-------True rate on tranche
14000---------- 0.0125 ---------- 0.0125
30000---------- 0.0025 ----------- 0.015
80000---------- 0.0025 ----------- 0.0175
300000--------- 0.0175 ----------- 0.035
1000000-------- 0.01 ------------- 0.045
Excess--------- 0.01 ------------- 0.055

My solution charges 0.01 on anything over 1000000
Then it adds on a further charge of 0.01 on anything over 300000, INCLUDING the slice over 1000000. So, up to that point it has charged 0.02 on anything over 1000000 (0.01 + 0.01), but only 0.01 on the tranche between 300000 and 1000000.
Then it adds on a further charge of 0.0175 on anything over 80000, INCLUDING the higher two slices, and so on.
By the end, the first 14000 has been charged on just 0.0125, and anything over 1000000 has been charged on 0.055 (being the sum of 0.0125+0.0025+0.0025 etc).

Incidentally, if these rates are likely to change from one year to the next, then you may find it more useful to put the rates into a table and then use the VLOOKUP function or similar to access them in the formula. That would make maintenance a bit easier, and would also make the workbook easier to understand should someone else have to pick it up and run with it.

Last edited:

Replies
10
Views
10K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

1,163,988
Messages
5,834,727
Members
430,314
Latest member
Kym_beginner_in_VBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back