# 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.

Source: rianjs.net

Category: Credit

## Similar articles:

4 Tips to Determine How Much Mortgage You Can Afford