calculate-loan

01 Loan payments

Home Affordability Calculator Excel: Build a Budget

Calculator No. 01-12817 · Updated · Formula and assumptions shown below

The calculator on this page is switched off while it is checked. The formula is below, and the worked example shows how to do the sum by hand.

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A home affordability calculator in Excel can estimate a purchase-price ceiling from your income, monthly debts, down payment, interest rate, and housing costs. This worksheet-style calculator estimates a monthly housing budget and the home price that fits it; its result is a planning estimate, not a loan approval or a statement of what you should spend.

Formula

The calculator first sets a total monthly debt budget as gross monthly income multiplied by the selected debt-to-income ratio. It subtracts other monthly debt payments to find the available housing budget, then subtracts estimated monthly taxes, insurance, and other housing costs to find the amount available for mortgage principal and interest. If the result is below zero, the calculator floors the mortgage payment at zero.

For a fixed-rate mortgage with monthly payments, the loan principal supported by a payment is:

Loan principal = M × [1 − (1 + r)−n] ÷ r

The estimated home price is:

Home price = Loan principal + Down payment

  • Income: gross monthly income, in dollars.
  • DTI: the selected total debt-to-income ratio, as a percentage.
  • Debt: monthly payments on debts other than the proposed mortgage, in dollars.
  • Cost: estimated monthly property taxes, homeowners insurance, and any other housing costs included in the reader’s estimate, in dollars.
  • Down: cash down payment applied to the purchase price, in dollars.
  • APR: annual interest rate entered as a percentage. The formula uses monthly rate r = APR ÷ 1200.
  • Years: mortgage term in years; the payment count is n = years × 12.
  • M: monthly principal-and-interest payment available after the other modeled costs, in dollars.

This is an Excel-friendly calculation: cells can hold each input, with formulas linking the monthly budget, loan principal, and purchase price. The calculator uses the same arithmetic without requiring a spreadsheet.

Worked Example

Using the calculator’s default inputs:

  • Gross monthly income: $8,000; DTI: 36%; other debt: $600.
  • Down payment: $60,000; APR: 6.5%; term: 30 years; other housing costs: $500 per month.

First, the total monthly debt budget is $8,000 × 0.36 = $2,880. Subtract the $600 in other monthly debt to get an available housing budget of $2,880 − $600 = $2,280. After the estimated $500 for taxes, insurance, and other housing costs, the amount left for principal and interest is $2,280 − $500 = $1,780 a month.

The monthly rate is 0.065 ÷ 12 = 0.0054166667, and the number of payments is 30 × 12 = 360. The loan principal supported by a $1,780 payment is:

$1,780 × [1 − (1 + 0.0054166667)−360] ÷ 0.0054166667 ≈ $281,846.

Add the $60,000 down payment: the estimated maximum home price is $341,846 when rounded to whole dollars. The monthly housing budget output is $2,280. The estimated principal-and-interest output is $1,780. These figures follow the formula and defaults; changing any input changes the estimate.

Assumptions

  • The mortgage has a fixed APR and equal monthly payments.
  • Payments are made monthly; the model treats the APR as a nominal annual rate divided by 12.
  • The loan is fully repaid over the entered term, with no extra payments.
  • Taxes, insurance, and other housing costs are represented only by the single monthly cost input. Maintenance, utilities, closing costs, and moving expenses are not separately calculated.
  • The down payment is treated as cash applied directly to the purchase price. The calculator does not estimate fees, taxes due at closing, mortgage insurance, or lender underwriting adjustments.

How to Use an Excel Home Affordability Calculator

To recreate the worksheet in Excel, place the inputs in separate cells and label their units. Keep monthly income, debts, and housing costs on a monthly basis; enter the interest rate as a percent and the term in years. A clear layout reduces errors and makes it easier to test different scenarios.

  1. Enter gross monthly income and monthly payments for existing debts.
  2. Choose a DTI percentage to test. This is an assumption for the worksheet, not a guarantee of what a lender will accept.
  3. Enter a down payment, annual APR, and loan term.
  4. Estimate monthly housing costs not included in principal and interest, such as property taxes and homeowners insurance.
  5. Use the formula section to calculate the monthly budget and resulting price estimate. Change one assumption at a time to see its effect.

