Microsoft Access - Stamp Duty Calculation (1 Viewer)

KarlT · Aug 10, 2018

Hi there,
I am new to access and I am trying to build a calculator for when I am buying a buy to let property. I am trying to find the query for second property Stamp duty and failing miserably at every turn.
I have found one for excel but it make no sense to me at all and do no have a clue on how to translate it into access even if it is possible.

this is what it looks like in excel:
=SUMPRODUCT(--(B4>{125000;250000;925000;1500000}), (B4-{125000;250000;925000;1500000}), {0.02;0.03;0.05;0.02})+(B4*IF(B4>39999,0.03,0))

If you are good enough to answer or help please be gentle as I really do not have a clue and I am in at the deep end with little knowledge and understanding of terminology. Thank you in advance

CJ_London · Aug 10, 2018

looks like this is UK stamp duty to me. So the formula needs to calculate incremental stamp duty and then add 3% for non principle private residence (i.e. second home, buy to let)

first you need stamp duty tables -

tblDuty
BandBottom…BandTop...Rate
125000...……..250000...…...2%
250000...……...925000..…...3%
925000...……...1500000.....5%
1500000...……..null.........….2%

tblnonPPR
nonPPRBottom…nonPPRTop...nonPPRRate
40000...……......….Null...…….....….3%

to calculate the duty, your query will then be

Code:

PARAMETERS [Enter House Price] Long;
SELECT [Enter House Price] AS HousePrice, Sum((IIf([Enter House Price]>Nz([BandTop],[Enter House Price]),[BandTop],[Enter House Price])-[bandbottom])*[rate])+Avg((IIf([Enter House Price]>Nz([nonPPRTop],[Enter House Price]),[nonPPRTop],[Enter House Price])-[nonPPRbottom])*[nonPPRrate]) AS DutyDue
FROM tblDuty, tblnonPPR
WHERE (((tblDuty.BandBottom)<[Enter House Price]))
GROUP BY [Enter House Price]

Create the tables exactly as above and then copy/paste the sql to a new query

KarlT · Aug 10, 2018

You have got it about the stamp duty...... bang on. would this formula work for this? example at the bottom of the of the stamp duty tax breaks.
If I purchase a second property I need to calculate the stamp duty.
£0-£39999 is 0% of purchase price in Stamp duty
£40000 - £124999 is 3% of purchase price in Stamp duty
£125000-249999 is 5% of purchase price in Stamp duty
£250000-924999 is 8% of purchase price in Stamp duty
£925000-£1499999 is 13% of purchase price in Stamp duty
£1.5 over is 15% or Purchase price in stamp duty

so if I am to purchase a property at say £150000 the calculation is 125000 * 3% (3500) + 25000 @ 5% (1250) so the total stamp duty to be paid is £5000

I hope this makes sense? thank you again

CJ_London · Aug 10, 2018

don't see why not, keeps it in one table - just doesn't follow the calculation banding shown at gov.uk

But you need to make sure you have your break points correct - you are missing £1 between the top of one band and the bottom of the next. Since the smallest coin is 1p you will need to use £124999.99 and perhaps need to change the formula from using > to >=

KarlT · Aug 10, 2018

Ahhhh, yes you are right about the banding........ this is not going to be easy if I cant even get that bit right....lol.
Again thank you. I thought it may have been a little more simple. I have created a some tables already but I am sorry I do not fully understand the tables and code.. I think my friend may, I will ask him to help formulate it into my calculator. Again thank you

Ranman256 · Aug 10, 2018

Use a rate table,and a query.

KarlT · Aug 10, 2018

CJ_London, I think I might be getting a nit of this into my tied thick skull. What code needs changing if I am to use only the one table?
thank you in advance......feeling like I might get this.

CJ_London · Aug 10, 2018

the code I provided uses both tables

KarlT · Aug 11, 2018

Thank you CJ_London.... I am very new to this and this might actually be beyond me... so sorry for the lack of understanding. First question - how do I get Null? and the second, Can I not have it all in one table and if I do does the code still work unchanged? Again thank you very much, you are helping a very tortured head right now

CJ_London · Aug 11, 2018

you have cross posted here

http://www.accessforums.net/showthread.php?t=73283

and been advised of the etiquette. You have answers on the other thread, so suggest you continue on the other forum.

Microsoft Access - Stamp Duty Calculation (1 Viewer)

KarlT

New member

CJ_London

Super Moderator

KarlT

New member

CJ_London

Super Moderator

KarlT

New member

Ranman256

Well-known member

KarlT

New member

CJ_London

Super Moderator

KarlT

New member

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread