Exceljet

Quick, clean, and to the point

Calculate original loan amount

Excel formula: Calculate original loan amount
Generic formula 
=PV(rate,periods,-payment)
Explanation 

To calculate the original loan amount, given the loan term, the interest rate, and a periodic payment amount, you can use the PV function. In the example shown, the formula in C10 is...

=PV(C5/12,C7,C6)

How this formula works

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the PV function is to calculate the the original loan amount, when given the other 3 components.

For this example, we want to find the original amount of a loan with a 4.5% interest rate, and a payment of $93.22, and a term of 60 months. The PV function is configured as follows:

rate - The interest rate per period. We divide the value in C5 by 12 since 4.5% represents annual interest: 

C5/12

nper - the number of periods comes from cell C7, 60 monthly periods in a 5 year loan.

pmt - The payment made each period. This is the known amount $93.22, which comes from cell C6. By convention, payments in PV are input as negative values.

With these inputs, the PV function returns 5,000.226, which is displayed as $5000 using number formatting. The actual loan amount is $5000 even, but the monthly payment is rounded to the nearest penny causes FV to return a slightly different result.
 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.