Using the Excel PV Function to Compute Present Value of Investments

Use the Excel spreadsheet PV function to compute the present value of a future stream of payments.

Here are three examples that show you how to use the PV function.

Example 1: Fixed Annuity - $1,200 Annual Payment for 15 Years

To calculate the present value of a fixed annuity, enter the interest rate, number of periods, payment amount, cash balance after the last payment and when a payment is made.

In a cell of an Excel spreadsheet enter:

=PV(.06,15,-1200,0,0)

Excel returns:

$11,654.70

So the value today of receiving annual payments of $1,200 for 15 years is $11,654.70.

The parameters of the PV function are:

PV Parameters
Parameter Value Comment
Interest rate
.06
Annual interest rate. Adjust annual interest rate if investments are monthly or some other interval.
Number years
15
Number of payments.
Payment Amount
-1200
Payment. Make negative to show positive PV.
Future Value
0
Future value or cash balance after last payment. Make negative if Payment is negative
When invested
0
A 1 indicates the investment is made at the beginning of the period. A 0 indicates the investment is made at the end of the period. Investments made at the beginning of the period yield more money.



Example 2: Fixed Annuity - $100 Monthly Payment for 15 Years

To calculate the present value of a fixed annuity, enter the interest rate, number of periods, payment amount and when payment is made.

In a cell of an Excel spreadsheet enter:

=PV(.005,180,-100,0,0)

Excel returns:

$11,850.35

In this example the annual interest rate and number of payments are adjusted to monthly values. The 6% annual interest rate is divided by 12 and the monthly payments are 15 multiplied by 12.

Example 3: Fixed Annuity - $100 Monthly Payment for 15 Years With $1,000 Cash Balance

Enter -1000 as the required cash balance after the last payment is made.

In a cell of an Excel spreadsheet enter:

=PV(.005,180,-100,-1000,0)

Excel returns:

$12,257.83


Related Articles and Calculators

Ordinary Annuity Calculator - Present Value
Using the Excel FV Function to Compute Future Value of Investments
Using the Excel POWER Function to Compute Investment Returns