max. house price calculator

[Schoschli]

Hi There

I m struggling to make an excel that solves following problem for me:

I want to show me the max house price someone can afford based on the parameters that can be changed.

The function is as followed:

a/3= ((0.8x-2/3x):15)+0.01x+((x-y)*0.045)

a stands for his income and is one of the dynamic fields
y stands for the funds someone brings and is also one of the dynamic
x here is the price of the house and is the real variable here that should get automatically caluclated based on a and y which are given

I'd much appreciate any input on how i can put this together, so it stays dynamic.

Cheers
Joël

[alansidman]

Please read the yellow banner at the top of this page on how to attach a file.

[maniacb]

In addition to Alan's request, What does ":15" mean mathematically?

[PKowalik]

The ":" stands for division in some countries. In Excel it is "/".

Schoschli, 2/3x stands for 2/3 multiplied by x or 2 divided by 3x?

If 2/3x is 2/3*x, then it is a linear equation with the variable x and parameters a and y.
I solved it for x and the result is

x=(75a+10.125y)/14.375

Your problem is not really an Excel-related problem but just a mathematical problem.

[Schoschli]

yes : is / im sorry i forgot :D

It's not a mathematical problem. i wonder how i can make it automatically solve by x by using the underlying parameters which are not static. I have no experiece with the solver function, but i know basic VBA. So i wondered if it was possible to have both the dynamical aspect of what im looking for and also the autosolver by x and how i can make it as efficiently as possible.

Thanks for your input.

[AliGW]

Well, it IS a mathematical problem!!!

Try SOLVER: https://support.microsoft.com/en-us/...b-f63e45925040

[PKowalik]

Schoschli, in what way did I misunderstand you?
The phrase "x here is the price of the house and is the real variable here that should get automatically calculated based on a and y which are given" suggests you want to perform some mortage-related calculations.
So, there is a customer with income a and some savings in amount y. You want to calculate his/her "loan ability" (not sure if it is a correct name, I translated it literally from Polish) expressed as the maximal price of a house he/she can afford. Your assumption is (I guess) that the (maximal) payment is 1/3 of income a. The value of a single payment depends (except from interest rates, other costs and the number of payments) on the amount borrowed which is the price of the house x minus savings y. My formula calculates what is the value of x if the customer has savings y and pays 1/3 of his/her income a.
Put the value of a in A1 and the value of y in A2. In A3 type =(75*A1+10.125*A2)/14.375. A3 will return the value of x you need. Then, as you change the numbers in A1 and A2, you will obtain dynamically adjusted values of x depending on a and y (and, of course, fixed parameters 0.8, 2/3, 15, 0.01 and 0.045).

[davsth]

It seems reasonable you can borrow 5 times your income +70% of your deposit . Although the 70% of deposit seems a bit strange it is not 100% of the deposit as you already have the money.


income 10000 deposit 200000 suggests the maximum is 193043 which is less than the deposit! But that is the formula not the derivation to find x

[PKowalik]

I agree that the result of my formula looks somehow strange. I made the transformation of the original equation late in the night and I could make a mistake. However, I made two test calculations and I put the results as x to the original equation and it was satisfies in both cases.

[davsth]

I quite agree with you, my transformation of the formula was the same as yours. It could be amount to lend and the deposit is added as an extra value. but as you say it seems a bit strange!