# How to determine how large a mortgage you can afford using Excel

Note: this is applicable to fixed rate mortgages.

Mortgage brokers typically use your gross monthly income to calculate the amount they’re willing to lend you. Frankly, this is a very bad way of calculating what you can actually afford. It is more useful to know what you can reasonably afford each month before you go house shopping.

If you’ve got a monthly payment in mind that you’re comfortable making, you can use a present value calculation to come up with the amount you can afford to finance. In Excel, this is very easy with the `pv` function:

`=pv(interest rate, number of payments, payment, montly payment)`

• Interest rate: If annual percentage rate (APR) is 3.5%, this number will be 3.5%/12 = (0.035/12).
• Number of payments: 12 months * 30 years = 360
• Payment: What you’re comfortable paying on a mortgage each month.

Suppose:

• You’re willing to spend \$1,750 a month on a house
• APR: 3.5%
• Term: 30 years

`=pv((0.035/12), 360, 1750)`

You can afford to finance: \$389,716.22

When determining what you can afford each month, don’t forget the following:

• House insurance
• Mortgage insurance (PMI)
• House taxes, typically calculated as some amount per thousand dollars of assessed house value
• Homeowners’ fees, if applicable

These are things that many renters don’t need to pay, and thus forget to think about when buying their first home.

## 2 thoughts on “How to determine how large a mortgage you can afford using Excel”

1. bak says:

Perfect, plain and simple!!

2. Chuck says:

Any Idea how I could add PMI, Real Estate Taxes, HOA to calculate what I can afford without using circular references?