Formula for Cost of Potential Home from Monthly Mortgage

Question

I'm trying to find a way to calculate the price of a home I can afford based on my potential downpayment, the monthly rent I can afford, and the interest of a potential loan. I've seen questions like this and this and I've consulted other sites like this, but I can't seem to find one with the home value as the output.

The structure I need is an equation that spits out a total home value (e.g. $800,000) with the following variables:

• Monthly Mortgage Payment
• Term of Loan
• Interest Rate
• Downpayment

I'm fine assuming it's a standard mortgage, but if there was a way I could try out different types (ARM, etc.) that would be a plus.

One issue: I know Excel has the PMT function, but I want to know what's happening, so I need it to be in basic, PEMDAS-able operations.

Can anyone lead me in the right direction?

Additional Information:

Here's a picture of my (unfortunately-handwritten) attempt:

Answer 1

You are trying to create one long equation. Which can be a valuable tool, but “under the hood” there needs to be a number of calculations.

• 28% of your monthly income can be applied to the mortgage, property tax, and home insurance.
• figure that 23% is what’s available for the mortgage only.
• take that figure, use the current rate for the length mortgage you want, and calculate the loan value from that.
• if you have the down payment to afford the house, you now have the number.

One can take these figures and combine into one equation, but either way, you need to use a PV equation to go from PMT to value of the mortgage you can borrow.

Answer 2

Elaborating on D Stanley's answer. Let's look at an example.

• Interest Rate: 4%
• Term: 30 years
• Monthly Payment: $4000 total, 3000 for the mortgage, 1000 for tax and insurance
• Downpayment: $50,000

Step by Step:

1. punch term and and rate into a mortgage calculator for $100,000 loan: $477
2. Divide your available mortgage payment by $477 and multiply by $100,000 = $629,000
3. Add your down-payment: $679,000

So if you can spent $3000/month on the mortgage payment, think you can get a 4% rate for a 30 year mortgage and have $50,000 for down payment you can pay $679,000 on a house

Keep in mind that this needs to include points, closing costs, fees, realtor, not just the offer price for the house.

Answer 3

There are a lot of answers here that do not provide exactly what you have asked for, but do a good job of explaining how to calculate the present value of an annuity, which is closely related.

You asked for a single formula to compute how much house you can afford...

...based on my potential downpayment, the monthly rent I can afford, and the interest of a potential loan. I've seen questions like this and this and I've consulted other sites like this, but I can't seem to find one with the home value as the output.

So, let's write one, based on the other answers:

Cost = downpayment + (0.23*monthly_income)*((1-1/(1+rate)^term))/rate)

Here, rate would be the APR of your mortgage, and term the number of years of the mortgage. The factor 0.23 is a guess at the fraction of your monthly income that could go towards mortgage payments, exclusive of property taxes, upkeep, insurance, and related costs. You can change this as you like.

Answer 4

The links you provide all calculate a payment amount as the loan amount (principal) times some factor based on interest rate and tenor. So just reverse it, dividing the payment by that factor to get a total principal amount.

From there, add your down payment amount and subtract closing costs to get the total home value you can afford.

Answer 5

The formula you are looking for is called present value, specifically present value of an annuity. An annuity in the mathematical sense is any series of payments for a defined period of time.

HouseValue = DownPayment + PV(Payment)

PV = Payment * ((1-1/(1+rate)^term))/rate)

Here, rate and term are both in reference to the compounding period, i.e. one month for a mortgage. The same generic formula can also be used for other terms though, for instance a series of payments once a year, or a savings account with daily compounding.

Algebraically, this is the same as the formula you got, just a bit simpler. You can get from yours to mine by dividing your numerator and denominator both by (1+rate)^term.

The derivation of the formula can be found e.g. at http://financeformulas.net/Present_Value_of_Annuity.html

Answer 6

The balance at payment N is:

N    B
0    P
1    P * r - m
2    (P * r - m) * r - m = P * r ^ 2 - r * m - m
3    (P * r ^ 2 - r * m - m) * r - m = P * r ^ 3 - m * r ^ 2 - m * r - m
n    (P * r ^ n) - m * (1 + r + r ^ 2 + … + r ^ (n - 1))
     = (P * r ^ n) - m * (1 - r ^ n) / (1 - r)

Here:

P = original principal of the loan: the home price minus downpayment
r = the per-pay-period interest rate - e.g., 3% APR would be 1.0025
m = the fixed monthly payment amount towards interest + principal
n = an arbitrary integer

To find P from the other variables we should fix the value n to N, the number of payments (the loan term) and then require that the balance after this payment be zero:

(P * r ^ N) - m * (1 - r ^ N) / (1 - r) = 0
P * r ^ N = m * (1 - r ^ N) / (1 - r)
P = m * (1 - r ^ N) / [(r ^ N)(1 - r)]

Notice that the original loan balance P is directly proportional to the amount m of your monthly payment. This makes a lot of sense if you consider it. Let's assume a middle-of-the-road monthly payment of $1000 and see what you can buy with a 3% APR on a 30-year (360-month) mortgage:

P = $1,000 * (1 - 1.0025 ^ 360) / [(1.0025 ^ 360)(1 - 1.0025)]
  ~ $1,000 * (-1.4568422115) / [(2.4568422115)(-0.0025)]
  ~ $1,000 * (-1.4568422115) / (-0.0061421055)
  ~ $237,190