Present Value Formula in Excel: A Practical Guide
Master the present value formula in Excel with the PV function. Learn syntax, sign conventions, and real-world cash-flow examples to discount future money accurately.
The present value formula in Excel calculates the current worth of a stream of future payments by discounting them at a specified rate. In Excel, use the PV function: =PV(rate, nper, pmt, [fv], [type]). This article shows how to apply PV to loans, annuities, and retirement cash flows, with practical examples.
What the present value means in finance
The concept of present value (PV) is fundamental in finance. PV answers the question: what is a future cash flow worth today, given a discount rate? In Excel, the PV function provides a compact way to perform this calculation for standard cash-flow patterns, such as regular payments or fixed future values. When you discount future money at a given rate, you're recognizing the time value of money and risk adjustments. This section introduces the key idea behind PV and why it matters in budgeting, project appraisal, and personal finance. The following example demonstrates the core syntax and how to interpret results.
=PV(0.05, 10, -1000, 0, 0)This formula assumes a 5% per-period discount rate, 10 periods, a fixed payment of -1000 per period, and no future value. The result represents the present value of receiving 1000 each period for 10 periods, discounted at 5%. Sign conventions matter: cash outflows (negative) vs inflows (positive) affect the PV sign. In practice, you typically enter payments as negative values to reflect outgoing cash, while the PV result is positive when the cash flows are favorable.
PV syntax and sign conventions
Excel's PV function uses five parameters: rate, nper, pmt, [fv], [type]. rate is the discount rate per period, nper is the number of periods, pmt is the payment per period (negative for cash outflows). fv is the optional future value at the end of the period; type indicates when payments are due: 0 end of period (default) or 1 beginning of period. Here are representative examples to illustrate the defaults and variations.
=PV(rate, nper, pmt)=PV(0.04, 12, -100, 0, 0)=PV(0.04, 12, -100, 1000, 1)Understanding sign conventions is crucial: payments you receive are typically positive, payments you make are negative. If you are modeling a loan, you usually enter payments as negative to reflect cash leaving your pocket, and the PV result will be positive, representing the amount you would need today to fund those payments.
Practical loan example: fixed-rate loan
Suppose you take a fixed-rate loan with monthly payments. The goal is to know how much (present value) you would need today to fund those payments. Let the annual rate be 6%, the term 30 years, and the monthly payment be -150. Convert the annual rate to a monthly rate and set nper = 30*12. The PV gives the loan amount you can borrow today.
=PV(0.06/12, 360, -150, 0, 0)If you expect a balloon payoff at the end (fv = 20000) while still paying monthly, you can model that as well:
=PV(0.06/12, 360, -150, 20000, 0)In this setup, the PV is the amount you borrow today, considering both monthly payments and the final balloon payment. Remember the sign convention: payments are negative, inflows (like a loan you receive) would be positive, and the computed PV reflects the liability you carry today.
PV with payment timing: end vs beginning of period
The timing of payments changes PV, even if the rate and payment amount stay the same. Use type = 0 for end-of-period payments (most common) and type = 1 for beginning-of-period payments. The difference may look small but can be material over long horizons.
# End of period payments (default)
=PV(0.04/12, 24, -500, 0, 0)
# Beginning of period payments
=PV(0.04/12, 24, -500, 0, 1)The second formula typically yields a slightly higher present value because payments start earlier, reducing the amount you need today to fund them. This nuance matters in lease calculations and annuity models.
PV with a known future value (fv)
Sometimes you know the target future value your investment should reach after the payments. The PV function accommodates a fixed future value (fv). This is useful for savings plans or retirement funding where you want to reach a target amount.
# Regular payments with a fixed future value
=PV(0.05, 20, -200, 10000, 0)
# Same with payments at the beginning of periods
=PV(0.05, 20, -200, 10000, 1)Here, fv represents the amount you want to have at the end of the horizon. A nonzero fv reduces the amount you must invest today if you’re aiming for that target. Pay attention to your sign conventions: an inflow target (fv) is typically positive, while payments remain negative for outflows.
Retirement cash flows: annuities and withdrawals
PV is a staple in retirement planning for estimating how much to save to fund periodic withdrawals. For a fixed monthly withdrawal, use a PV of the annuity. Compare end-of-period vs beginning-of-period withdrawals to model different withdrawal strategies.
# Monthly withdrawals of 1000 for 30 years at 5% annual discount, monthly rate
=PV(0.05/12, 30*12, -1000, 0, 0)
# If withdrawals occur at the start of each month
=PV(0.05/12, 30*12, -1000, 0, 1)These formulas provide rough estimates of the lump-sum needed at retirement to fund your withdrawal plan, assuming a constant rate. In practice, you’ll often combine PV with other models (e.g., dynamic rate scenarios) for more robust planning.
Validation and quick checks
To validate PV results, you can reverse the calculation using FV. If PV represents today’s funding, applying FV with the same rate and terms should recover the target future value (or confirm consistency). This is a common sanity check in financial modeling.
# Validate by reversing: compute FV from the PV amount
=FV(0.05/12, 360, -200, PV(0.05/12, 360, -200, 0, 0), 0)Another quick check is to test different sign conventions or to switch type from 0 to 1 and compare the PVs. If you change the sign of pmt while keeping rate and nper the same, you should see PV flip sign, which helps verify your inputs are interpreted as cash inflows vs outflows.
Putting PV into a practical worksheet workflow
In real worksheets, you’ll model PV alongside other metrics (NPV, IRR, and cash-flow tables). Create a small cash-flow table with yearly periods, forecasted payments, and then compute PV in a separate column to compare scenarios. This practice helps you build transparent, auditable models where PV sits next to rate, nper, and pmt inputs.
# Setup (example):
# A2:A6 = Year 1 to Year 5
# B2:B6 = Cash flows (-100, -100, -100, -100, -100)
# C1 = Rate (0.06)
=PV($C$1, ROW()-1, B2, 0, 0)Tip: Lock rate cells with absolute references and copy the PV formula down to build a quick PV-by-year view for sensitivity analysis.
Steps
Estimated time: 15-25 minutes
- 1
Identify inputs
List your rate per period, number of periods, payment per period, and any future value. Decide if payments occur at the end or beginning of periods.
Tip: Double-check the sign convention for pmt to reflect cash outflows correctly. - 2
Choose the PV variant
Decide whether you need FV (target future value) and the type (0 end, 1 beginning). This affects the PV output and interpretation.
Tip: If unsure, start with type 0 and adjust after initial results. - 3
Enter the PV formula
Enter =PV(rate, nper, pmt, [fv], [type]) in a blank cell and replace with your inputs.
Tip: Use absolute references for rate if you plan to copy the formula across a table. - 4
Review and interpret
Compare PV results across scenarios (different rates or payment amounts) to assess sensitivity.
Tip: Plot a small sensitivity chart to visualize PV changes. - 5
Validate with FV
Optional: compute FV using the PV value to verify consistency under the same rate and term.
Tip: A successful reverse calculation indicates model integrity.
Prerequisites
Required
- Required
- Basic knowledge of Excel formulas (sum, subtraction, etc.)Required
Optional
- Familiarity with rate per period conceptsOptional
- A sample cash-flow dataset to practice PVOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Edit active cellModify the current cell's formula or value | F2 |
| Copy selected cellCopy PV formula or results to clipboard | Ctrl+C |
| Paste into adjacent cellPaste formula or value into neighbor cell | Ctrl+V |
| Fill down to copy formulaExtend PV formula to multiple rows/periods | Ctrl+D |
| Insert function wizardOpen the function dialog for PV or other functions | ⇧+F3 |
People Also Ask
What is the present value formula in Excel?
The present value formula in Excel uses the PV function to determine today’s worth of a stream of future payments, given a discount rate, number of periods, and optional future value and payment timing. It is a standard tool for discounting cash flows in finance.
PV helps you see how much future money is worth today, given a rate and time horizon.
Why are payments entered as negative in the PV function?
In PV, cash outflows (payments you make) are represented as negative values to reflect money leaving your pocket. Inflows are positive. This convention ensures the PV result aligns with the sign of the cash flow stream.
Negative payments show money going out; positive PV reflects value of future inflows.
Can PV handle non-regular cash flows?
PV is ideal for regular, level payments or fixed future values. For irregular cash flows, consider using a cash-flow table with multiple PV calculations or switch to NPV/IRR methods that are designed for irregular timing.
PV is best for regular patterns; irregular flows may need more complex modeling.
What is the difference between PV and NPV?
PV discounts a single stream of cash flows to a present value. NPV sums multiple discounted cash flows, often with varying amounts and timings. PV is a component of NPV calculations.
PV gives a present value for one pattern; NPV aggregates many cash flows.
How do I factor in payments at the start of a period?
Set type to 1 to indicate payments occur at the beginning of each period. This increases the present value slightly compared with end-of-period payments at the same rate and pmt.
Use type = 1 to model payments at period start.
What if I don’t know the rate per period?
Convert any annual rate to a per-period rate before using PV (e.g., monthly rate = annual_rate/12). If rate is uncertain, run sensitivity analyses across a range of rates.
Convert annual to per-period rate and test multiple values.
The Essentials
- Master PV syntax in Excel and the role of sign conventions
- End-of-period vs beginning-of-period payments matter for PV
- PV can model loans, annuities, and retirement withdrawals
- Use FV to validate PV logic and ensure consistency
- Layer PV into broader cash-flow models for robust analysis