To calculate present value in Excel, use the PV function: =PV(rate,nper,pmt,[fv],[type]). For example, with a 5% annual rate and three end-of-year payments of $100, enter =PV(5%,3,-100,0,0); Excel returns about $272.32. The payment is negative because Excel uses cash-flow signs to distinguish money paid from money received.
What present value means
Present value (PV) is what a future amount or series of payments is worth in today’s dollars at a specified interest or discount rate. The idea is that money available now can be saved or invested, while money received later cannot be used until it arrives. A discount rate converts those future amounts into an equivalent value today.
For a single future amount, the basic calculation is future value divided by one plus the rate, raised to the number of periods. Repeating that discounting for each payment gives the value of a stream of cash flows. The rate and period count must match: an annual rate goes with annual periods, and a monthly rate goes with monthly periods. PV is a calculation based on the assumptions you enter, not a prediction that a future payment or return is guaranteed.
PV function syntax and arguments
In Excel for Microsoft 365 on Windows, the function syntax is PV(rate,nper,pmt,[fv],[type]). The square brackets in the function description mark optional arguments; do not type the brackets into the formula. Here is what each argument means:
- rate: The interest or discount rate for one period.
- nper: The total number of periods in the calculation.
- pmt: The fixed payment made or received in each period. Use 0 if there is no repeating payment.
- fv: The balance or lump sum remaining after the final payment. If omitted, Excel assumes 0.
- type: The timing of each payment: 0 or omitted means payments occur at the end of each period; 1 means they occur at the beginning.
For example, if a contract has quarterly payments for five years, there are 20 periods. The rate argument should be the quarterly rate, not an annual rate left unchanged. Likewise, if the stated annual rate is 6% and the calculation uses monthly periods, a common nominal-rate setup is 6% divided by 12, with 60 periods over five years. The contract’s actual rate convention may differ, so check its terms before relying on the result.
Set up a basic PV calculation
For a straightforward calculation, identify the payment amount, payment frequency, number of payments, rate per period, and payment timing before entering the formula. In Microsoft 365 on Windows, type the formula into a cell and press Enter. The same PV function is available in Excel for Mac and Excel for the web; the calculation is the same, although menus and regional settings can differ. Some regional settings use semicolons rather than commas between formula arguments.
Suppose you expect to receive $100 at the end of each year for three years, and you use a 5% annual discount rate. With the annual rate in B1, the number of annual payments in B2, and the payment amount in B3, enter 5% in B1, 3 in B2, and -100 in B3. Because the expected receipts are cash coming to you, the payment is entered as negative so that the present value appears positive. With that layout, enter:
=PV(B1,B2,B3,0,0)
The result is approximately $272.32. This is the value today of three $100 payments received at the ends of years one, two, and three, discounted at 5% per year. It is not $300 because the later payments are discounted for longer. A result may display with fewer decimal places depending on the cell’s number format; changing the display format does not change the underlying value.
Calculate the present value of one future amount
PV can also calculate the value today of a single future lump sum. Set the payment argument to zero, place the future amount in the fv argument, and use the opposite sign for that amount if you want a positive present value. For instance, if B1 contains a 5% annual rate, B2 contains 3 years, and B3 contains a future balance of 1,000, use:
=PV(B1,B2,0,-B3,0)
The result is about $863.84. That is the value today of $1,000 due in three years when discounted at 5% annually: 1,000 divided by 1.05 to the third power. This example assumes the amount is received exactly three annual periods from now and that the selected rate is appropriate for the purpose of the calculation.
You can combine a repeating payment with a final balance. For example, a savings arrangement might involve regular contributions and a target account balance at the end. Enter both streams in PV’s pmt and fv arguments, using signs that reflect whether each amount is paid out or received. Check the result against the cash-flow direction you intend to model; a positive or negative PV is not automatically an error.
Match the rate and number of periods
One of the most consequential setup choices is keeping the rate, payment, and period count on the same schedule. If payments are monthly, nper counts months and rate represents a monthly rate. If payments are annual, nper counts years and rate is annual. A mismatch can make an answer dramatically too high or too low even when the formula itself is valid.
For a nominal annual rate quoted as 6% with monthly payments, dividing by 12 gives a monthly rate of 0.5% for a standard monthly calculation. A five-year term then has 60 monthly periods. But an annual percentage yield, an effective annual rate, or a loan rate with a particular compounding convention may need a different conversion. Do not assume that every figure described as an annual rate should simply be divided by 12; follow the rate definition used by the agreement or analysis.
Also confirm whether a payment is made at the beginning or end of each period. Most loan payments are modeled as end-of-period payments, so type is 0. Rent, deposits, or contributions made in advance may instead be modeled at the beginning, using type 1. Beginning-of-period payments have a higher present value than otherwise identical end-of-period payments because each payment is received or made earlier.
Understand cash-flow signs and payment timing
Excel’s financial functions follow a cash-flow convention: cash paid out is usually negative and cash received is positive. PV solves for a value that balances the other cash flows. If you enter a positive payment amount, Excel will commonly return a negative PV. That sign indicates the opposite cash-flow direction; it does not mean the arithmetic failed.
For a savings plan, contributions you make are outflows, while the future balance you receive is an inflow. For a loan, the money advanced to you is an inflow, while repayments are outflows. Keep the signs consistent with the perspective you are modeling. If you change every sign, the answer’s sign changes too, while its magnitude remains the same under the same assumptions.
The type argument affects when each repeating payment occurs, not the rate’s compounding method. With type 0, payments occur at the end of the first period and each later period. With type 1, the first payment occurs immediately, at the beginning of the first period. If you omit type, Excel treats it as 0. This default is easy to overlook, so make the timing explicit when it matters to your result.
PV versus NPV for uneven cash flows
PV is designed for a constant payment amount repeated at regular intervals, with an optional final balance. If future amounts vary from period to period, Excel’s NPV function is often more suitable for regularly spaced cash flows. For example, with a 5% discount rate and amounts of $100, $200, and $300 in cells B2:B4 for the ends of years one through three, enter:
=NPV(5%,B2:B4)
The result is approximately $535.80. Excel discounts the first amount by one period, the second by two, and the third by three. A key distinction is that Excel’s NPV function treats the first value in its list as a cash flow at the end of period one; it does not treat that value as an amount at today’s date. If you also have an initial outlay at time zero, account for it separately rather than including it as the first value in the NPV range.
For irregularly dated cash flows, a fixed-period PV or NPV calculation may not reflect the actual time between dates. Excel also provides XNPV for cash flows associated with dates. Choose a method that matches the timing data you actually have; comparing a result from one timing assumption with a result from another can be misleading.
Common PV errors and how to fix them
- Using an annual rate with monthly periods: Convert the rate to the period basis that matches the payments, using the rate convention specified for the calculation.
- Counting years instead of payments: For monthly payments over five years, nper is normally 60, not 5.
- Getting a negative answer: Check the signs of pmt and fv. Excel’s result sign reflects the cash-flow convention; it is not necessarily a formula error.
- Using the wrong type: Confirm whether payments happen at the beginning or end of a period. If uncertain, check the actual payment schedule.
- Expecting PV to handle changing payments: PV assumes a fixed pmt. Use a function or a discounted cash-flow layout suited to variable amounts.
- Forgetting the final balance: If a balance remains after the last payment, enter it as fv instead of assuming the account or loan ends at zero.
When a result looks surprising, test the inputs one at a time: verify rate units, total periods, cash-flow signs, the future balance, and timing. A useful check is to compare a simple case with the underlying discounting calculation or a trusted set of contract figures. Display enough decimal places while checking, since rounded cells can hide small differences.
Frequently Asked Questions
What is the Excel formula for present value?
Use =PV(rate,nper,pmt,[fv],[type]). Enter a rate per period and a number of periods on the same schedule; use the optional arguments for a final balance and payment timing.
Why does PV in Excel show a negative number?
Excel uses positive and negative signs to represent cash received and cash paid. If your payment is entered as a positive amount, the balancing present value may be negative. Reverse the cash-flow signs if you want the opposite perspective, while keeping the direction of each payment clear.
Should type be 0 or 1 in the PV function?
Use 0, or omit the argument, when payments occur at the end of each period. Use 1 when payments occur at the beginning. The correct choice depends on the real schedule, not on which result looks preferable.
Can I use PV in Excel without regular payments?
Yes. Set pmt to 0 and use fv for the single future amount. For example, with a 5% annual rate, a three-year term, and a future amount of 1,000 in B3, =PV(5%,3,0,-B3) returns about 863.84 when B3 contains 1000.
Is PV the same as NPV in Excel?
No. PV handles a fixed payment stream and an optional final balance. NPV discounts a series of cash flows that can vary but are spaced at regular intervals, and its first listed cash flow is treated as occurring one period in the future. Check timing carefully when choosing between them.
PV calculations are useful for comparing amounts on a consistent date, but the answer depends on the chosen rate, period, timing, and cash-flow assumptions. For a real loan, savings product, investment, or other financial decision, verify the terms and assumptions with the lender or a qualified professional; this calculation is illustrative, not financial, tax, investment, or legal advice.
Related
- Basic Cash Flow Template in Excel: Build and Use It
- Autofit Column Width in Excel: Steps, Shortcuts & Fixes
- Automatically Transpose Data in Excel: Methods That Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Auto Number Rows in Excel: Formulas and FillTo auto number rows in Excel, enter a row-based formula in the first cell of your numbering column…
- Excel XLOOKUP Between Two Worksheets: ExamplesIf your lookup value is on one worksheet and the data to search is on another, XLOOKUP can…
- Excel Rank No Ties? Use RANK.EQ and COUNTIFYes, Excel’s RANK.EQ function assigns the same rank to tied values by default, so two identical scores both…
- Round Up in Excel: Use ROUNDUP for Any Number or DecimalTo round a value in Excel away from zero, use =ROUNDUP(A2,0) when the number is in cell A2…
- COUNTIF Greater Than in Excel: Formula and ExamplesTo count values greater than a number in Excel, use =COUNTIF(B2:B20,">100"), assuming the values are in B2:B20 and…
- Excel Formula for Calendar Days in a MonthThe simplest Excel formula for calendar days in a month is =DAY(EOMONTH(A1,0)), where A1 contains any date within…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.