House price and mortgages

[phillipus2005]

Hi
I am desperately trying to write a formula to calculate the maximum possible property purchase price, based on a range of monthly payment amounts BUT also incorporating stamp duty (a land tax calculated based on the purchase price of the property)…

So, I have:

A/ Deposit amount
B/ interest rate
C/ payment term
D/ monthly payment
E/ mortgage amount =PV(B/12,C*12, D,,1)
X/ stamp duty formula

So theoretically, the max purchase price should be: A+E-X

But the actual stamp duty on (A+E-X) is lower than X (so reversing back into PMT, gives a lower monthly payment than we started with)

How do I make this work? This is driving me nuts

Thanks in advance

 

[awoohaw]

what is the calculation for the Stamp Duty, I do not see it. Is it: X=PP-(A+E)? Or is there are more specific formula?

 

[phillipus2005]

It’s a formula based on thresholds of the property price, but is essentially a function of the property value… the formula I use is:

X= IF(PRICE>SDLT3,((PRICE-SDLT3)*SDLTate3)+FIXEDAMOUNT2+FIXEDAMOUNT1,IF(PRICE>SDLT2,((PRICE-SDLT2)*SDLTRate2)+FIXEDAMOUNT1,IF(PRICE>SDLT1, (PRICE-SDLT1)*SDLTRate1,0)))))

Where PRICE=A+E
SDLT1/2/3 are the threshold amounts
SDLTRate1/2/3 are the percentage tax on the amounts above the threaholds
FIXEDAMOUNTS are the tax paid between each threshold

Your formula would technically be correct but is calculated as above…

Hope helpful and thanks in advance

 

[Dave Patton]

A couple of examples would be useful.
What version of Excel do you use? Please edit your profile.
N.B. You can post an extract of your sheet with the forums tool named XL2BB.

Please provide complete details for all the calculations including thresholds etc. for the Stamp Duty.
Some of use have nearly no knowledge of your Stamp Duty.

 

[phillipus2005]

Hi
Sorry. I am using Excel 365 - i don't see a way to edit my profile but hope this is ok. I also cannot load XL2BB (I get an error: Excel cannot load this file type in protected view) so I have attempted to explain below.

Here are the Stamp duty thresholds and rates:

	C	D	E
4​	Lower threshold	Upper threshold	Rate
5​	£0​	£250,000​	0​
6​	£250,001​	£925,000​	5%​
7​	£925,001​	£1,500,000​	10%​
8​	£1,500,001​		12%​

If the property value is PP, the stamp duty formula I'm using is:

=IF(PP>$D$7,((PP-$D$7)*$E$8)+$F$7+$F$6,IF(PP>$D$6,((PP-$D$6)*$E$7)+$F$6,IF(PP>$D$5,((PP-$D$5)*$E$6),0)))

So by way of examples:

Property price (PP)	Stamp duty (formula above)	Final cost (PP + Stamp Duty)
£500,000​	£12,500​	£512,500​
£825,000​	£28,750​	£853,750​
£1,850,000​	£133,250​	£1,983,250​

If the total amount of money available is: Deposit plus Mortgage Amount (calculated based on monthly payment):

Monthly payment	Mortgage Amount	Deposit	Amount Available
	=PV(4%/12,12*25,-B21,,1)	Constant	=mortgage+deposit
2000​	£380,167.98​	£800,000.00​	£1,180,167.98​
3000​	£570,251.97​	£800,000.00​	£1,370,251.97​
4000​	£760,335.97​	£800,000.00​	£1,560,335.97​
5000​	£950,419.96​	£800,000.00​	£1,750,419.96​

The Amount Available must cover Stamp Duty, so I'm trying to figure out: for each monthly payment amount, what is the maximum Property Price possible...?

