Excel and New Stamp Duty

gjrc

gjrc

gjrc

Soldato
Joined
18 Oct 2002
Posts
6,830
Location
London
hi,

i have been trying to work out a sheet which shows old and new stamp duty. old i can do.

someone has provided me with a formula for the new scheme which is:

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

now this seems to work but i cant for the life of me work out how?!

can someone explain it to me??

thx

g
 
I'm not sure how to explain it other than some psuedocode:

Code: 
TOTAL = 0
IF (F4 > 125000)
    TOTAL = TOTAL + ((F4 - 125000) * 0.02)
IF (F4 > 250000)
    TOTAL = TOTAL + ((F4 - 250000) * 0.03)
IF (F4 > 925000)
    TOTAL = TOTAL + ((F4 - 925000) * 0.05)
IF (F4 > 1500000)
    TOTAL = TOTAL + ((F4 - 1500000) * 0.02)
STAMPDUTY = TOTAL

Does this help?

If not, get more help on 'minus minus' and SUMPRODUCT here:
http://www.k2e.com/tech-update/tips/143-using-two-minus-signs-in-excel
 
Further explanation:

Under the new rules, no tax will be paid on the first £125,000 of a property, followed by 2% on the portion up to £250,000, 5% on the portion between £250,000 and £925,000, 10% on the next bit up to £1.5 million and 12% on everything over that.
Click to expand...
http://www.bbc.co.uk/news/uk-politics-30291460

So what the Excel is saying is:

Take 2% off everything above £125,000
Take 3% off everything above £250,000 (5% minus the 2% we've already taken)
Take 5% off everything above £925,000 (10% minus the 2% we've already taken minus 3% we've already taken)
Take 2% off everything above £1, 500, 000 (12% minus the 2% we've already taken minux the 3% we've already taken minus the 5% we've already taken)

Sum these up and you've got your answer.
 
Let's break the formula down, assume your new house costs £500,000

Code: 
F4>{125000;250000;925000;1500000}
{TRUE;TRUE;FALSE;FALSE}
Will check whether F4 (£500,000) is greater than each of the values in the curly brackets.

Code: 
--({TRUE;TRUE;FALSE;FALSE})
{1;1;0;0}
Minus minus converts them to 0 & 1.

Code: 
(F4-{125000;250000;925000;1500000})
{375,000;250,000;-425,000;-1,000,000}
Again calculate for each value in the curly brackets

Code: 
{0.02;0.03;0.05;0.02}
No calculations here, just constants. Finally you end up with:

Code: 
=SUMPRODUCT({1;1;0;0},{375,000;250,000;-425,000;-1,000,000},{0.02;0.03;0.05;0.02})

Which is equal to:

1*375,000*0.02=7,500
1*250,000*0.03=7,500
0*-425,000*0.05=0
0*-1,000,000*0.02=0

Sum them together and you'll pay £15,000 stamp duty :)


EDIT\\ Excel can present it better than i can:
refVMDo.png
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom