Al Arafat Siddique
The Excel PV function is a financial function that calculates the present value of an investment: the total amount that a series of future investments is worth now. The function calculates the PV (present value) depending on the time value of money concept.
The PV function returns the present value of a loan if the rate of interest, the amount paid per month, and the number of periods are known. Here is an overview:
Download Practice WorkBook
Download this practice workbook to exercise while you are reading this article.
Introduction to the PV Function
Objective:
To calculate the present value of a loan or investment.
Syntax:Â Â
=PV(rate, nper, pmt, [fv],[type])
Arguments:
Imagine a scenario that you are going to take a home loan. Now you are supposed to pay a fixed amount of $1,000Â monthly (pmt) to pay the total amount with a fixed interest rate of 10 %( rate) annually within a certain period of time, say 5 years.
In another case, you have a plan to raise a fund of $25,000 for your children’s education in the next 15 years, then $25,000 is the future value (fv). Now, it is easy to calculate how much money you need to save each month keeping the rate of interest in mind.
| Argument | Required or Optional | Value |
| rate– interest rate per period | Required | From the 1st scenario, rate is 10% annually, (10/12)= 0.833% monthly, 10*4/12=3.33% quarterly |
| nper– the number of the payment periods in an annuity | Required | From the 1st scenario, 5*12=60 (12 months a year) periods in 5 years |
| pmt– the amount of payment made each period and cannot change over the life of the annuity | Required if fv is not given Or, Optional if fv is given |
From the 1st scenario, pmt is $1000 per period pmt is 0 by default if not specified |
| fv- the future value or a cash balance you expect after the last payment | Required if pmt is not given Or, Optional if pmt is given |
From the second scenario, the future value is $25,000 fv is 0 by default if not specified |
| type-payment type | Optional | If payments are due at the- end of the period – put 0 or omit beginning of the period – put 1 |
3 Suitable Examples of Using the PV Function in Excel
1. Find the Present Value of Annuity Using the PV Function in ExcelÂ
In this example, we’ll find out the present value of a 5-year loan that has an annual interest rate of 10%. The loan should be paid by a monthly payment of 1000 dollars and the amount will be the same throughout the annuity.
To make the calculation, put the following formula in the cell you want to store the present value.
=PV(10/12,5*12,1000,,0)which returns $1200 as the present value.
=PV(rate, nper, pmt, [fv],[type])
Here,
rate: 10/12= .833 Â Â Â Â Â // the annual rate is divided by 12 as the no the of payments per year is 12.
nper: 5*12=60 Â Â Â Â Â Â Â Â Â Â //5 years is multiplied by 12(No of payments per year)
pmt: 1000 dollars
type: 0, as the payments are due at the end of the month.
Another Case:
The above example now calculates the present value quarterly( no of payments per year is 3).
Put the following formula in the cell you want to make the calculation.
=PV(10/3,5*3,1000,,0)and it returns the present value of 300 dollars.
Formula Breakdown:
=PV(rate, nper, pmt, [fv],[type])
Here,
rate: 10/3= 3.33%Â Â Â Â Â // the annual rate is divided by 3 as the no the of payments per year is 3.
nper: 5*3=15Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â // 5 years is multiplied by 3(No of payments per year)
pmt: 1000 dollars
type: 0, as the payments are due at the end of the month.
2. Use of the PV Function to Calculate the Initial InvestmentÂ
We can estimate the initial investment required to raise a fund of 15,000 dollars in 15 years with an interest rate of 5% annually. Let’s put the following formula in the selected cell:
=PV(0.4166%,180,,15000)
The present value returned by the formula is 7,097 which is the initial investment needed to raise the fund in time.
=PV(rate, nper, pmt, [fv],[type])
Here,
Rate:Â Â 5/12= .4166%Â // the annual rate is divided by 12 as the no the of payments per year is 12.
nper: Â 15*12=180Â Â Â Â // 15 years is multiplied by 12(No of payments per year)
Fv: Â Â Â Â Â 15000
type:Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â // as the payments are due at the end of the month.
Similar Readings
- How to Use IPMT Function in Excel (8 Examples)
- Use Excel PRICE Function (3 Appropriate Examples)
- How to Use IRR Function in Excel (4 Examples)
- Use XIRR Function in Excel (3 Methods)
- How to Use YIELD Function in Excel (4 Effective Examples)
3. A Comparison between Two InvestmentsÂ
Let’s compare two scenarios to illustrate how the PV function is helpful in making investment decisions. We have two investment plans to both will generate profit in future but which is better. Here are the plans:
Plan 1: Pay 1000$ monthly for 5 years at an interest rate of 5% annually.
Plan 2: Pay 2500$ quarterly for 8 years at an interest rate of 5% annually.
We are spending the same amount of money on both of the plans but the present values are different.
The comparison shows the present values are 2400 dollars and 1500 dollars for plan 1 and plan 2 respectively. That’s how the PV function helps us to decide on investment plans that one is better.
Things to Remember
- The annual interest rate and interest rate per period can or cannot be the same. If the number of payment periods is once a year then both the values are the same. Otherwise, we’ll divide the annual rate by the number of payment periods.
- You need to put the arguments in the PV function in sequential order.
- If any argument of the function is not provided then leave it blank.
- The PV function will show #NAME? error if any of the arguments is non-numeric.
ConclusionÂ
Now, we know how to use the PV function in Excel. Hopefully, it would encourage you to use this function more confidently. Any questions or suggestions don’t forget to put them in the comment box below
