PV function

The PV (Present Value) function in Excel calculates the present value of an investment or loan, based on a fixed interest rate, number of periods, and periodic payments. It is commonly used in financial analysis to determine the current worth of a future cash flow.

Syntax

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

Parameters

1. rate: The interest rate for each period.
2. nper: The total number of payment periods in the investment or loan.
3. pmt: The payment made each period; it cannot change over the life of the investment or loan. It is usually a negative number, indicating an outflow of cash.
4. [fv] (optional): The future value, or a cash balance you want to attain after making the last payment. If omitted, the default value is 0 (meaning no future value).
5. [type] (optional): The timing of the payments. This can be:
   • 0 (or omitted): Payments are made at the end of the period (default).
   • 1: Payments are made at the beginning of the period.

How It Works

The PV function calculates the present value of a series of future cash flows, using the following formula:

$$PV = \frac{PMT}{(1 + \text{rate})^{nper}} + \cdots + \frac{PMT}{(1 + \text{rate})^{nper-1}}$$

Where:

• PMT is the regular payment,
• rate is the interest rate per period,
• nper is the total number of periods.

Example

Let’s say you want to determine the present value of an investment where:

• The interest rate is 5% annually (or 0.05),
• The number of periods is 10 years,
• The annual payment is $1,000 (outflow),
• You expect the future value (after 10 years) to be $10,000.

To calculate the present value, use the formula:

=PV(0.05, 10, -1000, 10000)

In this case:

• 0.05 is the annual interest rate,
• 10 is the number of periods (10 years),
• -1000 is the payment (outflow),
• 10000 is the expected future value.

The result will give you the present value of this series of payments, taking into account the future value.

Important Notes

• The payment (pmt) should typically be a negative number, indicating money you pay out (such as loan payments or investments).
• If you don’t provide a future value (fv), it assumes a future value of 0 (such as for a loan where the balance is paid off).
• The type parameter (optional) controls when payments are made. The default (0) assumes payments are made at the end of the period. If payments are made at the beginning of each period, use 1.

Summary

The PV function is used to calculate the present value of a series of cash flows (such as loan payments or investment returns), given a constant interest rate and number of periods. It is widely used in financial analysis to determine how much a future series of payments is worth today.