Description
Use this function to calculate the present value of an investment based on a series of future payments. Supported in Chains.
PV is useful for determining how much an investment is worth now, given a specified interest rate and a series of future payments. It's commonly used in financial planning and investment analysis.
Syntax
PV(rate, nper, pmt, [fv], [type])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
rate |
Yes | The interest rate per period. | A decimal number, a reference to a cell containing a decimal number, or a formula which results in either of these. |
nper |
Yes | The total number of payment periods in an investment. | A positive integer, a reference to a cell containing a positive integer, or a formula which results in either of these. |
pmt |
Yes | The payment made each period. |
A number, a reference to a cell containing a number, or a formula which results in either of these. This number is typically negative if you are paying out. |
fv |
No | The future value, or cash balance, you want after the last payment is made. | A decimal number, a reference to a cell containing a decimal number, or a formula which results in either of these. If omitted, it's assumed to be 0. |
type |
No | Indicates when payments are due. | 0 for end of period, 1 for beginning. If omitted, it's assumed to be 0. |
Example
Scenario
You're considering an investment that promises to pay $1,000 annually for the next 5 years. If the interest rate is 5% per year, what's the present value of this investment?
Sample data
| A | B | |
|---|---|---|
| 1 | Loan Amount | 200000 |
| 2 | Annual Interest Rate | 0.05 |
| 3 | Loan Term (years) | 30 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| An investment promises to pay $1,000 annually for the next 5 years. If the interest rate is 5% per year, what's the present value of this investment? | =PV(5%, 5, -1000) |
This formula works in the following manner:
For this data, the formula returns 4,329.48. |
| An investment promises to pay $1,000 annually for the next 5 years. If the interest rate is 5% per year, what's the present value of this investment if payments are made at the beginning of each period.? | =PV(5%, 5, -1000, 0, 1) |
This formula works in the following manner:
For this data, the formula returns 4,545.95. |
| An investment promises to pay $1,000 annually for the next 5 years. If the interest rate is 5% per year, what's the present value if there's a $5,000 lump sum payment at the end? | =PV(5%, 5, -1000, 5000) |
This formula works in the following manner:
For this data, the formula returns approximately -8,237.15. The negative result indicates the initial investment (outflow) required to receive these future cash flows. |
Notes
-
rateis the interest rate per period. If you make monthly payments but yourrateis annual, divide the rate by 12. -
nperis the total number of payment periods. If you make monthly payments for 5 years,nperwould be 5*12 = 60. -
pmtis typically negative if you are paying out money, and positive if you are receiving money. - The
fvandtypearguments are optional. If omitted,fvis assumed to be 0 andtypeis assumed to be 0. - The result of PV is typically negative, indicating money you would need to invest now.
Tips
- Use PV to determine if a future stream of payments is worth a certain amount today.
- You can combine PV with other financial functions such as FV, PMT, or XNPV for comprehensive financial analysis.
- Be consistent with your time periods. If your payments are monthly, make sure your interest rate is also expressed monthly.
- Remember that changing the
typefrom 0 to 1 can significantly affect the result, especially for short-term investments.