I have tried simply calculating the stamp duty and calculating Mortgage+Deposit-Stamp Duty. However the result is a lot lower than it should be, as the Stamp Duty would in reality be calculated on that lower amount, i.e.:

	B	C	D	E	F	G	H
	Monthly PMT	Mortgage	Deposit	Amount Available	Stamp Duty	Assumed Property Price	Actual Stamp Duty
27​		=PV(4%/12,12*25,-B28,,1)	Constant	=Mortgage+Deposit	Formula above	Amount Available-Stamp Duty	Formula above
28​	£2,500.00​	£475,209.98​	£500,000.00​	£975,209.98​	£38,771​	£936,439.03​	£34,894​

Hope this explains it adequately

 

[Dave Patton]

I used Goal Seek to make the value 973,631.21
The bracket information is named with Name Manager aB with value ={250000;925000;1500000} and
the rate differential information is named aR with value ={0.05;0.05;0.02}
Please check the payment calculation and the Stamp Duty calculation.

T202304a.xlsm
	A	B	C	D	E	F
1	Property Price	Stamp Duty
2	938,528.37	35,102.84			The Table is not required
3
4			Est. Duty		Brackets	Rates
5	Deposit amount	464,897.17	0.0360535		0	0%
6	Interest	4%			250,000	5%
7	Term	25			925,000	10%
8	Payment	2,500.00			1,500,000	12%
9	PV	473,631.21
10	Total	938,528.37
11	Stamp Duty	35,102.84	0.037402
12	Total	973,631.21
13
14		973,631.21
3e

Cell Formulas
Range	Formula
B2	B2	=SUM((A2>aB)*(A2-aB)*aR)
B5	B5	=(500000+B9)*(1-C5)-B9
C11	C11	=B11/B10
B9	B9	=PV(B6/12,B7*12,-B8,0,0)
B10	B10	=B5+B9
B11	B11	=SUM((B10>aB)*(B10-aB)*aR)
B12	B12	=B10+B11
B14	B14	=500000+B9

 

[phillipus2005]

Thanks for this. I thought about using goal seek though have never used it before... however, I’m trying to figure out a way to make this dynamic, ie to be able to change the monthly payment (ie what someone is prepared to pay) change the deposit (ie what is saved in cash) and see what price properties they should be looking at…
Possible??

 

[Dave Patton]

" to be able to change the monthly payment (ie what someone is prepared to pay) change the deposit (ie what is saved in cash) and see what price properties they should be looking at…"

You could build a Data Table. See Excel Data Whatif/Analysis Data Table
You could show whatever information you want such as interest sensitivity, payments at various rates, total payments , total interest.
I could post an example but the post with XL2BB will not be a working model.
You could use a drop down to select the type of information that you want to show.

Additional information re initial question
Edit my suggestion if necessary

T202304a.xlsm
	A	B	C
16
17	500,000	12,500.00
18	825,000	28,750.00
19	1,850,000	133,250.00
20
21	PV
22	Your formula with payments in advance
23
24	5,000.00	950,419.96
25
26	I used formula with payments at end of month
27	5,000.00	947,262.41
28	2,500.00	473,631.21
29
3e

Cell Formulas
Range	Formula
B17:B19	B17	=SUM((A17>aB)*(A17-aB)*aR)
B24	B24	=PV(4%/12,12*25,-A24,,1)
B27:B28	B27	=PV(4%/12,12*25,-A27,0,0)

 

[phillipus2005]

Cool. Thanks. Will give it a go

 

[Dave Patton]

The Data Table shown below includes a drop down that provides a choice
See the Drop-Down @ right side of A8
=E4 shows payment amount
=F4 shows total cost
=G4 shows total interest
=H4 shows % interest of Principal
The Table does not need to be this detailed.

Mortgage - Canadian.xlsm
	A	B	C	D	E	F	G	H
