Excel PV Formula (Table of Contents)
PV Formula in Excel
PV Formula or Present Value formula in excel is used for calculating the present value of any loan amount. By this, we can calculate the amount of loan required to purchase anything or the asset’s present value when we have taken the loan. This is a complete financial formula that will not be seen in any operation calculations.
PV Formula in excel can be used from Insert Function, which is situated beside the formula bar, by clicking on the icon.
PV Formula in Excel has the following arguments:
- Rate: It is the rate of interest per compounding period. We can directly use the whole rate of interest, or we can divide this by the total compounding period, let’s say a month and use the interest applied for a single month.
- Nper: It is the total number of payments or the installments being paid in the whole tenure. Payment can be made monthly, quarterly or yearly.
- Pmt: It is a fixed amount which we need to pay in a period.
The above shown are mandatory arguments required in PV Formula. Below are some optional argument as well;
- Fv: The future value of a borrowed asset at the end of all payment of all period is made. If we do not keep this value, then automatically, it will be considered 0.
- Type: Use 1 if the EMI is paid at the start of the month, 0 if the EMI is paid at the end of the month. For this, also, if we do not keep them in excel automatically, it will be considered as 0.
How to Use PV Formula in Excel?
PV formula in excel is very simple and easy to use. Let’s understand how to use PV Formula in excel with some examples.
Excel PV Formula – Example #1
We have data where a person wants to borrow a loan for an item that he wants to buy. A bank is offering the loan with an interest rate of 8% yearly. And that person can pay the loan for 10 years with a fixed monthly payment of near about Rs. 1000/-
If we summarize the above data, then we will have;
As we have discussed the argument above, we can keep a rate of interest as it is, or we can divide it within the formula or else we can first scrub the argument separately and then use it in the formula. For better understanding, let’s scrub the data separately first. For that, we need a rate of interest applicable for per month which will be our Rate and a total number of payment which will be made in the whole tenure of loan which will be our Nper.
Now, as per Input data which we have seen above, our Rate is calculated by using the formula =B2/B4
The Result will be as given below.
Nper is calculated by using formula =B3*B4
The Result will be as shown below.
Now we can directly use these parameters in our PV Formula. First, go to the cell where we need the output. Then go to the Insert function option located beside the formula bar as shown below.
Once we do that, we will get the Insert function box. Now from there, under the Financial category from Or select a category option search PV function from the list. Or else, we can keep the Or select a category as ALL and look for the required function as shown below. And click on Ok.
Now for the selected cell for output E2, we will get an argument box for PV Function. Now from there, select the Rate, Nper, and Pmt as calculated above.
As we can see for the function argument of PV Formula, we have selected all the necessary cells in excel. At the bottom left of the argument box, we will get the result of the applied function. Here we are getting the result is negative, which means the borrowed value of the loan amount.
If we need to use this data in another form, then we can apply a negative sign (“-“) in the Pmt argument box to get a positive result. And once we apply the negative signs, we will see the formula result is positive in the bottom left of the argument box. Once done, click on Ok.
Once we click on Ok, we will get the result in the selected cell, as shown below.
The calculated present value loan amount in INR (Indian Rupees) Rs. 82,421.48/- is the final amount that can be sanctioned to that person for 10-year tenure with an interest rate of 8 if that person pays Rs. 1000/- monthly EMI.
Excel PV Formula – Example #2
Below is another example for which we need to calculate a Present Value or a Discounted value.
For this, go to the edit mode of the cell, where we need to see the output by typing the “=” sign (Equal). And search and select PV Formula in excel as shown below.
Here we will directly use the data and crunch it in the formula itself, as shown below. Here we have divided the Rate by the total number of months of the loan period. And Nper is kept 12 because the loan is only for 12 months. And we have considered the compounding period as 30 Year which is our FV.
Now press the Enter key to get the result as shown below.
As the obtained value PV is Rs. 4067.80/- which means the present value of loan amount for 30 years of compounding will be Rs. 4067.80/-
Things to Remember
- A negative sign used in Example 1 shows the borrowed loan amount.
- Keeping “0” for Type tells us that the payment is done at the end of the month.
- FV is optional, but in some cases, where the compound interest rate is applicable, then it is recommended to consider FV (Future Value).
Recommended Articles
This has been a guide to PV Formula in Excel. Here we discuss how to use the PV formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
18 Online Courses | 7 Hands-on Projects | 85+ Hours | Verifiable Certificate of Completion
4.5
View Course
Related Courses
