PV
The PV function returns the present value of a loan or investment based on a constant interest rate per period and unchanging payment amounts.
This function is particularly useful for financial analysis, for example, when evaluating annuities or loans.
Syntax
PV(rate, number_of_payments, payment_amount, [future_value], [end_or_beginning])
| Argument | Description | Permitted values |
|---|---|---|
| discount | Interest rate | Number (for example, 5% or 0.05) or a reference to a cell containing a number |
| number_of_payments | Total number of payment periods | Integer or a reference to a cell containing a number |
| payment_amount | Payment for one period | Number (usually negative) or a reference to a cell containing a number |
| [future_value] | (optional) Expected balance after the last payment By default, 0 | Number or a reference to a cell containing a number |
| [end_or_beginning] | (optional) The number that determines when a payment should be made: at the end (0) or at the beginning (1) of each period. By default, 0 | 0 or 1 |
Examples of use
Calculation of the present value of an annuity
=PV(5%, 5, -1000)
This formula calculates the present value of an annuity that pays $1,000 per year for 5 years at 5% per annum.
Result: approximately 4329.47.
Use with future value
=PV(3%, 10, -200, 5000)
Here, the function calculates the present value of an annuity with a payment of $200 per month for 10 months, with a future value of $5,000 at 3% per annum.
Result: approximately −2014.42.
Nested functions
=PV(7%, 10, -1500, IF(A1="Yes", 10000, 0))
This formula calculates the present value of an annuity with a monthly payment of $1,500, where the future value depends on the value in cell A1: if A1 equals “Yes,” then the future value is $10,000, otherwise it is 0.
Notes
- The PV function can be used to analyze various financial scenarios and budget planning.
- Be careful with signs: outgoing money (payments) is usually indicated with a minus sign.