How to Use PV Function in Excel (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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:

the PV function in Excel


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

How to Use the PV Function in Excel: 3 Suitable Examples

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.

Formula Breakdown:

=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.

the PV function in Excel

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 PV function in Excel

The present value returned by the formula is 7,097 which is the initial investment needed to raise the fund in time.

Formula Breakdown:
=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.

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.

Download Practice WorkBook

Download this practice workbook to exercise while you are reading this article.


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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo