Try to calculate the max borrowing power for a client using a cell for funds available towards purchase and a max LVR

[totalegazy]

Hi Guys,

I am hoping someone can help me out. Really stumped with this one.

I want to calculate the potential purchase price based off the amount of funds avaialble in Cell E51 and continued all the way up to cell W51 if each year and a max loan to value ratio of 88%, like the example provided above.

If you are not sure what Loan to Value ratio is it is calcuated as the % of the loan balance against the value of the property

This potential price has nothing to do with the property details at the top. This is based on the available equity in the property, how much they could spend on the next property.

I understand this might be a difficult calculation and that is why I require help.

Thanks in advance

 

[totalegazy]

 

[totalegazy]

Sorry the mini sheet didn't want to work for me

 

[totalegazy]

Investment-property-spreadsheet.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
39		Today		Year 1		Year 2		Year 3		Year 4		Year 5		Year 6		Year 7		Year 8		Year 9		Year 10
40
41	Property Value:	$ 639,000.00		$ 677,340.00		$ 717,980.40		$761,059.22		$806,722.78		$855,126.14		$906,433.71		$960,819.74		$1,018,468.92		$1,079,577.05		$1,144,351.68
42
43	Equity In Property:	$ 63,900.00		$ 102,240.00		$ 142,880.40		$185,959.22		$231,622.78		$280,026.14		$331,333.71		$385,719.74		$ 443,368.92		$ 504,477.05		$ 569,251.68
44
45	Property Growth %:	0%		6%		11%		16%		21%		25%		30%		33%		37%		41%		44%
46
47	LVR:	90%		85%		80%		76%		71%		67%		63%		60%		56%		53%		50%
48
49	Loan Balance:	$ 575,100.00		$ 575,100.00		$ 575,100.00		$575,100.00		$575,100.00		$575,100.00		$575,100.00		$575,100.00	Q	$ 575,100.00		$ 575,100.00		$ 575,100.00
50
51	Surplus to reinvest:	-$ 12,780.00		$ 20,959.20		$ 56,722.75		$ 94,632.12		$134,816.04		$177,411.01		$222,561.67		$270,421.37		$ 321,152.65		$ 374,927.81		$ 431,929.48
52
53	Purchase Power:	$ -		$ 188,632.80
54
55	Rental Income:	$ 550.00		$ 28,600.00		$ 29,458.00		$ 30,341.74		$ 31,251.99		$ 32,189.55		$ 33,155.24		$ 34,149.90		$ 35,174.39		$ 36,229.62		$ 37,316.51
56
57	Gross Rental Yield:			4.22%		4.35%		4.23%		4.11%		3.99%		3.66%		3.55%		3.45%		3.36%		3.26%
58	Net Rental Yield:			3.15%		3.06%		3.03%		3.00%		2.96%		2.93%		2.90%		2.86%		2.82%		2.78%
Proposed Analysis

Cell Formulas
Range	Formula
C41	C41	=I15
E41	E41	=(C41*I25)+C41
G41	G41	=(E41*I25)+E41
I41	I41	=(G41*I25)+G41
K41	K41	=(I41*I25)+I41
M41	M41	=(K41*I25)+K41
O41	O41	=(M41*I25)+M41
Q41	Q41	=(O41*I25)+O41
S41	S41	=(Q41*I25)+Q41
U41	U41	=(S41*I25)+S41
W41	W41	=(U41*I25)+U41
C43	C43	=I15-I17
E43,W43,U43,S43,Q43,O43,M43,K43,I43,G43	E43	=E41-E49
E45	E45	=(E41-I15)/E41
G45	G45	=(G41-I15)/G41
I45	I45	=(I41-I15)/I41
K45	K45	=(K41-I15)/K41
M45	M45	=(M41-I15)/M41
O45	O45	=(O41-I15)/O41
Q45	Q45	=(Q41-I15)/Q41
S45	S45	=(S41-I15)/S41
U45	U45	=(U41-I15)/U41
W45	W45	=(W41-I15)/W41
C47	C47	=S17
E47,W47,U47,S47,Q47,O47,M47,K47,I47,G47	E47	=E49/E41
C49	C49	=I17
E49	E49	=IF($I$29="Interest Only Repayments",C49,Sheet4!E19)
G49	G49	=IF($I$29="Interest Only Repayments",E49,Sheet4!E31)
I49	I49	=IF($I$29="Interest Only Repayments",G49,Sheet4!E43)
K49	K49	=IF($I$29="Interest Only Repayments",I49,Sheet4!E55)
M49	M49	=IF($I$29="Interest Only Repayments",K49,Sheet4!E67)
O49	O49	=IF($I$29="Interest Only Repayments",M49,Sheet4!E79)
Q49	Q49	=IF($I$29="Interest Only Repayments",O49,Sheet4!E91)
S49	S49	=IF($I$29="Interest Only Repayments",Q49,Sheet4!E103)
U49	U49	=IF($I$29="Interest Only Repayments",S49,Sheet4!E115)
W49	W49	=IF($I$29="Interest Only Repayments",U49,Sheet4!E127)
C51	C51	=(I15*88%)-I17
E51,W51,U51,S51,Q51,O51,M51,K51,I51,G51	E51	=(E41*88%)-E49
E53	E53	=E51*S17/(1-S17)
C55	C55	=I19
E55	E55	=C55*52
G55	G55	=(E55*S25)+E55
I55	I55	=(G55*S25)+G55
K55	K55	=(I55*S25)+I55
M55	M55	=(K55*S25)+K55
O55	O55	=(M55*S25)+M55
Q55	Q55	=(O55*S25)+O55
S55	S55	=(Q55*S25)+Q55
U55	U55	=(S55*S25)+S55
W55	W55	=(U55*S25)+U55
E57	E57	=(C55*52)/E41
G57,I57,K57,M57	G57	=(G55/E41)
J57,L57,V57,P57,R57	J57	=(J55*52)/J41
O57,Q57,S57,U57,W57	O57	=(O55/O41)
E58,G58	E58	=(E55-E65)/E41
F58,H58,J58,N58,P58,T58,V58	F58	=(F55*52-J27)/F41
I58	I58	=(G55-I65)/G41
K58	K58	=(G55-K65)/G41
L58,R58	L58	=(L55*52-L27)/L41
M58	M58	=(G55-M65)/G41
O58	O58	=(G55-O65)/G41
Q58	Q58	=(G55-Q65)/G41
S58	S58	=(G55-S65)/G41
U58	U58	=(G55-U65)/G41
W58	W58	=(G55-W65)/G41