Microsoft Excel has much more for everyone. Do you have any idea what is PV in Excel?
The PV function is used quite often as a financial function because it helps in finding the present value of a loan or an investment.
With the PV function, you can easily calculate the future values of payment in the present time, so that you can have an idea about how much you need more investment. Apart from that the PV function is helpful in finding the present value of a single future value.
The syntax for PV in Excel:
Below is the formula for PV:
=PV(rate, nper, pmt, [fv], [type])
Let’s have a look at the arguments used in this formula:
RATE: It includes the interest rate per period
NPER: It includes the number of the payment period
PMT: It includes an amount that you have paid per period.
[FV]: It includes the future value of the investment.
[TYPE]: It includes the time of payments done.
Outflow is a term used in PV formulas that you need to add when any payment is done. Remember that outflows should be a negative number whereas inflows must be positive integers.
5 Things to Consider when Using PV Function:
Below are some useful points you must consider while using the Excel PV function to work properly:
- In the formula, when the fv value is zero or skipped, the pmt value must be added and vice versa.
- As already mentioned, outflows must be negative integers and inflows must be positive integers. For instance, when you have made an investment in an insurance allowance, you may use a negative number for pmt.
- The rate argument used in the formula must be supplied as a percentage or decimal integer such as 8% or 0.3.
- If an argument is not numeric, you will encounter #VALUE! error in the PV function.
How to Use the PV Function in Excel?
Let’s understand what is PV in Excel with simple examples:
Example 1:
You can see an annuity that pays episodic payments of $100.00 with a 5.5% interest rate annually. Monthly this annuity pays and will continue to do so for the next 5 years. Below is the setup and formula for the PV function:
With the PV function, you can find the fair present value. The annuity worth is $5,235.28 in the recent time period.
Calculate the PV of the Annuity:
Suppose, an annuity is purchased that is paid regularly an amount of $200 to the insurance company monthly for the next 10 years. With a 9% of annual interest rate, the annuity responds back 9%.
You must add data in individual cells to start with:
Monthly payment (B4): -200
Annual interest rate (B2): 9%
Number of periods per year (B6): 12
Annuity type (B5): 1
Number of years: (B3): 10
You can simply notice the rate and pmt both have different periods. To perform the PV function, you need to have a few conversions:
The annual interest rate must be converted into a periodic rate. For this, you need to divide the annual rate by the number of periods per year.
Rate= annual interest rate / no. of periods per year
You can multiply the annuity in years with the number of periods per year to find the net number of periods:
nper = no. of years * no. of periods per year
As you know, here we are using monthly annuity in the example, you can simply divide and multiply it with 12 or cell B6.
Now, the full form of PV would be as:
=PV(B2/B6, B3*B6, B4, , B5)
Likewise, you can find the PV of a week, quarter, or semi-annual annuity. You just need to change the number of periods per year in the relevant cell:
Semiannual: 2
Weekly: 52
Annual: 1
Quarterly: 4
Monthly: 12
To Sum Up:
This post clarifies what is PV in Excel along with its uses and syntax. The arguments used in the formula are super convenient to use when you need to calculate the present value of an investment.
