Microsoft Access - Stamp Duty Calculation

K

KarlT

New member
Local time
Yesterday, 20:19
Joined
Aug 10, 2018
Messages
6
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
 
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
 
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
 
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 >=
 
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
 
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.
 
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
 
You must log in or register to reply here.

Similar threads

D
Solved Why Some Calculated Fields Will Not Work In A Query?
2
Replies
39
Views
1,243
dalski
D
J
New Member
Replies
4
Views
143
The_Doc_Man
The_Doc_Man
T
Display text in calculated query field based on multiple checkboxes
Replies
4
Views
463
timbits35
T
HavingDatabaseRelations
Hello, fellow relational people!
Replies
4
Views
419
The_Doc_Man
The_Doc_Man
R
Current Inventory Calculations
Replies
6
Views
581
tvanstiphout
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom