Excel Present Value Formula: PV Function and Examples

Formulas & functions · Updated November 18, 2026

In Excel, use the PV function to calculate the present value of future cash flows: it discounts them using a rate and number of periods. For example, to find today’s value of $10,000 received in three years at 5% per year, enter =-PV(B1,B2,0,B3) when B1 is 5%, B2 is 3, and B3 is 10,000; the result is about $8,638.38.

What the PV Function Calculates

Present value is the value today of money expected in the future. The calculation reflects the idea that a dollar available now can be saved or invested, while a dollar received later cannot be used until then. A discount rate expresses how much less a future amount is worth today under the assumptions in the calculation.

Excel’s PV function can calculate the value of a single future amount, a regular series of payments, or both together. Its arguments represent the rate per period, total number of periods, payment in each period, future value remaining after the last payment, and payment timing. The payment and future-value arguments are optional in Excel, but they must be included in the correct order if you provide later arguments.

  • Rate: The interest or discount rate for one period.
  • Nper: The total number of periods.
  • Pmt: A constant payment made each period, if there is one.
  • Fv: The balance or lump sum at the end of the final period.
  • Type: 0 for payments at the end of each period, or 1 for payments at the beginning. If omitted, Excel assumes 0.

The function assumes a constant rate and, for payments, equal amounts at regular intervals. It is therefore useful for many loan, savings, and annuity calculations, but it does not automatically account for fees, changing rates, irregular payment dates, or taxes.

Calculate the Present Value of One Future Amount

For a single future amount, set the payment argument to zero and put the future amount in the future-value argument. In this example, B1 contains 5%, B2 contains 3, and B3 contains 10,000. The formula returns a positive value for the present value:

=-PV(B1,B2,0,B3)

The result is approximately $8,638.38. This matches the calculation $10,000 divided by 1.05 raised to the third power. The minus sign before PV is there to make the displayed answer positive; it does not change the discounting calculation. Excel’s financial functions use cash-flow signs, explained below.

You can use the same setup with different inputs. For instance, changing B1 changes the discount rate, while changing B2 changes how long the money is discounted. The result is not a prediction of what an investment will earn. It is the value implied by the rate and timing assumptions you enter.

Value a Series of Regular Payments

PV is also commonly used to find the value today of a fixed payment stream. Suppose B1 contains an annual nominal rate of 6%, B2 contains 24 monthly payments, B3 contains a monthly payment of -300, B4 contains a future value of 0, and B5 contains 0 for end-of-month timing. The formula is:

=PV(B1/12,B2,B3,B4,B5)

The result is approximately $6,768.78. Dividing the annual rate by 12 converts it to 0.5% per month, matching the 24 monthly periods. The payment is entered as negative because it represents money paid out; PV returns the positive amount that is financially equivalent to those payments under the stated rate.

This example assumes the 6% rate is a nominal annual rate divided into monthly periods. If the rate supplied to you is an effective annual rate, dividing it by 12 is not generally the same as converting it to an effective monthly rate. Confirm what the stated rate means before choosing the periodic rate. For payments at the beginning of each month, use 1 as the type argument rather than 0; that timing increases the present value because each payment is made one period earlier.

Match the Rate and Periods

The most important setup rule is that rate and nper must use the same time unit. If periods are months, rate must be monthly; if periods are years, rate must be annual. A common error is entering an annual rate with a count of monthly payments, which discounts the cash flows too heavily or too lightly.

  • For annual payments over five years, use an annual rate and five periods.
  • For monthly payments over five years, use a monthly rate and 60 periods.
  • For quarterly payments over five years, use a quarterly rate and 20 periods.

These examples assume payments occur at regular intervals and that the rate remains constant. Loan contracts may define rates, compounding, payment dates, or fees differently. A spreadsheet calculation can illustrate the effect of assumptions, but it does not replace the lender’s official payment schedule.

Understand Excel’s Positive and Negative Signs

Excel’s financial functions follow a cash-flow convention: money received and money paid should have opposite signs. If you enter a positive future amount as an inflow, PV may return a negative present value because it treats the equivalent amount today as an outflow. That is mathematically consistent, even if a negative result is not the presentation you expected.

There are two straightforward ways to handle signs. You can enter the future inflow as positive and place a minus sign before the PV function, as in the lump-sum example, or enter it as negative and let PV return a positive result. For a loan viewed from the borrower’s perspective, money received at the start and payments made later have opposite signs. Choose a viewpoint, keep the signs consistent, and describe what a positive result represents in your sheet.

PV Compared with NPV and XNPV

PV is designed for a regular payment stream with equal payments and consistent intervals. If future cash flows vary by period, Excel’s NPV function is often more appropriate. NPV discounts the values supplied as periods 1, 2, 3, and so on; it does not treat the first supplied value as a cash flow at time zero. A starting investment made today must be added separately.

For example, suppose B1 contains a 10% discount rate and B2:B4 contain cash inflows of 1,000, 1,500, and 2,000 at the ends of years 1, 2, and 3. The formula =NPV(B1,B2:B4) returns approximately $3,651.39, the present value of those future inflows. If B5 contains an initial outlay of -4,000 at time zero, =NPV(B1,B2:B4)+B5 returns approximately -$348.61. The calculation discounts each future inflow once for each year between it and today.

When cash flows occur on irregular dates, XNPV is designed to discount them using the actual dates supplied rather than assuming evenly spaced periods. It requires a cash-flow range and a corresponding date range. Use the function whose timing assumptions match your data: PV for regular level payments, NPV for uneven amounts at regular intervals, and XNPV for dated, irregular cash flows.

Common Errors and Checks

If the result seems implausible, check the units, signs, and timing before changing the formula. A rate entered as 5 instead of 5% means 500% in Excel, while a text value that looks like a percentage may not behave as a numeric rate. Also check whether the payment is at the beginning or end of a period and whether the future value should be zero or a remaining balance.

  • Confirm that rate and nper refer to the same period length.
  • Check that regular payments are equal and occur at regular intervals before using PV.
  • Make sure the payment and future-value signs reflect cash flowing in or out from one consistent viewpoint.
  • Do not include a time-zero investment inside NPV’s future cash-flow range.
  • Compare a simple case against hand calculation or a trusted payment schedule.

Formatting can also mislead: a cell formatted as currency may round the displayed value even though Excel retains more decimal places. For a decision or reconciliation, inspect the underlying inputs and increase displayed decimals as needed. Rounding the result for display is different from rounding each period’s cash flow before discounting.

When PV Is Useful—and What It Leaves Out

PV can help compare amounts that occur at different times on a common date, estimate the current equivalent of a payment schedule, or explore how a chosen discount rate affects a result. It is a calculation tool, not a recommendation about whether a loan, purchase, or investment is worthwhile. Different discount rates can produce materially different present values, so the rate is an assumption to justify rather than a universal answer.

The basic function does not account for changing rates, transaction costs, taxes, inflation separately, or uncertain cash flows. Those factors may matter in a real-world comparison. If a loan has variable rates or irregular fees, a simple PV result may not reproduce its actual cost. Check the real terms with the lender or a qualified professional; this example is for spreadsheet illustration, not financial, tax, investment, or legal advice.

Frequently Asked Questions

What is the Excel formula for present value?
Use Excel’s PV function with a rate per period and a matching number of periods. For a single future amount, set the payment to zero and enter the amount as the future value. Signs determine whether Excel displays the equivalent amount as positive or negative.

How do I calculate the present value of a future lump sum?
Enter the discount rate, number of periods, zero for regular payments, and the future amount in the PV function. For example, $10,000 received in three years at 5% has a present value of about $8,638.38.

Why does Excel’s PV result show as a negative number?
Excel uses positive and negative values to distinguish cash received from cash paid. If the future amount is entered as a positive inflow, the equivalent present cash flow may appear negative. Reverse one cash-flow sign or negate the result if you want a positive displayed value.

Should I use PV or NPV?
Use PV for equal payments at regular intervals, such as monthly loan payments. Use NPV for cash flows that vary in amount but occur at regular intervals. Use XNPV when the cash flows have actual, irregular dates.

Does PV assume payments at the beginning or end of the period?
By default, PV assumes payments are made at the end of each period. Set the type argument to 1 for payments at the beginning. The timing matters because earlier payments are discounted for less time.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.