Jump to content

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

Link to post

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%))))

Link to post

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

Link to post

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

Link to post
  • 10 months later...
  • 2 weeks later...
  • 2 weeks later...
  • 5 weeks later...

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

Link to post

=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. 

Link to post
  • 1 month later...
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

Link to post
  • 3 weeks later...
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?

Link to post
  • 6 months later...
  • 2 weeks later...
  • 1 year later...

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%
Link to post

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
×
×
  • Create New...