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

 

[phillipus2005]

Apologies, I can't get this to work - maybe my excel is not up to date but {=TABLE(C4,D4)} returns an error saying: "That function is not valid"

 

[Dave Patton]

"I can't get this to work - maybe my excel is not up to date but {=TABLE(C4,D4)} returns an error saying: "That function is not valid""

The Data Table feature under the menu Data What if Analysis Data Table is a long time part of Excel.
The setup is not completely intuitive. Try searching for videos or help articles.
A condensed version of the instructions
setup a sample of the calculation such as B4:H4
enter the horizontal variables such as C8:F8
enter the vertical variables such as B9:B49
for Payment table enter =E4 in cell B8
use the Data Table feature to create the table Start the feature and enter the C4 and D4

N.B. You do not enter =TABLE(C4,B4) and ignore the Press CTRL+SHIFT+ENTER to enter array formulas. The Data Table feature
records the formulas behind the scene.

 

[Dave Patton]

You could automate or simplify the information using the Goal Seek with VBA.
The example below changes the initial data to the new value shown in D8.

initial

T202304a.xlsm
	A	B	C	D
1	Deposit	84,588.99
2	Interest	4%
3	Term	25
4	Payment	2,500.00
5	PV	473,631.21
6	Total	558,220.20
7	Stamp Duty	15,411.01
8	Total Cash	100,000.00	new amt -->	100,000.00
3e

Cell Formulas
Range	Formula
B5	B5	=PV(B2/12,B3*12,-B4,0,0)
B6	B6	=B1+B5
B7	B7	=SUM((B6>aB)*(B6-aB)*aR)
B8	B8	=B1+B7

run the VBA

T202304a.xlsm
	A	B	C	D
1	Deposit	464,897.16
2	Interest	4%
3	Term	25
4	Payment	2,500.00
5	PV	473,631.21
6	Total	938,528.37
7	Stamp Duty	35,102.84
8	Total Cash	500,000.00	new amt -->	500,000.00
9			see VBA
3e

Cell Formulas
Range	Formula
B5	B5	=PV(B2/12,B3*12,-B4,0,0)
B6	B6	=B1+B5
B7	B7	=SUM((B6>aB)*(B6-aB)*aR)
B8	B8	=B1+B7

Sub GoalSeekStampDuty()

Range("B8").GoalSeek Goal:=Range("D8").Value, ChangingCell:=Range("B1")

End Sub

 

[phillipus2005]

Huge thanks. Will give this a go!!

 

[Dave Patton]

You can inset a button, name it, and attach the VBA. The button does not show on the extract below.

T202304a.xlsm
	A	B	C	D	E
1	Deposit	346,496.88
2	Interest rate	6%
3	Term	25
4	Payment	5,000.00
5	PV	776,034.32
6	Total	1,122,531.20
7	Stamp Duty	53,503.12
8	Total Cash	400,000.00	new amt -->	400,000.00
9	Edit rate, term, payment, and/or cash D8; then click on "Goal Seek"
10
3e

Cell Formulas
Range	Formula
B5	B5	=PV(B2/12,B3*12,-B4,0,0)
B6	B6	=B1+B5
B7	B7	=SUM((B6>aB)*(B6-aB)*aR)
B8	B8	=B1+B7