Function Problems

Calculate a Loan Amount from Monthly Payments in Excel

Maira MehtabMaira Mehtab Nov 18, 2026 870 views

Question details

Calculate a loan amount using a known monthly payment, loan term, and annual interest rate.

Calculate a Loan Amount from Monthly Payments in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user knows the monthly payment, number of monthly payments, and annual interest rate and wants to find the loan's present value.
Observed behavior
The desired result is the loan amount supported by the specified payment, term, and interest rate.
Before you start

Confirm the monthly payment, annual interest rate, and loan term in months. The example assumes a fixed rate, payments at the end of each month, and no additional fees or remaining balance.

Solution 1Recommended

Calculate the Loan Amount with Excel's PV Function

Use PV to find the present value of a series of fixed monthly payments.

1
Open a worksheet

Open Excel and select an empty cell in a worksheet.

2
Enter the PV formula

For a $1,000 monthly payment over 60 months at a 4% annual interest rate, enter =PV(4%/12,60,-1000) and press Enter.

3
Adapt the inputs

In the formula bar, replace 4%, 60, and -1000 with your annual rate, number of monthly payments, and negative monthly payment. Divide the annual rate by 12 to get the monthly rate.

Calculate the Loan Amount with Excel's PV Function
Payment sign: Entering the payment as a negative number returns a positive loan amount. The formula assumes the stated annual rate can be divided by 12 to obtain the monthly rate.
Calculate loans in WPS Spreadsheet

Calculate a Loan Amount with WPS Spreadsheet

Use the PV function in WPS Spreadsheet to calculate a loan amount from a fixed monthly payment, term, and interest rate.

  1. 1. Open a spreadsheet: Launch WPS Office, open Spreadsheet, and create a blank workbook or open an existing one.
  2. 2. Enter the formula: Select an empty cell and enter =PV(4%/12,60,-1000) for a $1,000 monthly payment over 60 months at a 4% annual rate.
  3. 3. Calculate and adjust: Press Enter to display the loan amount. Edit the formula in the formula bar to use your own rate, term, and payment.
  4. 4. Save the workbook: Select Menu > Save As and choose an Excel-compatible format such as XLSX if you want to share the workbook with Excel users.
Calculate loan amounts with the PV functionOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with formulas in a free office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why is the monthly payment negative in the PV formula?

Excel uses cash-flow signs: a payment entered as money paid out is negative, so =PV(4%/12,60,-1000) returns a positive loan amount.

How do I use a loan term stated in years?

Multiply the term in years by 12 to get the number of monthly payments. For example, use 5*12 as the second argument in =PV(4%/12,5*12,-1000).

How can I calculate the monthly payment for a known loan amount?

Use the PMT function in an empty cell: =PMT(annual_rate/12,term_in_months,-loan_amount). The negative loan amount makes the returned payment positive.

Does the PV formula include loan fees?

No. The example calculates the present value of the specified payments and does not separately include fees. Check the lender's terms before treating the result as the amount you will receive.