# How to work out how much you can borrow in Excel

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

**Excel:** `=PV(B3/12,B4*12,-B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Budget | Value |
| Monthly payment you can afford | 650 |
| Annual interest rate | 0.069 |
| Term (years) | 6 |
| Loan amount |  |

Formula in B5: `=PV(B3/12,B4*12,-B2)`

Result: $38,232.98

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

## Other ways

- `=PV(B3/12,5*12,-B2)`: A 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.

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

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/how-much-you-can-borrow