Financial: Maximum Loan From A Monthly Payment

All versions

Most people know what payment they can afford and need the loan size that fits it. PV works backwards from the payment, rate and number of periods to the present value, which is the loan amount. This recipe also shows the total interest paid over the term.

Quick formula: PV of the payment stream at the monthly rate over the number of months:

=ROUND(PV(C2/12,D2*12,-B2),0)

A $450 payment at 7% for 5 years supports a loan of about $22,726.

Functions used (tap for the full reference guide):

PV ROUND

The example

Three payment scenarios. A longer term or lower rate lifts the maximum loan for the same payment.

	A	B	C	D	E	F
1	Case	Payment	APR	Years	Max loan	Interest
2	Car loan	450	7.0%	5	$22,726	$4,274
3	Truck loan	650	6.5%	6	$38,668	$8,132
4	Equipment	900	8.9%	7	$56,115	$19,485

The formula

PV takes the monthly rate, the number of months and the payment as a negative number:

=ROUND(PV(C2/12,D2*12,-B2),0) // maximum loan; total interest = payment*months - loan

How it works

Here is the logic, one piece at a time:

C2/12 converts the annual rate to a monthly rate.
D2*12 converts years to months.
-B2 makes the payment a negative cash outflow so PV returns a positive loan.
ROUND(...,0) rounds to whole dollars; the second column subtracts the loan from total payments.

Add down payment and trade-in to the loan to see the price you can afford.

Try it: interactive demo

Interactive

Enter the monthly payment, annual rate and term.

Monthly payment ($)

Annual rate (%)

Term (years)

Variations

Add a down payment

Add cash down to get the purchase price.

=ROUND(PV(C2/12,D2*12,-B2),0)+DownPayment

Solve for the payment instead

Reverse the question with PMT.

=PMT(C2/12,D2*12,-LoanAmount)

Pitfalls & errors

Passing the payment as a positive number returns a negative loan.

Dividing the annual rate by 12 gives a nominal monthly rate; the lender's APR may be compounded differently.

Check the answer against a lender's quote; fees can change the true payment.

Practice workbook

📊

Download the free Financial: Maximum Loan From A Monthly Payment practice workbook

Edit the yellow payment, rate and term cells; the loan size and interest recalculate.

Frequently asked questions

Does this include taxes and insurance?

No. It is only principal and interest; subtract taxes and insurance from the budget first.

What if the rate is 0%?

PV with a zero rate fails in some versions; use payment times months instead.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: PV ROUND