5

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: enter image description here

  • Here's on that adds debt, debt to income ratio, tax, insurance etc if you need them zillow.com/mortgage-calculator/house-affordability – AbraCadaver Jul 24 '18 at 20:04
  • 1
    You're ignoring the fact that your rent payment covers things like maintenance and property taxes that your landlord was responsible for; you will be responsible for such costs in addition to your mortgage payment. – chepner Jul 25 '18 at 13:55
  • Also, keep in mind there will be other complicating factors such as Private Mortgage Insurance (PMI). In general if you don't put 20% down you will have to pay PMI, this will make your loan more expensive. One should avoid PMI if at all possible. – Glen Yates Jul 25 '18 at 14:19
5

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.

3

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.

2

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
  • I forgot to mention - this is equivalent to your formula and both are correct, though you define term in years and the rate to be my rate, minus one, times twelve. If any of the other answers produce a different answer than your and my formula, I think you'd do well to calculate out the amortization table and see which is right. – Patrick87 Jul 25 '18 at 12:42
2

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.

1

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.

  • I've tried doing this but something always goes wrong and gets me wonky answers. I have several different versions I've tried and was hoping to fact check. – Liz Jul 24 '18 at 16:19
  • Add what you've tried to your question so we can see where you want wrong. – D Stanley Jul 24 '18 at 16:20
  • Added to the OP. – Liz Jul 24 '18 at 16:24
  • 2
    @Liz your formula is correct - I've verified it against Excel's PV function. I'd double-check your arithmetic, especially parentheses. Can you add inputs you're using and what result you get? – D Stanley Jul 24 '18 at 16:40
  • 1
    What is a loan's tenor? – stannius Jul 24 '18 at 20:28
1

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

  • 1
    Note that algebraically, that formula is the same as the one in the question. Just multiply the numerator and denominator by (1+r)^n. – D Stanley Jul 24 '18 at 20:58
  • @DStanley Sure, but this one is simplified, and provides an alternate derivation for the OP to follow. – stannius Jul 24 '18 at 21:02
  • That's fine - I'm just clarifying that the formula is correct - it is likely the implementation that is wrong. – D Stanley Jul 24 '18 at 21:12

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.