1								Data Table Mortgage information
2
3		Rate	Term	Principal	Payment	Total Cost	Total Interest	Int % of Princ
4		6.36%	300	500,000.00	3,333.33	1,000,000.00	500,000.00	100%
5
6	Drop down A8
7	Selection	# Years	10	15	20	25
8	Payment		120	180	240	300
9		2.00%	4,600.67	3,217.54	2,529.42	2,119.27
10		2.25%	4,656.87	3,275.42	2,589.04	2,180.65
11		2.50%	4,713.50	3,333.95	2,649.51	2,243.08
12		2.75%	4,770.55	3,393.11	2,710.83	2,306.55
13		3.00%	4,828.04	3,452.91	2,772.99	2,371.06
14		3.25%	4,885.95	3,513.34	2,835.98	2,436.58
15		3.50%	4,944.29	3,574.41	2,899.80	2,503.12
16		3.75%	5,003.06	3,636.11	2,964.44	2,570.66
17		4.00%	5,062.26	3,698.44	3,029.90	2,639.18
18		4.25%	5,121.88	3,761.39	3,096.17	2,708.69
19		4.50%	5,181.92	3,824.97	3,163.25	2,779.16
20		4.75%	5,242.39	3,889.16	3,231.12	2,850.59
21		5.00%	5,303.28	3,953.97	3,299.78	2,922.95
22		5.25%	5,364.59	4,019.39	3,369.22	2,996.24
23		5.50%	5,426.31	4,085.42	3,439.44	3,070.44
24		5.75%	5,488.46	4,152.05	3,510.42	3,145.53
25		6.00%	5,551.03	4,219.28	3,582.16	3,221.51
26		6.25%	5,614.00	4,287.11	3,654.64	3,298.35
27		6.50%	5,677.40	4,355.54	3,727.87	3,376.04
28		6.75%	5,741.21	4,424.55	3,801.82	3,454.56
29		7.00%	5,805.42	4,494.14	3,876.49	3,533.90
30		7.25%	5,870.05	4,564.31	3,951.88	3,614.03
31		7.50%	5,935.09	4,635.06	4,027.97	3,694.96
32		7.75%	6,000.53	4,706.38	4,104.74	3,776.64
33		8.00%	6,066.38	4,778.26	4,182.20	3,859.08
34		8.25%	6,132.63	4,850.70	4,260.33	3,942.25
35		8.50%	6,199.28	4,923.70	4,339.12	4,026.14
36		8.75%	6,266.34	4,997.24	4,418.55	4,110.72
37		9.00%	6,333.79	5,071.33	4,498.63	4,195.98
38		9.25%	6,401.64	5,145.96	4,579.33	4,281.91
39		9.50%	6,469.88	5,221.12	4,660.66	4,368.48
40		9.75%	6,538.51	5,296.81	4,742.58	4,455.69
41		10.00%	6,607.54	5,373.03	4,825.11	4,543.50
42		10.25%	6,676.95	5,449.75	4,908.22	4,631.92
43		10.50%	6,746.75	5,526.99	4,991.90	4,720.91
44		10.75%	6,816.93	5,604.74	5,076.14	4,810.46
45		11.00%	6,887.50	5,682.98	5,160.94	4,900.57
46		11.25%	6,958.45	5,761.72	5,246.28	4,991.20
47		11.50%	7,029.77	5,840.95	5,332.15	5,082.34
48		11.75%	7,101.47	5,920.66	5,418.54	5,173.99
49		12.00%	7,173.55	6,000.84	5,505.43	5,266.12
50
Data Table

Cell Formulas
Range	Formula
E4	E4	=PMT(B4/12,C4,-D4)
F4	F4	=E4*C4
G4	G4	=F4-D4
H4	H4	=G4/D4
C7:E7	C7	=C8/12
D8	D8	=15*12
E8	E8	=20*12
B8	B8	=CHOOSE(MATCH(A8,{"Payment","Total Cost","Total Interest","Int % of Princ"},0),E4,F4,G4,H4)
C9:F49	C9	=TABLE(C4,B4)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A8	List	=$L$2:$L$5