calculate-loan

01 Loan payments

Home Affordability Spreadsheet: Free Calculator

Calculator No. 01-12834 · 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 free home affordability spreadsheet can estimate a monthly housing budget from income, debts, down payment, interest rate, and loan term. This calculator estimates the home price supported by the monthly amount you enter, then shows the loan amount and estimated monthly principal-and-interest payment; it does not determine loan approval or include every cost of owning a home.

Formula

The estimate first assigns a portion of gross monthly income to housing, subtracts the monthly debt payments entered, and treats the remainder as the available monthly principal-and-interest payment. It then uses the standard fixed-payment loan formula to estimate the loan principal that payment could repay.

  • Housing payment budget B = gross monthly income I × budget share S ÷ 100 − monthly debt payments D. If this is below zero, the calculator uses zero.
  • Monthly rate r = APR ÷ 1,200, converting an annual percentage rate to a monthly decimal rate.
  • Number of payments n = loan term in years T × 12.
  • Estimated loan amount L = B × [1 − (1 + r)−n] ÷ r.
  • Estimated home price H = down payment P + L.
  • Monthly principal-and-interest payment M = L × r ÷ [1 − (1 + r)−n].

Income, debts, down payment, loan amount, price, and monthly payment are in dollars; the APR and budget share are percentages, and the term is in years. This page uses the entered budget share as a simplified planning assumption. It subtracts debt payments from that housing budget; it does not calculate a lender’s debt-to-income ratio or apply underwriting rules.

Worked Example

Using the calculator’s default inputs: gross monthly income $8,000; monthly debt payments $600; budget share 28%; down payment $60,000; APR 6.5%; and term 30 years.

  1. Monthly housing budget: $8,000 × 0.28 − $600 = $1,640.
  2. Monthly rate: 6.5 ÷ 1,200 = 0.0054167. Number of payments: 30 × 12 = 360.
  3. Estimated loan amount: $1,640 × [1 − (1 + 0.0054167)−360] ÷ 0.0054167 ≈ $258,430.
  4. Estimated home price: $60,000 + $258,430 = $318,430, rounded to whole dollars.
  5. Monthly principal-and-interest payment: approximately $1,640.00.

The final payment matches the budget used to derive the loan, apart from rounding the displayed home price and loan amount to whole dollars.

Assumptions

  • The loan has a fixed APR and level monthly payments throughout the entered term.
  • Payments are made monthly; the first payment is assumed to follow the loan start, with interest calculated monthly at APR ÷ 12.
  • The model treats the entered APR as the rate for the loan calculation and does not separately model compounding or a changing rate.
  • Principal and interest only: property taxes, homeowners insurance, mortgage insurance, homeowners association charges, maintenance, closing costs, and escrow are not included.
  • The down payment is added to the estimated loan amount to produce a price estimate; transaction costs and any other cash needed at purchase are not represented.

How to Use a Free Home Affordability Spreadsheet

A spreadsheet and a web calculator can both provide a quick estimate, but a spreadsheet lets you save assumptions, make copies, and compare scenarios. Before entering figures, check the labels, units, and formulas. Monthly income belongs in a monthly field, and a yearly income figure must be divided by 12 before use. Enter debt payments as monthly amounts, not outstanding balances.

  • Use gross income consistently if using this calculator’s budget-share method. Gross income is before deductions; take-home income is a different measure.
  • Include recurring monthly debt obligations you want reflected in the estimate. Do not enter a credit-card balance where the calculator expects a monthly payment.
  • Enter the down payment as a dollar amount, and enter the annual APR as a percentage: type 6.5 for 6.5%, not 0.065.
  • Try alternate income, down payment, rate, or term assumptions separately. Save the inputs with each scenario so the results are interpretable later.

If a free spreadsheet uses a different method, its result may differ. Some worksheets start with a target payment; others use a debt-to-income percentage, include property costs, or require a selected monthly budget. Compare the assumptions and formulas, not just the headline price. In a spreadsheet you control, a formula error, stale copied cell, or percentage entered as a decimal can materially change the output.

What the Estimate Leaves Out

A home price is not the same as a complete monthly housing cost. Principal and interest are only two parts of ownership. Taxes and insurance can be substantial and may change; mortgage insurance or association charges may apply in some situations. Repairs, utilities, and closing costs also affect a household’s cash needs, but this calculator has no inputs for them. Look up relevant figures for the property and include them in a separate budget rather than treating this result as an all-in payment.

The entered budget share is not a universal affordability rule, and the result is not a prediction of what a lender will approve. Lenders may assess income, debts, assets, credit history, property details, and other information using their own procedures. A spreadsheet’s percentage assumption cannot reproduce that review. Likewise, an estimated price is not a property valuation or a loan offer.

Choosing and Checking a Spreadsheet

A useful free home affordability spreadsheet should make its assumptions visible and allow you to change the major inputs without hiding calculations. Look for a clear distinction between purchase price, down payment, loan principal, principal-and-interest payment, and other monthly housing costs.

  • Check whether the APR is annual and whether the spreadsheet converts it to a monthly rate.
  • Confirm that the term is in years or months as labeled, and that the payment count is consistent with that unit.
  • Inspect whether taxes, insurance, mortgage insurance, and other expenses are included, separately listed, or omitted.
  • Test a simple case: increasing the down payment while holding other inputs fixed should reduce the required loan amount for the same price.
  • Review whether results are rounded and whether rounding occurs in intermediate calculations or only in the displayed values.

These checks matter whether the sheet is downloaded or built from scratch. A spreadsheet can be free to use while still asking for personal financial information; avoid entering sensitive details into a file or service unless you understand how that information is handled.

Frequently Asked Questions

Is there a free home affordability spreadsheet?
Yes. Free worksheets and calculators are available, and this page provides a no-cost estimate. Check each tool’s assumptions, included expenses, and formula before relying on its output.

How does a home affordability spreadsheet estimate a price?
It typically estimates a monthly payment budget, uses a loan-payment formula to convert that amount into a possible loan balance, and adds the down payment. The result depends on the inputs and the method the spreadsheet uses.

Does this calculator include property taxes and insurance?
No. It estimates principal and interest only. Add estimated taxes, insurance, mortgage insurance, association charges, and other costs separately for a fuller budget.

Should I use gross income or take-home pay?
This calculator’s budget-share input is applied to gross monthly income. If you plan using take-home pay, use a method designed for that measure rather than mixing gross income with net-income assumptions.

Is the estimated home price what I can borrow or get approved for?
No. It is arithmetic based on the inputs, not an approval decision, loan offer, or guarantee. A lender’s review may use additional information and criteria that this calculator does not model.

Why does my spreadsheet show a different answer?
It may use a different budget percentage, debt treatment, interest-rate convention, payment timing, rounding method, or include costs this calculator omits. Compare the underlying assumptions and formulas to identify the difference.

This result is an estimate, and the lender’s disclosure gives the exact figures.

Related

Formula used
in inc | Gross monthly income | $ | 8000 | 0 | 100000000
in debt | Monthly debt payments | $ | 600 | 0 | 100000000
in share | Housing budget share of gross income | % | 28 | 0.01 | 100
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
out price | Estimated home price | $ | down + (max(0, inc * share / 100 - debt) * (1 - (1 + apr / 1200) ^ -(yrs * 12)) / (apr / 1200)) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 0
out loan | Estimated loan amount | $ | price - down | 0
out pmt | Monthly principal-and-interest payment | $ | loan * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 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 →