Excel PV Function
last modified October 5, 2026
The PV function calculates the present value of an investment or
loan. It answers a simple question: what is a stream of equal future cash flows
worth today at a given interest rate? This tutorial provides a comprehensive
guide to using the PV function with detailed examples. You'll learn
basic syntax, practical applications, and advanced techniques to master this
financial function.
PV Function Basics
The PV function discounts a series of equal payments and an optional
final balance back to today's money. It's used for loans, annuities, leases, and
other financial calculations, and its result always carries the sign of the cash
flow direction.
| Component | Description |
|---|---|
| Function Name | PV |
| Syntax | =PV(rate, nper, pmt, [fv], [type]) |
| Required Arguments | rate, nper, pmt |
| Optional Arguments | fv, type |
| Return Value | Present value of the investment |
This table breaks down the essential components of the PV
function. It shows the function name, syntax format, required and optional
arguments, and return value characteristics.
| Argument | Meaning |
|---|---|
| rate | Interest rate per period. Divide an annual rate by the number of periods per year, for example 5%/12 for monthly payments. |
| nper | Total number of payment periods. Multiply the years by the periods per year, for example 10*12 for a ten-year monthly schedule. |
| pmt | Payment made each period, constant over the life of the cash flow. Cash
paid out is negative and cash received is positive. If it is omitted, only
fv is discounted. |
| fv | Future value: the balance to reach after the last payment, such as a residual or balloon amount. When omitted it defaults to 0. |
| type | Payment timing: 0 or omitted for the end of a period, 1 for the beginning. |
The rate and nper arguments must use the same time
unit. Use a monthly rate with a count of months, or an annual rate with a count
of years, but never mix the two. Excel evaluates the function with the
closed-form equation
PV = -(fv + pmt*(1+rate*type)*((1+rate)^nper-1)/rate) / (1+rate)^nper,
and when rate is 0 it reduces to PV = -(fv + pmt*nper).
Basic PV Example - Loan Calculation
This example demonstrates calculating the present value of a loan with fixed payments. We'll determine how much can be borrowed based on payment capacity.
| Parameter | Value |
|---|---|
| Annual Interest Rate | 5% |
| Loan Term (Years) | 10 |
| Monthly Payment | $1,000 |
| Present Value | =PV(5%/12, 10*12, -1000) |
The table shows loan parameters and the PV formula to calculate the maximum borrowable amount. The payment is negative because it is cash you pay out, so the result comes back positive as cash you receive.
=PV(5%/12, 10*12, -1000)
This formula calculates how much you can borrow with $1,000 monthly payments over 10 years at 5% annual interest. The result is $94,281.35. The monthly rate is the annual rate divided by 12, and the number of periods is the years multiplied by 12.
Understanding the Sign Convention
PV follows Excel's cash-flow sign convention: money you pay out is
negative and money you receive is positive. Because the loan payments
(pmt) are entered as negative cash you pay, the returned present
value is positive cash you receive. The result normally has the opposite sign
of the net cash flow you enter.
=PV(5%/12, 10*12, -1000) ' 94281.35, you pay the money back =PV(5%/12, 10*12, 1000) ' -94281.35, you put the money in today
The size of the result is the same either way; only the sign changes. To show a
result as a positive number, wrap the formula in ABS:
=ABS(PV(5%/12, 10*12, -1000)) returns 94281.35.
PV Example - Retirement Savings Goal
This example shows how to calculate the lump sum needed today to meet a future retirement goal with regular withdrawals.
| Parameter | Value |
|---|---|
| Annual Return | 6% |
| Retirement Duration | 20 years |
| Annual Withdrawal | $50,000 |
| Required Lump Sum | =PV(6%, 20, 50000, 0, 1) |
The table illustrates retirement planning parameters. The PV function calculates the present value needed to support $50,000 annual withdrawals for 20 years. Type is 1 because the withdrawals occur at the beginning of each year, and the future value is 0 because nothing is left after the last payment.
=PV(6%, 20, 50000, 0, 1)
This formula returns -$607,905.82, the lump sum you must set aside today to fund 20 years of $50,000 annual withdrawals starting immediately. The minus sign is the money you pay in today; the withdrawals are positive because they are cash you receive. With end-of-period withdrawals the required lump sum is smaller: -$573,496.06.
PV Example - Comparing Investment Options
This example compares two savings options that both promise the same $100,000 goal. It shows how much has to be invested today to reach that goal under each interest rate.
| Option | Annual Return | Term | Future Value | Invest Today |
|---|---|---|---|---|
| A | 7% | 5 years | $100,000 | =PV(7%, 5, 0, 100000) |
| B | 5% | 5 years | $100,000 | =PV(5%, 5, 0, 100000) |
The table compares two options that both pay $100,000 in 5 years but earn
different returns. The goal is cash received at the end of the term, so
fv is entered as a positive 100000 and the negative results are the
cash that has to be paid in today.
=PV(7%, 5, 0, 100000) =PV(5%, 5, 0, 100000)
The first formula returns -$71,298.62 (Option A) and the second -$78,352.62 (Option B). Reaching the same $100,000 goal costs $7,054.00 less up front under the higher 7% return, so Option A is the cheaper way to the goal. The signs are negative because a present value here is money paid in, not received.
PV Example - Lease Evaluation
This example evaluates a lease agreement by calculating the present value of lease payments. It helps determine if leasing is better than buying.
| Parameter | Value |
|---|---|
| Monthly Payment | $800 |
| Lease Term | 3 years |
| Discount Rate | 4% annual |
| Residual Value | $5,000 |
| Present Value | =PV(4%/12, 36, -800, 5000) |
The table shows lease terms including monthly payments, term length, discount rate, and residual value. The payment is negative because it is cash you pay out, while the residual value is positive cash you receive at the end of the term.
=PV(4%/12, 36, -800, 5000)
This formula returns $22,661.13. The 36 payments of $800 are worth about $27,097 today and the $5,000 residual value is worth about $4,435, so the lease costs $22,661.13 in current money. Compare that figure with the cash price of the asset to decide between leasing and buying.
PV Example - Annuity Purchase Decision
This example helps decide whether to purchase an annuity by calculating the present value of its payments compared to its cost.
| Parameter | Value |
|---|---|
| Annual Payment | $10,000 |
| Payment Period | 15 years |
| Discount Rate | 5% |
| Annuity Cost | $100,000 |
| PV of Payments | =PV(5%, 15, 10000) |
The table compares an annuity's cost to the present value of its payments. The payments are positive because they are cash the purchaser receives, so the result comes back negative as the lump sum that would be paid today.
=PV(5%, 15, 10000)
This formula returns -$103,796.58, the present value of 15 annual $10,000 payments at a 5% discount rate. The magnitude, $103,797, is the fair price of the annuity, and it is $3,796.58 above the $100,000 asking price, so the annuity appears financially attractive based on these assumptions.
Annuity Due vs. Ordinary Annuity
An ordinary annuity pays at the end of each period; an annuity due pays at the
beginning. Setting type to 1 discounts each payment one period less,
so the present value of an annuity due is always the ordinary annuity value
multiplied by (1 + rate).
=PV(6%, 20, 50000, 0, 0) ' -573496.06, withdrawals at year end =PV(6%, 20, 50000, 0, 1) ' -607905.82, withdrawals at year start
The same $50,000 annual withdrawal costs $34,409.76 more when the money leaves
at the start of each year, because every withdrawal displaces one extra year of
growth: 573496.06 * 1.06 = 607905.82. Omitting type gives 0, the
ordinary annuity and the default.
PV with Zero Interest Rate
When the interest rate is 0, Excel does not fail; it uses the simple case
PV = -(fv + pmt*nper) and merely adds up the cash flows. This makes
PV useful for interest-free loans and savings plans.
=PV(0, 10, -500)
This formula values ten payments of $500 with no interest. The result is exactly $5,000.00, the same as 500 * 10, and it is positive because the payments are cash you pay out. The zero-rate case never divides by zero, so no special handling is required.
PV Compared with FV, PMT, and NPV
PV belongs to a family of four closely related time-value
functions. They share the same sign convention, but each one solves a different
problem.
| Function | Returns | Syntax |
|---|---|---|
| PV | Present value of a series of equal cash flows | =PV(rate, nper, pmt, [fv], [type]) |
| FV | Future value of a series of equal payments | =FV(rate, nper, pmt, [pv], [type]) |
| PMT | Payment per period | =PMT(rate, nper, pv, [fv], [type]) |
| NPV | Net present value of a different cash flow per period | =NPV(rate, value1, [value2], ...) |
PV, FV, and PMT take the same arguments
and are inverses of one another: PV discounts future cash flows
back to today, PMT solves for the periodic payment, and
FV compounds current values forward. The checks are exact:
=PV(5%/12, 60, PMT(5%/12, 60, 20000)) returns 20000, the loan
amount that the payment amortizes, and =PV(5%/12, 10*12, -100)
returns 9428.14 for a stream of $100 monthly deposits, which FV
compounds back to 15528.23 after ten years. NPV is the odd one out:
it accepts a different cash flow for each period, discounts the first one a full
period, and has no type argument. For a constant end-of-period
stream it returns exactly the opposite sign of PV, so
=NPV(10%, 100, 100, 100) returns 248.69 while
=PV(10%, 3, 100) returns -248.69. Any cash flow that occurs today
must be added outside NPV, because NPV discounts every
value it receives by at least one period.
PV Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| rate and nper use different time units | Result is far too high or too low | Match units, for example 5%/12 with 10*12 |
| An argument is text | #VALUE! error | Make sure the referenced cells contain numbers |
| rate is at or below -100% | #NUM! error | Use a realistic interest rate |
| Cash flows entered with the wrong sign | Result has the opposite sign to what you expected | Cash received is positive, cash paid is negative |
| fv omitted although a balance remains | Result ignores the residual or balloon balance | Enter the remaining balance as fv |
| type is a value other than 0 or 1 | Cash flows treated as beginning-of-period | Use exactly 0 or 1 |
| pmt and fv are both 0 or omitted | Result is 0 | Supply at least one cash flow |
A present value that looks wrong is almost always a unit mismatch or a sign
mistake. Using a 5% annual rate directly with a monthly nper
distorts the answer, and entering every cash flow with the same sign makes the
result point the wrong way. Convert the rate to the same period as
nper, keep inflows positive and outflows negative, and remember
that any non-zero type switches the schedule to an annuity due.
The PV function is the discounting core of Excel's financial
functions, turning any stream of equal future cash flows into one figure in
today's money. Use it to size loans, price annuities and leases, and compare
goals that pay off on different schedules. Two habits prevent nearly every
mistake: keep rate and nper in the same time unit, and
enter inflows as positive numbers and outflows as negative ones. With those
rules in place, PV returns a trustworthy number that can be set
directly against a price, a budget, or a competing offer.
Author
List all Excel Formulas.