LTV Maximum Loan

[tvrulesme]

Hi, this should be really simple but it's hurting my head.

I have a known amount of savings (lets say £50,000) and some known LTV percentages from a mortgage company.

70% LTV	Interest Only
75% LTV	Part & Part
85% LTV	Repayment

I would like to know the maximum loan amount based on the above criteria. Have worked this out with a goal seek on a known house value of 100,000 but there must be an easy way to do this so I can work out the maximum house price I should be looking at based on my current savings. Lets assume that the monthly repayments would always be affordable (haha)

Deposit	LTV	How do I calculate this to change Max House Value?	Type	Max House Value
£30,000​	70% LTV	333.33%​	Interest Only	100000​
£25,000​	75% LTV	400.00%​	Part & Part	100000​
£15,000​	85% LTV	666.67%​	Repayment	100000​

Many thanks in advance.

 

[Candyman8019]

Not sure if this totally answers your question, but I've put this sheet together which calculates the entire amortization schedule of the loan based on 30yrs. Enter the downpayment, interest rate and the desired monthly payment. It will calculate your maximum loan amount and also the corresponding LTV.

Book1.xlsm
	A	B	C	D	E	F
1					$277,622.44	Loan
2		Int Rate 5yr	2.54		$50,000.00	Downpayment
3					82%	LTV
4
5		Monthly Pmt	Int	Principle	$227,622.44
6	1	$900.00	$475.20	$424.80	$227,197.64
7	2	$900.00	$474.31	$425.69	$226,771.96
8	3	$900.00	$473.43	$426.57	$226,345.38
9	4	$900.00	$472.53	$427.47	$225,917.92
10	5	$900.00	$471.64	$428.36	$225,489.56
11	6	$900.00	$470.75	$429.25	$225,060.31
12	7	$900.00	$469.85	$430.15	$224,630.16
13	8	$900.00	$468.95	$431.05	$224,199.11
14	9	$900.00	$468.05	$431.95	$223,767.17
15	10	$900.00	$467.15	$432.85	$223,334.32
16	11	$900.00	$466.25	$433.75	$222,900.57
17	12	$900.00	$465.34	$434.66	$222,465.91
Sheet1

Cell Formulas
Range	Formula
E3	E3	=1-(E2/E1)
E5	E5	=+(E1-E2)
C6:C17	C6	=+E5*($C$2/100)/365*30
D6:D17	D6	=+ChangeValue60-C6
E6:E17	E6	=+E5-D6
B7:B17	B7	=+B6

Named Ranges
Name	Refers To	Cells
ChangeValue60	=Sheet1!$B$6	B7, D6:D17
ChangingValue	=Sheet1!$B$6	B7, D6:D17
MaxLoan	=Sheet1!$E$1	E5, E3

NOTE: You will need to copy the last line down so that it has 360 payments. I couldn't paste the entire sheet here due to a character limitation.

You will also need this snippet of VBA added to the applicable worksheet. This will perform a goal seek anytime a value is changed on the sheet.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("E365").GoalSeek Goal:=0, ChangingCell:=Range("E1")
Application.EnableEvents = True
End Sub