UK Property Tax SDLT

[DavidHum]

Hi

SDLT

You have featured the UK Property Tax, SDLT, in earlier posts but the Calculations are for a fixed cost of Property.

Assuming a Cash Pot of £50,000 and a Mortgage of 75% of Value (LTV), except for the SDLT I could buy a £200,000 Property but I need to withold money to pay the SDLT.

Say the SDLT is 3% of the Buy Price, on £200,000 that is £6,000.

But if I reduce the Cash Pot by £6,000 to £44,000, the maximum Buy Price reduces to £176,000 at 75% LTV with a new SDLT liability at 3% or £5,280.

If the difference of £720 is added to the Deposit, I could increase the Buy Price by up to £2,880, less the increasing SDLT

Without using iteration how can I get an absolutely accurate calc? The code will be in an Online Calculator.

Thankyou David Humphreys

 

[FormR]

Hi, something like this (D2 formula) maybe:

Book1
	A	B	C	D
1	Cash	LTV %	SDLT %	Max Purchase Price
2	50000	0.75	0.03	178571.4
3
4	Check
5	Deposit Amount	44642.86
6	SDLT Amount	5357.143
7	Spent	50000
8	Cast Left	0
Sheet1

Cell Formulas
Range	Formula
D2	D2	=A2/(1-(B2-C2))
B5	B5	=D2*(1-B2)
B6	B6	=D2*C2
B7	B7	=SUM(B5:B6)
B8	B8	=A2-B7

 

[DavidHum]

Hi, something like this (D2 formula) maybe:

Book1
	A	B	C	D
1	Cash	LTV %	SDLT %	Max Purchase Price
2	50000	0.75	0.03	178571.4
3
4	Check
5	Deposit Amount	44642.86
6	SDLT Amount	5357.143
7	Spent	50000
8	Cast Left	0
Sheet1

Cell Formulas
Range	Formula
D2	D2	=A2/(1-(B2-C2))
B5	B5	=D2*(1-B2)
B6	B6	=D2*C2
B7	B7	=SUM(B5:B6)
B8	B8	=A2-B7

 

[DavidHum]

Hi, YES, "Something like this works" though I have no idea why!
Thankyou. David Humphreys

 

[FormR]

If you started with your purchase price and you wanted to know what 28% of it was you would multiply it by 0.28 - so it stands to reason that if you started with your cash amount and you knew if was 28% of the purchase price then to calculate the purchase price you would divide the cash amount by 0.28.

 

[DavidHum]

If you started with your purchase price and you wanted to know what 28% of it was you would multiply it by 0.28 - so it stands to reason that if you started with your cash amount and you knew if was 28% of the purchase price then to calculate the purchase price you would divide the cash amount by 0.28.

If you started with your purchase price and you wanted to know what 28% of it was you would multiply it by 0.28 - so it stands to reason that if you started with your cash amount and you knew if was 28% of the purchase price then to calculate the purchase price you would divide the cash amount by 0.28.

Hi FormR

You kindly gave me a formula for calculating SDLT on a reducing Deposit & variable LTV assuming a fixed percentage (3%) but of course SDLT is not fixed but variable depending on the Buy Price, varying from 0% <40,000 to 15% > 1.5 million (2nd Property version).

Attached is an Image of part of the Calculator in Excel with Coding beneath the relevant Cells whichare Rows 29,30,32 Columns D,E,F, which is repeated in Rows 43,44,45.

Rgds David Humphreys

 

[Dave Patton]

Did you review the SDLT calculation on post

SDLT Calculation Excel Formula

Can someone help me to create an excel formula for the updated version of UK SDLT ?

www.mrexcel.com

Please post a clear example complete with expected results.
N.B. You can post a concise example with the forum's tool that is named XL2BB.

 

[Dave Patton]

DavidHum also started a thread at

https://www.mrexcel.com/board/conversations/uk-stamp-duty-variable.143854/#convMessage-158909

Please define your terms and provide a clear post on how the SDLT is calculated.
I used Excel 365 desktop. You stated "The code will be in an Online Calculator." ; I have no information on this part.

You can use the Lambda or a sum formula (see post cited previously) and gross up to the Purchase Price with Goal Seek.
Excel Data What if Goal Seek

With Name Manger,
Name the Lambda formula a user friendly name such as SDLT
Value = the formula part.
The formula prompts for the required information.

N.B. Please ensure that the brackets and rates are correct; review the legal definition and examples. I relied on the information that you provided.
Example 1 Shows the initial calculation. Example 2 Shows the result with a new amount of available cash 100,000.

