Calculate a Loan Amount from Monthly Payments in Excel
Question details
Calculate a loan amount using a known monthly payment, loan term, and annual interest rate.

- 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.
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.
Calculate the Loan Amount with Excel's PV Function
Use PV to find the present value of a series of fixed monthly payments.
Open Excel and select an empty cell in a worksheet.
For a $1,000 monthly payment over 60 months at a 4% annual interest rate, enter =PV(4%/12,60,-1000) and press Enter.
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 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. Open a spreadsheet: Launch WPS Office, open Spreadsheet, and create a blank workbook or open an existing one.
- 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. 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. 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.

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.




