Calculate original loan amount

Calculate original loan amount using an Excel formula

To calculate the original loan amount, you can use the PV function if you have the loan term, interest rate, and periodic payment amount.

How to calculate original loan amount in Excel

  1. Select cell C9
  2. Type the formula =PV(C3/12, C5, C4, 0)
  3. Here, C3= interest rate, C5= no. of total payments, C4= monthly payment.
  4. Press Enter.
  5. The formula will return the original loan amount.

Example

In the example below, we have a dataset in range C3:C6 detailing key inputs for a loan calculation, including the interest rate, monthly payment, total number of periods, and the end period of the loan. We want to calculate the original loan amount based on these inputs using a formula.

Formula:

=PV(C3/12,C5,C4)

Calculate original loan amount using an Excel formula

The formula in cell C9 uses the PV (Present Value) function to determine the loan amount by factoring in the monthly interest rate, number of payments, and payment value. The formula provides the initial principal amount needed to satisfy the loan conditions specified.

Explanation

Evaluate the formula:

  • C3/12 (Rate): Divide the annual interest rate (6.91%) by 12 to calculate the monthly interest rate. For example, 6.91% / 12 equals 0.576%.
  • C5 (Number of periods or nper): The second argument, C5, represents the total number of monthly payments (120). Since the loan term is 10 years, the number of months is 10*12 = 120.
  • C4 (Payment or pmt): Input the fixed monthly payment amount (-$115.64). The negative sign represents cash outflow from the borrower’s perspective.
  • PV Function: Use the PV function to calculate the present value of future payments, discounted at the monthly interest rate. In this formula, the present value represents the loan amount (principal).

Divide the annual interest rate (6.91%) by 12 to find the monthly interest rate. For example, dividing 6.91% by 12 gives 0.576%.

If you want to calculate the monthly payment instead of the loan amount use the formula:

=PMT(C3/12, C5, -10000)

Download the Workbook.