Excel: how to illustrate a budget constraint that depends on more than two factors in a table? Calculate how much of a house you can afford

Question

Imagine that you want to make calculation of how expensive flat/house you can afford to buy. You know how much you can spend on housing per month (100). You also know that there are three factors that affect the cost of housing:

1. Price = size of the loan
2. Interest rate. The interest is not fixed and you want to experiment with different levels "what happens if the interest increases from 2% to 4,5%?"
3. The cost of running the house. Varies between objects.

The whole situations is a little "swings and roundabouts". If one flat/house has high running costs, especially 1 needs to be lower. If you expect the interest to increase, again 1 (and 3) must be lower, etc.

I want to setup a table in Excel where I can find answer to questions like

• "If 3 is 20, my loan is 1000, what is the maximum interest rate if I want the whole cost to be lower than 100?" (8% in this case)

• "If I borrow 1500 and expect the interest to increase to 6%, what is the max running cost I can afford?" (10)

• "If the interest rate is 5%, how does the relationship between mortgage and running cost look?" (If the running cost is 100, you can't afford any mortgage, if the running cost is 60, you can borrow 800, if there is no running cost, you can borrow 2000).

As you understand, I am looking for a way to use at least three dimensions in one table to see which combinations of the three factors described above that are possible when you have a certain budget constraint.

It is easy to make a table over, e.g., price/interest rate or running cost/interest payments

but none of these tables cover all three dimensions. And that is my question:

Is it possible to illustrate a budget constraint depending on more than two factors in a regular spreadsheet? How?

Answer 1

Table containing all of combinations from your example required 10*9*10*90=8100 rows (!)

But total cost are product of simple formula:

Total cost = (Price*interest)+(Running costs + Mortgage)

Right?

So you can make this huge table and filter data.

OR

Create regular formula and insert values of variables manualy OR add sliders for adjusting variables in Total cost. It's handy solution.

See my test worksheet, there are both variants (sliders are not working in Web Office)

PS: There are Defined names for better formulas readability.