A loan has four main elements: the amount, the interest rate, the loan term or the number of periodic payments, and the payment amount per period. We can use the Excel built-in PV function to calculate the original loan amount given the other three elements.
The Excel PV Function
The PV function calculates the present value of a loan: the total amount a sequence of future payments is worth now, and it has the following syntax:
The PV function has the following arguments:
- Rate This argument is required, and it is the interest rate per period.
- Nper This argument is mandatory, and it is the total number of payment periods.
- Pmt This is a required argument, which is the payment amount paid each period.
- Fv This argument is optional. It is the future value or cash remainder you want to achieve after making the last payment. If we omit it, the default value of 0 (zero) is assumed.
- Type This argument is optional. It is either number 1 or 0, indicating when payments are due. The value 1 indicates that payments are due at the start of the period, and 0 (or omitted) shows that payments are due at the end.
Example Dataset
We use the following example dataset that shows the interest rate, payment periods, and payment per period to explain how to calculate the original loan amount using the PV function.
Calculate the Original Loan Amount
We calculate the original loan amount using the steps below:
- Select cell B7 in the example dataset and type in the formula below:
|
1 |
=PV(B3/12,B4,B5) |
Note: The annual interest rate in cell B3 is divided by 12 to determine the monthly interest rate.
- Press Enter
The original loan amount of $6,603.82 is displayed in cell B7.
Conclusion
This tutorial showed how to calculate the original loan amount in Excel using the PV function. We hope you found the tutorial helpful.