T240203a (version 1).xlsb
	A	B
2	Purchase price	107,142.86
3	LTV	75%
4		80,357.14
5	Down Payment	25%
6	Cash	26,785.71
7	SDLT	3,214.29
8	Total cash	30,000.00
1c

Cell Formulas
Range	Formula
B4	B4	=B2*B3
B6	B6	=B2*B5
B7	B7	=SDLT_Tax(E3:F6,B2)
B8	B8	=SUM(B6:B7)

Lambda Functions
Name	Formula
SDLT_Tax	=LAMBDA(rngRateData,PurchasePrice,LET(rng,rngRateData,s,PurchasePrice,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))))

T240203a (version 1).xlsb
	A	B	C	D	E	F
1					Brackets	Rates
2	Purchase price	340,909.09			0	0
3	LTV	75%			0	3%
4		255,681.82			250,000	8%
5	Down Payment	25%			925,000	13%
6	Cash	85,227.27			1,500,000	15%
7	SDLT	14,772.73
8	Total cash	100,000.00
9
1c

Cell Formulas
Range	Formula
B4	B4	=B2*B3
B6	B6	=B2*B5
B7	B7	=SDLT_Tax(E3:F6,B2)
B8	B8	=SUM(B6:B7)

Lambda Functions
Name	Formula
SDLT_Tax	=LAMBDA(rngRateData,PurchasePrice,LET(rng,rngRateData,s,PurchasePrice,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))))

 

[Dave Patton]

I changed the rate table. Please edit to the actual rate table per your references.

T240203a (version 1).xlsb
	A	B	C	D	E	F	G
1						Brackets	Rates
2	Purchase price	182,857.14	200,714.29	220,714.29		0	0
3	LTV	75%				40,000	3%
4		137,142.86				250,000	8%
5	Down Payment	25%				925,000	13%
6	Cash	45,714.29	50,178.57	55,178.57		1,500,000	15%
7	SDLT	4,285.71	4,821.43	4,821.43
8	Total cash	50,000.00	55,000.00	60,000.00
1c

Cell Formulas
Range	Formula
B4	B4	=B2*B3
B6	B6	=B2*B5
C6:D6	C6	=C2*$B$5
B7	B7	=SDLT_Tax(F3:G6,B2)
C7	C7	=SDLT_Tax(F2:G6,C2)
D7	D7	=SDLT_Tax(F2:G6,C2)
B8:D8	B8	=SUM(B6:B7)

Lambda Functions
Name	Formula
SDLT_Tax	=LAMBDA(rngRateData,PurchasePrice,LET(rng,rngRateData,s,PurchasePrice,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))))

 

[Dave Patton]

Pending clear information on the brackets and rates

T240203a.xlsm
	A	B	C	D	E	F	G	H
1							Brackets	Rates
2	Purchase price	178,571.43	196,428.57	214,285.71	214,285.71		0	3%
3	LTV	75%					250,000	8%
4							925,000	13%
5	Down Payment	25%					1,500,000	15%
6	Cash	44,642.86	49,107.14	53,571.43	53,571.43
7	SDLT	5,357.14	5,892.86	6,428.57	6,428.57
8	Total cash	50,000.00	55,000.00	60,000.00	60,000.00
9
10					6,428.57
11
12		With a price less than 40000
13	Price	39,999.99	0.00		0.00
14
1c

Cell Formulas
Range	Formula
B6:E6	B6	=B2*$B$5
B7:D7	B7	=SDLT_Tax1($G$2:$H$5,B2)
E7	E7	=SDLT_Tax2(E2)
B8:E8	B8	=SUM(B6:B7)
E10	E10	=LET(s,E2,b,{0,250000,925000,1500000},r,{0.03,0.05,0.05,0.02},IF(s<40000,0,SUM((s>b)*(s-b)*r)))
C13	C13	=SDLT_Tax2(B13)
E13	E13	=LET(s,B13,b,{0,250000,925000,1500000},r,{0.03,0.05,0.05,0.02},IF(s<40000,0,SUM((s>b)*(s-b)*r)))

Lambda Functions
Name	Formula
SDLT_Tax1	=LAMBDA(rngRateData,PurchasePrice,LET(rng,rngRateData,s,PurchasePrice,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))))
SDLT_Tax2	=LAMBDA(PurchasePrice,LET(s,PurchasePrice,b,{0,250000,925000,1500000},r,{0.03,0.05,0.05,0.02},IF(s<40000,0,SUM((s>b)*(s-b)*r))))