The Excel PV function has the syntax shown below.

PV(Rate, Nper, Pmt, FV, Type)

**Arguments used in the Excel PV function**

Rate = Discount rate per period (i)

Nper = Number of periods (n)

Pmt = Periodic payment

FV = Future Value

PV = Present Value

Type = 0 or 1 depending on whether Pmt is at the end or start of a period

## Use of the Excel PV Function

The Excel PV function is used to calculate the present value (PV) of the following:

- Present value of a lump sum
- Present value of an annuity
- Present value of an annuity due
- Present of a combined annuity and lump sum
- Present value of a combined annuity due and lump sum

## Present Value of a Lump Sum Using the Excel PV function

The Excel PV function can be used to calculate the present value (PV) of a lump sum of money received at the end of a period in the future.

### Present Value of a Lump Sum Example 1

If a lump sum of 7,000 is received at the end of period 4, and the discount rate is 5%, then the value of the lump sum today is given by the Excel PV function as follows:

Rate = 5% Nper = 4 Pmt = 0 (not used) FV = 7,000 Type = 0 (not used) Present value = PV(Rate, Nper, Pmt, FV, Type) Present value = PV(5%,4,0,7000,0) Present value = -5,758.92

The present value (PV) is negative as it represents the payment you would need to make today 5,758.92 (cash out), in order to receive back the 7,000 (cash in) at the end of period 4.

When using the Excel PV function to calculate a lump sum value, the Pmt and the Type arguments are not used, they can be left blank or set to zero.

### Present Value of a Lump Sum Example 2

If a lump sum of 6,000 is received at the end of year 3, and the discount rate is 6% per year compounded monthly, then the value of the lump sum today is given by the Excel PV function as follows:

Rate = 6%/12 = 0.5% Nper = 3 x 12 = 36 Pmt = 0 (not used) FV = 6,000 Type = 0 (not used) Present value = PV(Rate, Nper, Pmt, FV, Type) Present value = PV(0.5%,36,0,6000,0) Present value = -5,013.87

Again the present value is negative as it represents the payment out at the start of year 1 to receive 6,000 at the end of year 3.

This time the discounting is monthly so the argument Rate is set to the monthly rate of 6%/12 = 0.5%, and the number of periods argument Nper is set to 3 x 12 = 36 months.

It should be noted that formulas (without the equals sign) can be entered as arguments in the Excel PV function, so in the above example PV(6%/12,3*12,0,6000,0) would return the same answer.

## Present Value of an Annuity Using the Excel PV Function

An annuity is a series of constant periodic payments received at the end of each period. The Excel PV function can be used to calculate the present value of an annuity.

### Present Value of an Annuity Example

If the discount rate is 9%, what lump sum would need to be paid today for an annuity of 500 per month to be received at the end of each month for the next 7 years.

The amount to be paid must be the same as the present value of the monthly payments and is calculated using the Excel PV function as follows:

Present value = PV(Rate, Nper, Pmt, FV, Type) Rate = 9%/12 monthly Nper = 7 x 12 = 84 months Pmt = 500 per month FV = 0 (not used) Type = 0 (payment at the end of the period) Present value = PV(9%/12,84,500,0,0) Present value = -31,076.98

At a discount rate of 9%, the amount of 31,076.98 would need to be paid out at the start of year 1 in order to receive payments of 500 at the end of each month for the next 7 years.

In this example the argument FV is not used and can be set to 0 or left blank. The argument Type is used and is set to 0 (or can be left blank), and indicates that the annuity payment is at the end of each period.

## Present Value of an Annuity Due Using the Excel PV Function

An annuity due is a series of constant periodic payments received at the start of each period. An annuity due is similar to an annuity except that the payments are made at the start of each period instead of the end.

### Present Value of an Annuity Due Example

If the discount rate is 9%, what lump sum would need to be paid today for an annuity of 500 per month to be received at the **start** of each month for the next 7 years.

The amount to be paid must be the same as the present value of the monthly payments and is calculated using the Excel PV function as follows:

Present value = PV(Rate, Nper, Pmt, FV, Type) Rate = 9%/12 monthly Nper = 7 x 12 = 84 months Pmt = 500 per month FV = 0 (not used) Type = 1 (payment at the start of the period) Present value = PV(9%/12,84,500,0,1) Present value = -31,310.06

If the discount rate is 9%, the amount of 31,310.06 would need to be paid out at the start of year 1 in order to receive payments of 500 at the **start** of each month for the next 7 years.

In this example the argument FV is not used and can be set to 0 or left blank. The argument Type is used and is set to 1, and indicates that the annuity payment is at the start of each period.

The only change made in this example compared to the previous annuity example is that the Type argument has been set to 1 to represent payments at the start of the period. This has increased the amount required (present value) since the payments from the annuity (500) are going to be received one period earlier.

## Present Value of Combined Annuity and Lump sum

The Excel PV function can be used to calculate combinations of regular annuity payments together with a lump sum received at the end of the term. This is particularly useful for example, when carrying out bond price calculations.

Suppose the discount rate was 10%, and a business issued 10,000, 18 month, 8% bonds, with interest payable every 6 months. The total face value (par value) of the bonds is 10,000. The interest payment every 6 months for 18 months is 10,000 x 8% x 6 / 12 = 400.

The price of the bond can be calculated using the Excel PV function as follows:

Present value = PV(Rate, Nper, Pmt, FV, Type) Rate = 10%/2 = 5% per 6 month period Nper = 3 (6 month periods) Pmt = 400 per month FV = 10,000 Type = 0 (payment at the end of the period) Present value = PV(5%,3,400,10000,0) Present value = -9,727.68

The Excel PV function has worked out the present value of the annuity of 400, and the present value of the lump sum received at the end of the term and combined them.

The present value of the cash flows from the bond is 9,727.68 which is the theoretical price of the bond.

A similar calculation can be carried out using an annuity due by setting the Type argument to 1. In all cases the lump sum future value (FV) is deemed to have been received at the end of the term and is not affected by the Type argument.

## Using the Excel PV Function with Variable Arguments

Excel is at its most useful when the arguments can be varied without having to enter the Excel function each time a calculation is made. To do this a simple spreadsheet can be set up as shown below.

In this example spreadsheet, the variable arguments are entered in cells B4 to B8, and the Excel PV function is entered at cell B11 as =PV(B4,B5,B6,B7,B8). By changing any of the variables in B4 to B8, the present value can be recalculated without having to enter the Excel PV function each time.

The Excel PV function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

## About the Author

Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.