In Excel, the mortgage-principal expression can be written with cell references or with the built-in PV function. If using PV, check its sign convention: Excel may display the loan amount as negative when the payment is entered as positive. The calculator here reports the purchase price as a positive amount.

How to Read the Estimate and Its Limits

The estimated maximum price is the arithmetic result under the inputs, not a recommended spending limit. A higher down payment increases the modeled price because it reduces the amount that must be borrowed for a given purchase price. More monthly debt, higher modeled housing costs, or a higher rate reduces the principal supported by the remaining payment budget. A longer term generally supports a larger principal for the same monthly payment, but also means more scheduled payments.

The DTI input is a simplified budgeting assumption. The calculator applies it to gross income and subtracts entered debt and housing costs; it does not account for every lender’s method, income documentation, credit review, property assessment, or other underwriting criteria. Ask a lender for the rate, payment, fees, and qualification figures that apply to a specific transaction.

It also does not calculate a complete ownership budget. Repairs, utilities, homeowners association charges, changing taxes or insurance, and costs at purchase can materially affect cash needs. Enter realistic monthly estimates for the expenses you want included, and consider keeping expenses outside this simplified formula visible in a separate budget.

Common Excel Mistakes

  • Mixing annual and monthly figures: use monthly income and debts, convert the annual APR to a monthly rate by dividing by 1,200, and convert years to months by multiplying by 12.
  • Using take-home pay with a gross-income DTI assumption: this formula is defined around gross monthly income. A separate household budget can help assess spending from take-home pay.
  • Omitting housing costs: the principal-and-interest payment is not the full monthly cost of owning a home. Include taxes and insurance in the cost input, and account separately for costs the input leaves out.
  • Confusing price with loan amount: the estimate adds the down payment to the modeled mortgage principal. It does not mean the entire purchase price is borrowed.
  • Rounding too early: keep full precision in intermediate Excel cells and round only displayed results. Spreadsheet results can differ slightly if intermediate values are rounded.

Frequently Asked Questions

Can I make a home affordability calculator in Excel?
Yes. Enter the assumptions in labeled cells, calculate the monthly payment budget, use the fixed-rate mortgage formula to estimate supported loan principal, and add the down payment to estimate the purchase price.

What is the difference between home affordability and a mortgage payment calculator?
A payment calculator starts with a loan amount and estimates its payment. An affordability calculator starts with income, debts, and a housing budget, then estimates the loan or purchase price that fits those assumptions.

Does this estimate include property taxes and insurance?
Only if you enter them in the monthly cost input. It does not look up local costs or determine how those expenses may change.

Is the estimated price what a lender will approve?
No. It is a simplified calculation, not a lending decision. A lender uses its own review and disclosures to provide transaction-specific figures.

Why does a higher interest rate lower the estimated price?
At a fixed monthly payment and term, a higher rate means more of each payment goes toward interest, so the formula supports a smaller loan principal.

The result is an estimate for educational use, and the lender’s disclosure gives the exact figures.

Related

Formula used
in inc | Gross monthly income | $ | 8000 | 0 | 10000000
in dti | Maximum total debt-to-income ratio | % | 36 | 0.01 | 100
in debt | Other monthly debt payments | $ | 600 | 0 | 1000000
in down | Down payment | $ | 60000 | 0 | 100000000
in apr | Interest rate (APR) | % | 6.5 | 0.01 | 100
in yrs | Loan term | years | 30 | 0.0833 | 50
in cost | Monthly taxes, insurance, and other housing costs | $ | 500 | 0 | 1000000
out price | Estimated maximum home price | $ | (max(0, inc * dti / 100 - debt - cost) * (1 - (1 + apr / 1200) ^ -(yrs * 12)) / (apr / 1200) + down) | 0
out house | Available monthly housing budget | $ | max(0, inc * dti / 100 - debt) | 2
out pmt | Estimated monthly principal and interest | $ | max(0, inc * dti / 100 - debt - cost) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →