Excel PV Function
Many of us buy insurance or invest in a company to secure a steady cash flow for our retirement years or any other future plans. Sometimes you also invest your money in FD with decent annual interest. Whatever the reason, there is always a second thought “is it a good deal?” To answer this question, the best way is to find the present value of your investment. Don’t worry because Microsoft Excel has provided an inbuilt Excel function named PV function (it stands for “present value”) to solve this problem.
In this tutorial, we will cover the definition, syntax, and the steps to build correct PV formula for a series of cash flows, various examples, and more!
What is PV Function?
The Excel PV Function tells the present worth of the future payments (present value).
PV function is categorized under Excel financial functions that return the present value of an annuity, loan, or investment based on a fixed rate of interest. The function was introduced with Excel 2007 and since then has been available in all versions, including Excel 2010, Excel 2013, Excel 2016, Excel 2017, and Excel 365.
Syntax
Parameters
Rate (required)- This argument represents the rate of interest per period.
Nper (required) – This argument represents the total number of payment periods for the length of an annuity.
pmt (optional)- This argument represents the payment made for each period. If this parameter is omitted, the default value is 0 but in that case the fv parameter must be included.
fv [optional]: This argument represents the future value, or a cash balance the user want after the last payment is made. Since it is an optional parameter therefore its default value is 0. But if it is omitted in that case the pmt argument must be included.
type [optional]: This argument provides the information regarding when payments are due.
- If the user provides 0 it means end of period
- If he supplies 1, it means beginning of period. Since it is an optional parameter therefore its default value is 0.
Points to Remember about PV Function
Kindly have a look at the below given point to efficiently utilize the PV function in your worksheets and bypass common mistakes in Excel:
- If the pmt parameter is omitted or its value is 0 in that case the fv parameter must be included and vice versa.
- You can specify the rate parameter as a percentage or decimal number, for instance 13% or .13.
- Money that you pay (outflow of the amount) is represented by negative numbers, and the amount you receive is represented by positive numbers. For instance, if you are investing money into a company (you are paying them), there would be an outflow of cash; it will be represented using a negative number for pmt.
- If you want to calculate periodic cash flows, be uniform with the rateand nper For example, if you make 6 yearly payments at a 7% annual interest rate, specify the value 6 for the nper parameter and 7% or 0.07 for the rate (interest rate) parameter. If you plan to invest the monthly payments for 10 years, then nper will be 10*12 (a total of 120 periods) and 7%/12 for rate.
- It throws a #VALUE! error if any of the arguments specified in your PV formula is non-numeric. To fix this error in Excel, always ensure that all the parameters are numeric and all the specified numbers are not formatted as text.
Examples
Example 1: Calculate the present value of the annuity using PV function for the data given in the below table.
NOTE: In the above example, the pmt parameter is negative (represented by red) as we are investing the amount.
To determine the present value of the annuity follow the below given steps:
Step 1: Add helper row at the bottom of table
Put your cursor below the table and add your helper row i.e., “Present Value”. In this row we will type the PV formula and will fetch the periodic payment for the given data.
Refer to the below image:
Step 2: Enter the PV formula
Move to next cell of your helper row and start typing the formula = PV(
It will look similar to the below image:
STEP 3: Add the parameter to your formula
- In the first parameter, we will specify the interest rate per period. In the above table, the rate is mentioned in cell C4. So our formula becomes: =NPER (C3,
- Next, this function will ask for the NPER parameter. This parameter represents the total number of payment periods for the length of an annuity. The NPER is mentioned in cell C5. So our formula becomes: =NPER (C3, C5
- The pmt function represents the payment. Since we it an outflow so pmt is negative and is mentioned in cell C5. So our formula becomes: =NPER (C3, C5, C4
- fv is an optional parameter that represents the future value, or a cash balance the user want after the last payment is made. Since it is an optional parameter therefore its default value is 0. So our formula becomes: =NPER (C3, C5, C4, 0
- This last parameter, i.e., type, is optional. In this, we specify the information regarding when payments are due. Since we want the payment for the end of the period, so we will specify 0. So our formula becomes: =NPER (C3, C5, C4, 0, 0)
The overall formula will look similar to the below image:
STEP 4: PV will return the output
Press the enter button as soon as you are done typing the formula. Excel will return the output for your PV formula. As shown below it will return 79986 as an output of your periodic payment.
Therefore, we can conclude that Present Value of 48 future payments of $1,806 is $79,986.
Example 2: Suppose you have invested in an insurance company where a regular payment of $200 is to be paid to the company at the beginning of every month for the next 10 years. The funding makes a 9% annual interest compounded monthly. Calculate how much the total annuity at the end of 10 years?
NOTE: In the above example, the pmt parameter is negative (represented by red) as we are investing the amount.
To determine the total annuity at the end of 10 years the below given steps:
Step 1: Add helper row at the bottom of table
Put your cursor below the table and type the heading of your helper row i.e., “Present Value”. In this row we will type the PV formula and will fetch the annuity worth for the given data.
Refer to the below image:
Step 2: Enter the PV formula
Move to next cell of your helper row and start typing the formula = PV(
It will look similar to the below image:
STEP 3: Add the parameter to your formula
- In the first parameter, we will specify the interest rate per period. In the above table, we are given an annual interest rate. To convert the specified annual interest rate to a periodic rate, we will divide the annual rate by 12 (total periods in a year). So our formula becomes =PV (C3/12,
NOTE: Always remember to convert annual interest rate to periodic interest rate while working with Excel PV formula for monthly cash flows.
- Next, we will specify the nper To fetch the total number of periods, we will multiply the annuity term in years by 12 (total periods in a year. (nper= number of years * number of periods in a year). So our formula becomes: =PV(C3/12, C4*12,
- Next, we will specify the pmt The negative pmt signifies an outflow payment made for each period. The pmt is mentioned in cell C5. So our formula becomes: =PV(C3/12, C4*12, C5
- Since the future value is not used in the above table therefore the fvparameter is omitted. So our formula becomes: =PV(C3/12, C4*12, C5,,)
- This annuity type is 1. The type is mentioned in cell C6. So our formula becomes: =PV(C3/12, C4*12, C5,, C6)
The overall formula will look similar to the below image:
STEP 4: PV will return the output
Press the enter button as soon as you are done typing the formula. Excel will return the output for your PV formula. As shown below it will return 15,906 as an output of your periodic payment.
Therefore, we can conclude that the annuity worth will be 15,906.7 at the end of 10 years.
In the above example, we calculated the yearly annuity. Similarly, you can find the present value for a weekly, quarterly, or semiannual annuity. To achieve this, you only need to change the number of periods per year in the respective cells:
- Weekly: 52
- Monthly: 12
- Quarterly: 4
- Semiannual: 2
- Annual: 1
