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.