PV Function - Get the Present Value in Excel

Add to Favorites
Author:

The Present Value (PV) function in Excel will return the current value of an investment.  This calculates the current value of a series of future payments, a future lump sum value, or both combined.

This is a simple, yet powerful function, giving you the ability to understand how much money you need for something in the future, or simply how much something is worth today.

Syntax

PV(rate, nper, pmt, [fv], [type])

Argument

Description

Rate

The interest rate used per period to calculate the present value.  Make sure this rate is per period; if the rate is 5% but there are two periods per year, the number for this argument would be 2.5%.

Nper

The number of periods used for the calculation.  If each year is broken into two periods and you calculate the PV for a period of 5 years going into the future, this number would be 10.

Pmt

This is the payment that is made each period.  This amount cannot change per period when using the PV function. This argument is actually optional, but only if you include the FV argument in order to calculate the value of a lump-sum - an example for this will be shown below.

[Fv]

Optional argument.  This is the Future Value argument and is used when you already know the future value of the investment.  This is an optional argument and you can leave it blank or put a 0, zero, in for it, which is the default value; if it's blank or zero, you must put a value in for the Pmt argument.

[Type]

Optional argument. This tells the PV function when the payment in the Pmt argument will be made, either 0, for the end of the period, or 1, for the beginning of the period.  If you leave this argument empty, Excel will assume it is 0, or that payments are made at the end of each period.

[] means the argument is optional.

Time for some examples so that you understand how to use this function and can get comfortable with it.

PV Function Examples

Let's start with a basic iteration of this Present Value function.  Don't forget to download the accompanying workbook so that you can follow along.

Here, I have put all of the arguments into their own separate cells so that we can clearly see their values.  We have a present value of $14,346.66 on a series of payments that equals $1,000 per period at an interest of 5.00% and with a pre-determined future value of $5,000 and where the payments are made at the end of each period (Type is set to 0 [zero]).

Now I'll step into the function to go through examples.

Example 1 - Negative Values

Let's look at the function I have above:

You can see that, though I have entered all of the numbers into the cells as positive numbers, I turned some of them into negative numbers within the PV function.

The Pmt and Fv arguments have a negative sign in front of their cell references, "-B4" and "-B5".  I did this so that we could get a nice looking positive value from the PV function.  Due to the way the Present Value is calculated, if we do not use negative values for these two arguments, the output of the function will be negative:

The absolute number is still the same, just negative.  Don't worry about why this happens, this is normal for this type of calculation in finance.  But, you must remember that this happens so that you don't confuse yourself or others when you make your spreadsheet.

It's also very important to make sure that both values, Pmt and Fv, are both either negative or positive or you will get an incorrect result from your equation.

Example 2 - Most Common PV Example - Present Value of a Series of Future Payments

Let's look at the most common Present Value example in finance, getting the present value of a future series of payments.  If you see this in finance books, it might include a specific scenario which, in the end, is nothing more than a series of future payments.

Here, we simply clear-out the FV argument:

Notice that, now, there is nothing in cell B5, the value for the Future Value argument.

I have entered the PV function so that it references cells in the worksheet, so the actual PV function will still look the same in cell B8, but, in this case, since the two optional arguments, Fv and  Type, are or can be left empty, we could also write the function like this in cell B8:

=PV(B2,B3,-B4)

All that was done was to remove the Fv and Type arguments.  The Type argument was removed since it was already 0, which is the default value for that argument.  The Fv argument was 0, which is the same as leaving it blank or not entering it at all.

Now, if we wanted to still have a Type argument in there, we would have to skip over the Fv argument like this:

=PV(B2,B3,-B4,,B6)

The Fv argument has simply been left blank.

You could also just put a zero in for this argument like this:

=PV(B2,B3,-B4,0,B6)

It is important to note these variations because you may encounter them and you don't want to be confused by something as simple as a small variation of the function.

Example 3 - Present Value of a Future Lump Sum Payment

This is the last basic variation of this function.  It allows you to figure out how much a single lump sum payment in the future is actually worth today.

Here is how it will look in our spreadsheet:

The only change here is that the Pmt argument has been left blank and there is an amount in for the Fv argument.

This result now tells us that if we receive $5,000 in the future, 20 periods from now (periods will be explained more in a separate section below) and at an interest rate of 5.00%, that it is worth $1,884.45 today, right now.

(Note also that I changed the text in cell A3 to better reflect that argument (Nper) since, for this example, there will not be a series of payments, but a single lump sum payment.  The text in cell A3 doesn't really matter; I just wanted it to better reflect its purpose for this example.)

As in the last example, since we are omitting an argument, there are a few different ways this can be represented in the Excel function:

=PV(B2,B3,,-B5,B6)

The above function simply removes the reference to cell B4 and leaves the argument empty.  You could, however, also input a 0, zero, for that argument and it would work the same:

=PV(B2,B3,0,-B5,B6)

Also, the Type argument is meaningless here since, remember, it only refers to when payments are made, at the start or end of the period.  Here, no payments are being made, so we don't need to include it.  Therefore, we can simply remove the reference to the Type argument:

=PV(B2,B3,0,-B5)

All we did was to remove the reference to cell B6.

Note on Periods

When calculating the present value of anything, you need to ensure that you keep the period calculations consistent.

If there are two periods in a year but you have an interest rate that is a yearly rate, you need to divide that rate by two so that you have the correct rate for the period.  A period does NOT mean a year; it can be any segment of time.

The Nper argument is not the total number of years for an investment but the total number of periods.  So, if there are two periods per year and you have an investment time of 5 years, you need to put 10 in for the Nper argument because, in this case, there are 10 periods in 5 years (5 years*2 periods = 10 total periods).

Also, remember that the Pmt argument refers to the payment per period and not a payment per year.  Therefore, a similar calculation should be made for this as for the Nper argument.

Conclusion

The PV function is a simple but very important function that is used in finance.  This is one of the building blocks of finance and you should memorize how to use this function well as it will serve as a very important part of complex spreadsheets that you might use in the future.


Excel Function: PV()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Excel Function: PV()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course