Jump to content
david thompson

excel formula for new stamp duty

Recommended Posts

Morning folks,

 

Could anyone be kind enough to provide me with a formula to calculate the new stamp duty figures for second homes?

 

My knowledge of excel stops at way before '=SUMPRODUCTS.....'

 

Coiuld you assume the purchase price is A1

 

Many thanks in advance.

 

David

Share this post


Link to post
Share on other sites

Hey David

 

Just take the existing stamp duty calculation and add 0.03*PRICE, like the following:

 

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

 

Hope that helps!

Share this post


Link to post
Share on other sites

Hi,

 

Just thought I would share an equation for anyone that uses numbers, the free Apple equivalent of Excel. Took me a while to find one that worked.

 

= IF(A1>925000, 64000+(A1−925000)×13%, IF(A1>250000, 10000+(A1−250000)×8%, IF(A1>125000, 3750+(A1−125000)×5%, IF(A1>0, (A1)×3%))))

Share this post


Link to post
Share on other sites

Here is something I have created which I think is accurate but it should be check by your solicitor or accountant.  I think I have created the correct algorithms for the step changes at £125k and £250k but someone else may have a set up that is less clunky.  If anyone finds an error in my calculations please let me know and I will take it down (Added to 13.4.16)

BTL SDLT Calculator.xlsx

Share this post


Link to post
Share on other sites

Hi David

 

I'm on numbers (the apple version of excel)

 

IF(B4>1500000, 138750+(B4)×0.15, IF(B4>925000, 64000+(B4−925000)×0.013, IF(B4>250000, 10000+(B4−250000)×0.08, IF(B4>125000, 3750+(B4−125000)×0.05,IF(B4>0,(B4)×0.03,0)))))

 

That formula works for me, but only up to £925,000.

Still need to find a minute to do it up to and over £1,500,000

Share this post


Link to post
Share on other sites

Hello everyone. I'm struggling to work out the formula for commercial leasehold SDLT. I can get the NPV in a seperate cell but cannot for the life of me work out the formula to calculate the SDLT. Any help would be greatly appreciated.


Thanks


Alex

Share this post


Link to post
Share on other sites

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

with g4 as the land/commercial/non-res cost figure. I haven't updated it, but could tweek if over 1500000

 

Alternatively for commercial investments you the yield calculator website. 

Share this post


Link to post
Share on other sites
On 2017-4-27 at 11:08 PM, Adam Sturdy said:

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

with g4 as the land/commercial/non-res cost figure. I haven't updated it, but could tweek if over 1500000

 

Alternatively for commercial investments you the yield calculator website. 

 

Surely it is now just

 

=SUMPRODUCT(--(B4>{150000;250000}), (B4-{150000;250000}), {0.02;0.03})

https://www.gov.uk/stamp-duty-land-tax/nonresidential-and-mixed-use-rates

Share this post


Link to post
Share on other sites
On 2016-4-7 at 4:32 PM, Ashley Sole said:

Hey David

 

Just take the existing stamp duty calculation and add 0.03*PRICE, like the following:

 

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

 

Hope that helps!

 

Ashley, thanks a lot for this. How do I include in the formula for properties under £40,000 houses, there is no stamp duty to pay?

Share this post


Link to post
Share on other sites

Hi there,

Does anybody know how to calculate this for LTT rates in Wales based on the rates shown below?

I have tried the following formula but it's spitting out a calculation way off.

=SUMPRODUCT(--(M2>{180000;250000;500000;750000;1500000}), (M2-{180000;250000;500000;750000;1500000}), {0.035;0.05;0.075;0.1;0.12})

 

Tax Band Normal Rate  
less than £180k 0%  
£180k to £250k 3.5%  
£250k to £400k 5%  
£400k to £750k 7.5%  
£750k to £1.5m 10%  
rest over £1.5m 12%

Share this post


Link to post
Share on other sites
=SUMPRODUCT(--(B4>{180000;250000;400000;750000;1500000}), (B4-{180000;250000;400000;750000;1500000}), {0.035;0.015;0.025;0.25;0.2})=SUMPRODUCT(--(B4>{180000;250000;400000;750000;1500000}), (B4-{180000;250000;400000;750000;1500000}), {0.035;0.015;0.025;0.25;0.2})
 
 
  • Hopefully

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×