Spreadsheet Practice

How to work out how much you can borrow in Excel

Formula (Excel and Google Sheets)

=PV(B3/12,B4*12,-B2)

Use PV with the payment you can afford: =PV(B3/12,B4*12,-B2) is the largest loan that payment pays off.

Result on the example
$38,232.98 in B5
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B5. Change any input and the result updates, or edit the formula to experiment.

How it works

PV answers PMT's question in reverse: given a payment, how much is it worth today? 72 payments of $650 at 6.9% a year pay off a loan of about $38,200. The payment is entered as negative, money going out, so the loan amount is positive.

=PV(B3/12,B4*12,-B2)

Result38232.98084

  1. B36.90%

    The value in B3.

  2. B3/120.00575

    The / operator divides.

  3. B46

    The value in B4.

  4. B4*1272

    The * operator multiplies.

  5. B2$650

    The value in B2.

  6. -B2-650

    Makes the value negative.

  7. PV(B3/12,B4*12,-B2)38232.98084

    PV: Present value of future payments.

    • rateB3/120.00575
    • nperB4*1272
    • pmt-B2-650

In Google Sheets

The same formula works in Google Sheets: =PV(B3/12,B4*12,-B2). We ran it in Google Sheets mode on the same data and it returns $38,232.98.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=PV(B3/12,5*12,-B2)$32,904.64Different resultA shorter term means a smaller loan for the same payment. $32,904.64 over 5 years.

Common mistakes

  • Lenders also cap the payment at a share of income and add fees, so treat the result as an upper limit.
  • Divide the rate by 12 and multiply the years by 12, as with PMT.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Work out how much you can borrow

In B5, calculate the largest loan you can pay off with the monthly payment in B2, at the rate in B3, over the years in B4.

Questions

How do I check the answer?

Put it back into PMT: =PMT(B3/12,B4*12,-B5) returns the payment in B2.

How do I add a down payment to get a price range?

Add your down payment to the loan: =B5+B6.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.