Calculation Issues

How to Calculate a Loan Amount from Payments in Excel

Huda Qurayshi Sep 30, 2026 870 views

Question details

The user needs to find the original principal loan amount based on a known periodic payment, interest rate, and loan term.

How to Calculate a Loan Amount from Payments in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Determining the initial principal balance of a loan when only the fixed periodic payment amount and loan terms (interest rate and duration) are known.
Observed behavior: The user requires a method to reverse-calculate standard financial formulas to find a missing loan amount variable.

Before you start

Before you begin, ensure you have the exact figures for your periodic payment amount, the annual interest rate, and the total number of payment periods organized in your Excel worksheet.

Solution 1Recommended

Calculate Loan Amount Using Goal Seek

The Goal Seek feature allows you to reverse-engineer financial formulas by adjusting the loan amount until the calculated payment matches your known payment amount.

Goal Seek is part of Excel's What-If Analysis tools. It is perfect for finding a missing variable (like the principal loan amount) when you already know the result of a formula (the payment amount).

Set up the PMT formula

In an empty cell, enter the PMT formula using a placeholder cell for the loan amount, along with your known interest rate and number of periods (e.g., =PMT(rate, nper, loan_amount_cell)).

Open Goal Seek

Navigate to the 'Data' tab on the Excel ribbon, click on 'What-If Analysis' in the Forecast group, and select 'Goal Seek'.

Configure Goal Seek parameters

In the 'Set cell' box, select the cell containing your PMT formula. In the 'To value' box, enter your known payment amount. In the 'By changing cell' box, select your placeholder loan amount cell.

Calculate the result

Click 'OK'. Excel will iterate through different values and replace your placeholder with the exact original loan amount required to match the payment.

Tip: Ensure your interest rate matches your payment frequency. For monthly payments, divide the annual interest rate by 12.

Solution 2

Calculate Loan Amount Using the PV Function

You can directly calculate the initial loan amount by using the Present Value (PV) function if you have all the other variables.

Seamless Financial Analysis

Calculate Loan Amounts Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful financial functions like PV and Goal Seek, allowing you to accurately calculate loan amounts, payments, and interest rates just as you would in Excel.

1. Open your financial worksheet: Launch WPS Spreadsheet and open the document containing your loan variables.
2. Access What-If Analysis: Navigate to the 'Data' tab and click on 'What-If Analysis' to find the Goal Seek tool.
3. Apply the calculation: Set your target payment value and select your principal cell to instantly calculate the original loan amount.

Fully compatible with Microsoft Excel's PMT, PV, and Goal Seek functions and formatting.Lightweight and lightning-fast for complex financial modeling.Intuitive user interface that makes finding 'What-If Analysis' tools easy.Free to use with comprehensive cross-platform support.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the interest rate divided by 12 in loan calculations?

Most loan interest rates are quoted annually (APR), but payments are usually made monthly. Dividing the annual rate by 12 gives the correct monthly interest rate required for accurate periodic formulas.

Can I calculate the loan amount if the payments are irregular?

Standard PMT and PV functions assume regular, fixed payments. For irregular payments, you would need to use the NPV (Net Present Value) function to discount each individual cash flow back to its present value.

Why does my loan calculation result in a negative number?

Financial functions in spreadsheet software use cash flow sign conventions. Money paid out (payments) is represented as negative, while money received (the loan amount) is positive. You can fix this by adding a minus sign before the payment variable in your formula.

Excel & Data Problems