Updated August 10, 2023
PV Function in Excel
PV Function also expands as Present Value, which is quite a useful function available under a financial category in excel; it is used for finding out the present value at any point of the time period of borrowed or invested amount and considering the rate of interest monthly. This is a quite useful function for calculating how much we need to invest or pay to complete the due with the rate of interest applicable at the point of time.
What is the PV Function in Excel?
PV denotes “Present Value”. PV function in Excel helps us to find out the present value of an investment, loan, etc…
Are you making any kind of investment and unaware of whether it is profitable or not? Use the PV function to check whether it is profitable or not.
PV function is an inbuilt function in Excel and accommodated under Financial Functions. This function is available as a Worksheet function and the VBA function.
PV Formula in Excel
Below is the PV Formula in Excel :
PV Formula in Excel has the following arguments :
- Rate: This is the interest rate per period. If we have taken a loan of INR. 2.5 lakhs at 18% per annum and choosing monthly EMI. Then our interest rate becomes 1.5% per month i.e. 18/12 = 1.5%
- Nper: This is the total payments we make to clear the loan. If the loan is for 2 years, then the Nper becomes 24, i.e. 2*12 = 24 months.
- Pmt: This is simply the payment per period, which is constant until the end of the investment or loan. Payment includes both principal and interest amounts.
- [FV]: This is an optional argument. This is the future value of an investment we wish to make. If we do not mention this argument, Excel, by default, takes like 0.
- [Type]: This is an optional argument. This determines when the payment is due. If we mention 0, the payment is at the end of the period, i.e. month. If we mention 1, the payment is at the month’s or period’s beginning.
Notes:
- All the payments are constant and cannot be changed over a period.
- Constant rate of interest throughout the loan period.
- We need to mention the outflow in negative and the inflow in positive numbers.
How to Use the PV Function in Excel?
PV function in Excel is very simple and easy compared to other functions in Microsoft Excel, which contains lots of arguments or parameters.
Example #1
Let’s say you are making an investment of INR. 4500 per month for 5 years at an interest rate of 8.5% per annum.
We need to find what the present value of that investment is.
The formula is =PV (B5/12, B3*B4, -B2, 0, 0). Apply the PV function to get the present value.
=PV (B5/12) This is the expected interest rate. I have divided the interest rate by 12 because 8.5% is per year. Therefore, by dividing 12, we get the interest rate per month.
B3*B4, This is the total payments we make. For 5 year, we make 60 payments at 12 payments per year i.e. 5 * 12 = 60 months.
-B2 This is the payment per month. Since it is an outflow, we need to mention it in a negative number.
0, 0) this is not a mandatory argument. So mentioned zero.
Below is the future amount using the FV formula:
Example #2
Let’s say you have availed a loan of INR. 2.5 lakh at an interest rate of 12% per annum. You have decided to pay out the money monthly for 1.5 years.
At the end of the loan tenure, you will pay 2,94,221 after 1.5 years. You need to find the present value of that loan. Apply the PV function to get the present value.
The loan amount you are paying along with interest is 2, 94,221, and the present value of that loan amount as of today is INR. 2, 45,974.
Below is the future amount by using the FV formula:
Using the PV function in Excel, we got to our loan value as of today. Similarly, we can do all kinds of analysis using the PV function.
We can alter our payment methods, like monthly, quarterly, and yearly payments. According to our payment option, we need to divide our interest rate.
In the case of monthly, divide the interest rate by 12.
In the case of quarterly, divide the interest rate by 4.
In the case of yearly, divide the interest rate by 1.
Things to Remember
- Negative numbers should represent all the outgoing payments; similarly, all the positive numbers should represent all the incoming payments.
- PV function in Excel can accept only positive numbers. If the supplied values are not numeric, then we get the error as #VALUE!
- There is always a constant interest rate and payment outflow.
- We need to convert the interest rate according to the payment period.
- If the FV and Types argument is not mentioned by default, it takes the value as zero.
Recommended Articles
Although this has been a guide to PV Function in Excel. Thus, here we discuss the PV Formula in Excel and How to use the PV function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
