What is the PV Function?
The PV function is categorized under Financial functions. It will calculate the present value of an investment or a loan taken at a fixed interest rate.
In financial statement analysis, PV will help calculate the value in today’s dollars of a series of future payments, assuming periodic, fixed payments and a fixed interest rate. The function can be used to calculate the present value with constant payments, or a future value, or the investment goal of the business.
Formula
=PV(rate, nper, pmt, [fv], [type])
The PV function uses the following arguments:
- Rate (required argument) – It is the interest rate per period. For example, if we take a business loan at 12% annual interest rate and make monthly payments, our interest rate per month is 12%/12, or 0.10%. we would enter 12%/12, or 0.10%, or 0.01, into the formula as the rate.
- Nper (required argument) – It is the total number of payment periods in an annuity. For example, for year loan and make monthly payment then the loan would have 60 periods. So NPER to be entered would be 60 months.
- Pmt (required argument) – It is the payment that is made for each period and cannot be changed over the life of the annuity. PMT includes Principal & taxes but excludes fees or taxes if any.
- Fv (optional argument) – It is the Future value of the investment or cash balance which we wish to attain. If fv is omitted, it is assumed to be 0. For example, if we want to save $500,000 to pay for a special project in 18 years, then $500,000 is the future value. We could then make a conservative guess at an interest rate and determine how much we must save each month. If fv is omitted, we must include the pmt argument.
- Type (optional argument) – The number 0 or 1 indicates when payments are due. If type is equal to 0 or omitted, payments are due at the end of the period. If set to 1, payments are due at the start.
Notes
- Annuity is a series of constant cash payments made over a continuous period. For example, a car loan or a mortgage is an annuity.
- In annuity functions, cash that we pay out, such as a deposit to savings, is represented by a negative number; cash that we receive, such as a dividend check, is represented by a positive number.
How to use the PV Function in Excel?
To understand the uses of the PV function, let us consider a few examples:
Example 1
Suppose we are given the following data:
The formula used is:
We get the result below:
A loan comprises four primary components: the loan amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. We can use the PV function to calculate the original loan amount when given the other three components.
Example 2
In the example below, the PV function is used to calculate the present value of an annuity that pays $5,000 per quarter for a period of 5 years. The interest is 10% per year and each payment is made at the start of the quarter.
The formula used is:
We get the result below:
Things to remember about the PV Function
- #VALUE! error – Occurs if any of the given arguments is non-numeric.
- One common error in using the PV function is not converting the annual interest rate into a monthly rate or quarterly rate, or as payments are made. If omitted, it can result in an error.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling and analysis. To learn more, check out these additional resources: