Using Excel's PV Function
The PV function is the inverse of FV: instead of asking "How much will my money grow?", it asks "How much is a future payment worth today?" This is the foundation of discounted cash flow (DCF) valuation, used by investors to price stocks, bonds, and real estate. Understanding PV transforms how you evaluate investment opportunities, compare loan offers, and make major financial decisions. Where FV compounds value forward in time, PV discounts it backward, revealing what future money is actually worth in today's dollars.
Quick Definition
The PV function calculates the present value (today's worth) of a future lump-sum payment or a series of equal periodic payments, assuming a constant interest rate and compounding frequency. The function syntax is:
=PV(rate, nper, pmt, fv, type)
Where rate is the discount rate per period, nper is the total number of periods, pmt is the regular periodic payment (optional), fv is a future lump-sum amount (optional), and type is 0 (payment at end of period) or 1 (payment at beginning). Like FV, PV returns a negative number by convention and is often wrapped in a minus sign for readability.
Key Takeaways
- PV discounts future cash flows to present value, the foundation of DCF valuation
- The discount rate reflects the time value of money—roughly your required return or opportunity cost
- PV works for single lump-sum payments, annuities (regular payments), or a combination
- Rate and nper must be in consistent time units (both monthly, both annual, etc.)
- A higher discount rate produces a lower PV (future money is worth less today if your alternatives are better)
- PV is essential for comparing loan offers, evaluating investment opportunities, and pricing bonds
- Common errors include mismatching time units, using incorrect discount rates, and confusing lump-sum vs. annuity scenarios
- PV, FV, NPER, RATE, and PMT are complementary; mastering all five unlocks complete financial analysis
- Real-world applications include bond pricing, mortgage evaluation, rental property analysis, and retirement income planning
Understanding Present Value and Discounting
Decision tree
Present value answers the question: "If I receive $X in the future, what is it worth to me today?" The answer depends on your discount rate—the return you could earn on an alternative investment with similar risk.
If your discount rate is 10% annually and someone offers you $110 next year, that's worth exactly $100 today (since $100 × 1.10 = $110). If they offer you $110 in 2 years, it's worth less: $110 / (1.10)^2 ≈ $91 today. The longer you wait and the higher your discount rate, the less future money is worth.
Discount rates vary by context. For a safe savings account earning 4%, your discount rate might be 4% (you'd not give up a dollar today unless promised at least $1.04 in a year). For a risky startup investment, your discount rate might be 25% (you demand high returns to compensate for risk). The PV function uses whatever discount rate reflects your situation.
The PV Function Syntax and Parameters
Breaking down =PV(rate, nper, pmt, fv, type):
Rate: The discount rate per period. If you expect 8% annual returns and you're looking at monthly cash flows, rate = 0.08/12. This is the return you'd require from an alternative investment. The interpretation differs slightly from FV's compound rate, but the mathematics is the same: it's the per-period rate.
Nper: The total number of periods. If you're evaluating payments or returns over 10 years with monthly compounding, nper = 120. Time units must align with rate (months with monthly rate, years with annual rate).
Pmt: The regular periodic payment. This is optional and defaults to 0. If you're evaluating an annuity (like a pension that pays $1,000/month), pmt = -1000 (negative by convention—the payment is money you'll receive, flowing to you). If you're evaluating a single lump-sum payment with no regular payments, leave this blank or enter 0.
Fv: A future lump-sum amount, separate from any regular payments. This is optional and defaults to 0. If someone promises you $100,000 in 10 years plus $1,000 monthly payments, fv = -100000, pmt = -1000. If it's just the lump sum, fv = -100000, pmt = 0.
Type: Either 0 (payment at end of period, the default) or 1 (payment at beginning of period). For most financial instruments, type = 0. Bonds pay interest at the end of each period; savings accounts credit interest at period end. Use type = 1 only for annuities where payments are made in advance (like lease payments or insurance premiums due at the start of each month).
By convention, PV returns a negative number (the present value is the amount you'd pay today for those future cash flows). Wrap it in a minus sign—=-PV(...)—to get a positive result, or enter future cash flows as positive, trading clarity for sign consistency.
The Mathematics Behind PV
For a single future lump-sum payment:
PV = FV / (1 + rate)^nper
This is discounting: divide the future amount by (1 + rate)^nper, which compounds the discount rate forward to show how much less future money is worth.
For an annuity (regular equal payments):
PV = PMT × [1 - (1 + rate)^(-nper)] / rate
Excel combines both in its PV function, so you can evaluate a complex cash flow stream with lump sums and regular payments simultaneously.
Worked Example 1: Single Future Payment
You're offered $50,000 in 5 years. If your discount rate (opportunity cost) is 7% annually, what's it worth today?
Formula:
=-PV(0.07, 5, 0, -50000, 0)
Breakdown:
- Rate = 0.07 (7% annual)
- Nper = 5 (years)
- Pmt = 0 (no regular payments, just a lump sum)
- Fv = -50000 (future amount of $50,000)
- Type = 0 (irrelevant without payments)
Result: $35,717.49
This means the $50,000 you'll receive in 5 years is equivalent to $35,717 today (at your 7% discount rate). If you had $35,717 now, you could invest it at 7% and have $50,000 in 5 years.
To verify: $35,717 × (1.07)^5 = $50,000. ✓
Worked Example 2: Annuity (Regular Payments Only)
You're evaluating a pension that will pay you $2,000/month for the next 20 years. If your discount rate is 6% annually, what's the pension worth today?
Formula:
=-PV(0.06/12, 20*12, -2000, 0, 0)
Breakdown:
- Rate = 0.06/12 = 0.005 (0.5% monthly)
- Nper = 20 * 12 = 240 months
- Pmt = -2000 (monthly payment to you; negative by convention)
- Fv = 0 (no lump sum after the annuity ends)
- Type = 0 (payments at end of month)
Result: $331,142.61
The $2,000/month for 20 years is worth $331,143 today at your 6% discount rate. If you had $331,143 now, you could invest it, withdraw $2,000/month, and have roughly zero left after 20 years (ignoring account fees).
Worked Example 3: Combination of Lump Sum and Annuity
You're evaluating an insurance settlement: $100,000 today (which you'll receive immediately, so it's not discounted), plus $5,000/month for 10 years, plus a final $50,000 at year 10. You require 5% annual returns. What's the total present value of the settlement?
Future payments to discount:
- Monthly payments: $5,000/month for 10 years
- Final lump sum: $50,000 at year 10
Formula:
=-PV(0.05/12, 10*12, -5000, -50000, 0)
Breakdown:
- Rate = 0.05/12 ≈ 0.004167 (monthly rate)
- Nper = 120 months
- Pmt = -5000 (monthly payment)
- Fv = -50000 (lump sum at end)
- Type = 0
Result: $537,817.28
The present value of future payments is $537,817. Add the $100,000 you receive immediately: total value = $637,817.
This settlement is worth about $637,817 in today's dollars, accounting for the time value of money at your 5% required return.
Worked Example 4: Comparing Loan Offers
You need a $200,000 loan. Two banks offer:
Bank A: 5% annual interest, monthly payments of $1,073.64 over 30 years (360 months).
Bank B: 4% annual interest, monthly payments of $954.83 over 30 years (360 months).
To compare, calculate the present value of all payments you'll make to each bank. In principle, both should equal $200,000 (since that's what you borrowed), but let's verify and see the true cost of each loan.
Bank A:
=-PV(0.05/12, 360, -1073.64, 0, 0)
Result: $200,000 (by design—this is a loan payment calculator's output).
Bank B:
=-PV(0.04/12, 360, -954.83, 0, 0)
Result: $200,000 (same).
Both present-value to the loan amount, so the comparison is simpler: Bank B has a lower interest rate and lower monthly payment. The total interest paid is roughly $200,000 more over 30 years with Bank A (5% vs. 4%), so Bank B is cheaper.
However, PV is more useful when comparing a loan with upfront fees. If Bank A charges a 1% origination fee ($2,000) but Bank B charges 0.5% ($1,000), you'd include those fees in the PV calculation to find the true cost of each loan.
Worked Example 5: Evaluating a Real Estate Investment
You're considering buying a rental property. The property costs $400,000. You expect to collect $3,000/month in rent for 20 years, at which point you'll sell the property for $600,000. Your required return on real estate investments is 8% annually. Is this a good deal?
Formula:
=-PV(0.08/12, 20*12, -3000, -600000, 0)
Breakdown:
- Monthly rental income: $3,000
- Final sale price: $600,000
- Discount rate: 8% annual
- Time horizon: 20 years
Result: $422,715.84
The present value of all future cash flows (rent plus sale) is $422,716. But the property costs $400,000 today. Since $422,716 > $400,000, the investment appears profitable: you're paying $400,000 for something worth $422,716, a gain of $22,716.
However, this ignores property taxes, maintenance, vacancy periods, and capital gains taxes on the sale, which would reduce the actual present value. A complete analysis requires adjusting these factors.
Building a Bond Valuation Model
PV is the core function for valuing bonds. A bond is simply a series of interest payments (coupons) plus a face value (par) repayment at maturity. If a $1,000 bond pays 5% annual interest (two $25 semi-annual coupons) over 10 years, what's it worth if prevailing interest rates are 6%?
Formula:
=-PV(0.06/2, 10*2, -25, -1000, 0)
Breakdown:
- Rate = 0.06/2 = 0.03 (3% per six-month period)
- Nper = 20 (ten years, two periods per year)
- Pmt = -25 (semi-annual coupon payment)
- Fv = -1000 (face value repaid at maturity)
- Type = 0
Result: $926.40
The bond is worth $926.40. It's trading at a discount to par because prevailing rates (6%) exceed the bond's coupon (5%). If rates were 5%, the bond would be worth par ($1,000). If rates were 4%, it would trade at a premium above par. This inverse relationship between rates and bond prices is fundamental to fixed-income investing.
Common Mistakes and How to Avoid Them
Mistake 1: Wrong discount rate Using 3% when your true required return is 8% produces a too-high PV. Be honest about your opportunity cost. What return could you earn on an alternative, equally risky investment? Use that as your discount rate.
Mistake 2: Mismatching time units If rate is annual (0.08) but nper is in months (120), you're compounding 8% per month instead of annually, producing a nonsensical result. Always align: annual rate with years, monthly rate with months.
Mistake 3: Entering positive fv or pmt when they should be negative By convention, cash flows you'll receive (future payments, annuity income, sale proceeds) are entered as negative. Cash flows you'll make (loan payments, investments) are positive (or omitted). Reversing the signs flips the result.
Mistake 4: Forgetting to adjust the annual rate for compounding frequency If the annual rate is 8% but you're discounting monthly, use 0.08/12, not 0.08. The latter compounds 8% per month, equivalent to 151% annually—wrong.
Mistake 5: Confusing PV with net present value (NPV) NPV is PV of inflows minus cost of the investment. For a $400,000 real estate purchase with $422,716 PV of future cash flows, NPV = $422,716 - $400,000 = $22,716. PV alone doesn't tell you if an investment is good—you must subtract the cost.
Mistake 6: Using PV for variable-rate instruments PV assumes a fixed discount rate. If interest rates are expected to change (e.g., an adjustable-rate mortgage), standard PV won't work—you need a more complex model.
Mistake 7: Ignoring taxes and fees The PV of a bond, annuity, or investment is often quoted pre-tax. Your actual after-tax present value is lower. For real estate, property taxes and maintenance reduce the PV of rental income. Always adjust for these.
Real-World Examples
Example 1: Evaluating a Pension Buyout Your pension fund offers a lump-sum buyout: they'll pay you $500,000 today instead of $3,500/month for 30 years. Which is better if your discount rate is 5%?
PV of the monthly payments:
=-PV(0.05/12, 30*12, -3500, 0, 0)
Result: $669,733.
The pension's future payments are worth $669,733 in today's dollars at your 5% discount rate. The $500,000 lump sum is less, so the monthly pension is more valuable. (Of course, you'd also consider life expectancy, investment skill, and other factors.)
Example 2: Evaluating a Loan Prepayment You have a $150,000 mortgage at 4% annual interest, 15 years remaining. A lender offers to pay off the loan if you accept $120,000 today (a refinance at a lower principal). Should you take it?
The PV of remaining payments at 4%:
=-PV(0.04/12, 15*12, -1,109.53, 0, 0) // assuming $1,109.53/month payment
Result: ≈$150,000 (by design, since this is the loan's original principal).
The offer of $120,000 is much less than $150,000, so you'd be overpaying. Don't take it. (Of course, if the offer included other terms, like a lower future rate, you'd recalculate.)
Example 3: Evaluating a Business Acquisition You're considering buying a business with projected earnings of $50,000/year for 10 years, then selling it for $400,000. Your required return is 12%. What's the business worth?
=-PV(0.12, 10, -50000, -400000, 0)
Result: $381,826.
The business is worth $381,826 in present value terms. If the asking price is $350,000, it's a good deal (NPV = $381,826 - $350,000 = $31,826 positive). If the asking price is $450,000, it's overpriced.
Example 4: Evaluating a Lottery Payout You win a lottery with two payout options:
- Option A: $1 million immediately
- Option B: $50,000/year for 30 years
If your discount rate is 4%, which is worth more?
=-PV(0.04, 30, -50000, 0, 0)
Result: $916,061.
Option B is worth $916,061 in present value. Option A (immediate $1 million) is better by about $83,939. This assumes you could invest the $1 million at 4% annually; the difference reflects the time value of money.
Advanced: Sensitivity Analysis with PV
Create a table showing how changes in the discount rate affect PV. In a spreadsheet, set up discount rates (4%, 5%, 6%, 7%, 8%) across the top and time horizons (5, 10, 15, 20 years) down the side. For a fixed annuity of $1,000/month:
| Years \ Rate | 4% | 5% | 6% | 7% | 8% |
|---|---|---|---|---|---|
| 5 | 55,733 | 54,546 | 53,386 | 52,251 | 51,138 |
| 10 | 101,041 | 97,313 | 93,847 | 90,625 | 87,623 |
| 15 | 135,795 | 127,646 | 120,263 | 113,555 | 107,426 |
| 20 | 161,834 | 148,856 | 137,629 | 127,844 | 119,369 |
This table shows PV is highly sensitive to discount rate assumptions (varying by 40%+ across the range) and moderately sensitive to time horizon. Use this to stress-test your assumptions.
Frequently Asked Questions
Q: What's a reasonable discount rate? A: It depends on risk and alternatives. For safe investments (U.S. Treasury bonds), 2–4% is reasonable. For stocks, 7–10%. For risky business ventures or startups, 15–30% or higher. Use the return you could earn on a similar-risk alternative investment.
Q: Is PV the same as price? A: PV is what something should be worth in theory. Price is what someone is willing to pay. If a stock's PV is $100 but it trades for $80, it may be undervalued. If it trades for $120, it may be overvalued. The difference between PV and price is where investing opportunities lie.
Q: Can I use PV for perpetuities (infinite annuities)? A: Yes, technically. For a perpetuity of $X/year at a discount rate r, PV = X/r. So a $10,000/year perpetuity at 5% is worth $10,000/0.05 = $200,000. However, Excel's PV function requires a finite nper, so you'd use the formula instead of the function.
Q: What if the discount rate changes mid-stream? A: Standard PV can't handle this. You'd split the calculation into multiple periods with different rates, or build a period-by-period model. This is necessary for some complex scenarios but adds complexity.
Q: How is PV different from IRR (internal rate of return)? A: PV calculates the present value of cash flows given a known discount rate. IRR is the discount rate that makes PV equal to zero (break-even return). IRR is the "mystery" you're solving for; PV is the value given a known rate. They're complementary.
Q: Can I use PV for annuities that grow (e.g., 2% annual growth)? A: Standard PV can't handle growing annuities. You'd need a modified formula: PV = PMT × [1 - ((1+g)/(1+r))^n] / (r - g), where g is the growth rate. Or build a year-by-year model.
Related Concepts
Net Present Value (NPV) is PV of benefits minus costs, the standard for evaluating investments. Discounted Cash Flow (DCF) valuation uses PV to estimate the intrinsic value of a stock, bond, or business. Internal Rate of Return (IRR) solves for the discount rate that makes NPV zero. Time value of money is the principle underlying all of these: money today is worth more than money tomorrow.
Summary
Excel's PV function calculates the present value of future cash flows—what a stream of future payments or a lump-sum amount is worth in today's dollars. It's the inverse of FV and the foundation of discounted cash flow valuation, used by investors, analysts, and financial planners to price securities, evaluate investments, and compare financial options.
The function is straightforward: discount future cash flows backward using a rate that reflects your opportunity cost or required return. Higher discount rates produce lower PV (future money is worth less if your alternatives are better). Correct use requires aligning rate and nper (both monthly, both annual) and understanding sign conventions (future cash flows you receive are negative, your upfront investment is positive or omitted).
Whether you're evaluating a pension, comparing loan offers, valuing a rental property, or pricing a bond, PV is indispensable. Combined with FV, NPER, RATE, and PMT, it forms a complete toolkit for time-value-of-money analysis in spreadsheets.