calculate-loan

01 Loan payments

Loan Amount Excel Formula: How to Calculate It

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

Monthly payment $405.53
Total interest
$4,331.67
= Monthly payment × loan term × 12 − loan amount
Loan amount
$20,000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount ($), Interest rate (APR) (%), Loan term (years)
You get
Monthly payment ($), Total interest ($)
Rounding
2 dp

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.

This calculator works out the loan amount you can borrow based on a target monthly payment, interest rate, and loan term. You enter the payment you want, the APR, and the number of years, and it returns the maximum principal that fits those inputs. Use the formula below to replicate the calculation in Excel or Google Sheets.

Formula

The standard loan payment formula solves for the payment when you know the principal. To solve for the principal, you rearrange it:

P = PMT × [1 – (1 + r)^(-n)] / r

Where:

  • P = loan amount (present value) in dollars
  • PMT = monthly payment in dollars
  • r = monthly interest rate = APR / 12 / 100 (as a decimal)
  • n = total number of monthly payments = years × 12

In Excel, the direct function is =PV(rate, nper, -pmt) where rate is the monthly rate, nper is the number of months, and pmt is the monthly payment (entered as a negative number because it is an outflow). For example, =PV(8%/12, 60, -405.53) returns approximately $20,000.

Worked Example

Using the calculator defaults: monthly payment = $405.53, APR = 8%, term = 5 years (60 months).

  1. Convert APR to monthly rate: r = 8 / 12 / 100 = 0.0066667
  2. Total payments: n = 5 × 12 = 60
  3. Plug into the formula: P = 405.53 × [1 – (1 + 0.0066667)^(-60)] / 0.0066667
  4. Calculate (1 + 0.0066667)^(-60) ≈ 0.67121
  5. 1 – 0.67121 = 0.32879
  6. Divide by r: 0.32879 / 0.0066667 = 49.3185
  7. Multiply by PMT: 405.53 × 49.3185 ≈ $20,000

The result is $20,000, matching the calculator output. The total interest shown is the payment multiplied by the number of payments minus the principal: 405.53 × 60 – 20,000 = $4,331.80.

Assumptions

  • Fixed interest rate for the entire term (no adjustable rates).
  • Payments are made at the end of each month (ordinary annuity).
  • Interest compounds monthly at APR / 12.
  • No fees, taxes, insurance, PMI, or escrow are included.
  • The payment is constant; extra payments or balloon payments are not modeled.

When to Use This Formula

Use this calculation when you know the monthly payment you can handle and want to see the maximum loan principal that fits. It is common for auto loans, personal loans, and some mortgages. For example, if you budget $400 per month for a car at 6% APR for 4 years, the loan amount would be about $17,048. The formula works for any loan with level payments and a fixed rate.

It is also useful for comparing offers: a lender quotes a payment, and you can reverse-engineer the principal to verify the price. In Excel, the PV function does this directly, but the manual formula helps you understand the math behind it.

How to Choose the Right Inputs

  • Monthly payment: Use a realistic budget figure, not the maximum the lender offers. Include other debts and living expenses.
  • APR: Get the actual rate from a lender, not a typical or advertised rate. APR includes some fees, but not all.
  • Term: Longer terms reduce the payment but increase total interest. Shorter terms cost more monthly but less overall.
  • Down payment: For a purchase, subtract the down payment from the vehicle or home price to get the principal you need to borrow.

Remember that lenders also consider your debt-to-income ratio, credit history, and collateral value. The formula gives a mathematical limit, not an approval guarantee.

Common Mistakes and Misconceptions

A frequent error is using the annual rate directly without dividing by 12. For example, using 8% instead of 0.6667% per month will give a much smaller loan amount. Another mistake is forgetting to convert years to months: a 5-year loan has 60 payments, not 5.

People also confuse the payment amount with the total cost. The loan amount is the principal, not the total of all payments. The total interest is separate and can be significant over long terms.

A common misconception is that the formula accounts for fees or insurance. It does not. If a loan has origination fees, the actual amount you receive may be less than the principal, or the APR may be higher than the nominal rate. Always read the loan estimate.

Limitations of the Formula

The formula assumes a fixed rate and equal payments. It does not handle:

  • Variable or adjustable rates that change over time.
  • Interest-only periods or balloon payments.
  • Prepayment penalties or extra payment benefits.
  • Fees that are rolled into the loan balance.
  • Daily or simple interest calculations (most consumer loans use monthly compounding).

For real estate, property taxes and insurance are often escrowed and added to the monthly payment, but they are not part of the principal calculation. The formula gives the pure loan amount, not the full monthly housing cost.

Frequently Asked Questions

What is the Excel formula for loan amount?
Use the PV function: =PV(rate, nper, -pmt). For example, with a $300 monthly payment, 5% annual rate, and 36 months, enter =PV(5%/12, 36, -300) to get about $9,995. The negative sign on the payment is essential.

How do I calculate the maximum loan I can afford?
Enter your comfortable monthly payment, the current APR from a lender, and the term you want into this calculator. The result is the principal that matches those inputs. Lenders may approve less based on your debt-to-income ratio and credit score.

Does the formula include interest?
Yes, the formula accounts for interest because it uses the rate to discount future payments. The loan amount is the present value of all future payments, so interest is built in. The total interest is shown separately as the difference between total payments and principal.

Can I use this for a mortgage?
Yes, the math works for any fixed-rate loan. However, mortgages often have property taxes, insurance, and PMI added to the payment, which are not included here. Use the principal portion of the mortgage only.

What if I make extra payments?
The formula assumes level payments. Extra payments reduce the balance faster and shorten the term, but the loan amount calculation remains the same for the original schedule. To model extra payments, you need a separate amortization schedule.

Why is the payment negative in Excel?
Excel treats money you receive (like a loan) as positive and money you pay out as negative. Using a negative payment tells Excel the direction of cash flow, which is required for the PV function to return a positive principal.

This result is an estimate based on the inputs you provide; the lender’s loan disclosure gives the exact figures for your specific offer.

Related

Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 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 →