Excel - "reverse" Loan to Value calculation

bhjm

Hello Everyone,
I know it may sound silly but I am stuck and hit a mental blockage.

I want to find out what is the LTV (Loan to Value)

if I give the percentage and the deposit.

LTV brackets (fixed)
<95
<90
<85
<80
<75
<70
Deposit: variable, so I could change it, and it tells me how much then the property can be worth.i.e. let`s say a house 200.000 and a deposit of 20.000  is a LTV of 90%, if I would increase the deposit to 30.000 the LTV would be 85. How can I calculate if I know the deposit and the LTV, but want to know the Property Value within the brackets. I hope I am clear, if not please ask !

DullGreyGuy

So you want to put 94% LTV in one field (A1) and £60,000 (A2) and then the formula calculate the property is £1m?

=A2/(1-A1)

If you wanted the upper and lower bound so for 94% it'd be 95% and 90%

=A2/(1-floor(A1,0.05))
=A2/(1-ceiling(A1,0.05))

bhjm

hi DGG - thanks for the quick reply.
i just need 1 value, not the floor and ceiling. 

bet yes basically thats what I want. 

bhjm

maybe this helps to understand. 

bhjm

some more thinking about - I would need to calculate the loan amount and then add the deposit, right? how do I calculate the Loan amount ?

outtatune

This any help?

Excel: Use Goal Seek to Calculate Loan Repayment

bhjm

outtatune said:

This any help?

Excel: Use Goal Seek to Calculate Loan Repayment

are you asking if this helps me or are you saying this is the solution for my issue?

Heedtheadvice

That (Goal Seek) is a general built in method in Excel that,given variables and a calculation, will produce the answer to 'what figure do I need to enter to get a desired result.

It is very useful especially when calculation are complex.

Yours is a simple question on the face of it but it could be we worth it to use goal seek to get your answer. ( have not read that specific link contents) but it would probably be worth reading it and applying it to your need.

double_dutchy

bhjm said:

maybe this helps to understand. 

Given the above setup put this formula in G3 and copy down the column:

=100/(100-F3)*G$1

bhjm

Heedtheadvice said:

That (Goal Seek) is a general built in method in Excel that,given variables and a calculation, will produce the answer to 'what figure do I need to enter to get a desired result.

It is very useful especially when calculation are complex.

Yours is a simple question on the face of it but it could be we worth it to use goal seek to get your answer. ( have not read that specific link contents) but it would probably be worth reading it and applying it to your need.

thanks but I am not wiser after your answer. but thanks

bhjm

double_dutchy said:

bhjm said:

maybe this helps to understand. 

Given the above setup put this formula in G3 and copy down the column:

=100/(100-F3)*G$1

muchias gracias ! that`s what I was looking for ! Thank you so much !