Excel PV Formula for Different Payments

Excel’s PV function is designed for a fixed payment repeated over a set number of periods, so it cannot directly value a schedule in which each payment is different. For varying payments, discount each cash flow to today and add the results, or use NPV for equally spaced periods and XNPV for dates that are not evenly spaced.

What the PV function calculates

Present value is the value today of money expected in the future, after allowing for a chosen discount rate. A dollar received later is worth less than a dollar received now when the rate is positive, because the money available today could earn a return or because future money carries time and risk. A present-value calculation converts future amounts into a comparable value at one reference date.

Excel’s PV function takes a rate per period, a number of periods, a regular payment amount, an optional future value, and an optional payment-timing setting. Its payment argument, named pmt, is a single repeated amount. That makes PV a good fit for a level-payment loan, lease, or annuity, but not for a list such as 1,000 in year one, 1,500 in year two, and 2,000 in year three.

For a constant payment, with the annual rate in E1 set to 8%, three annual payments of 1,000 made at each year-end, and no separate final balance, enter =PV(E1,3,-1000,0,0). The result is approximately 2,577.10. The payment is entered as negative so the present value appears positive; Excel uses cash-flow signs to distinguish money received from money paid.

Build a worksheet for changing payments

A transparent worksheet keeps the timing, amounts, and discount rate separate. For an example with three end-of-year payments, arrange the cells like this:

A2:A4: periods 1, 2, and 3.
B2:B4: payments 1,000, 1,500, and 2,000.
E1: annual discount rate of 8%, entered as 8% or 0.08.

With the period numbers in A2:A4, the payment amounts in B2:B4, and the rate in E1, calculate each payment’s value today with =SUMPRODUCT(B2:B4/(1+$E$1)^A2:A4). The result is approximately 3,799.60. The exponent in each row is that payment’s number of periods from today: the first payment is discounted once, the second twice, and the third three times.

This method follows the underlying calculation directly: divide each cash flow by one plus the periodic rate raised to its period, then sum the discounted amounts. It is especially useful when payments change, periods are skipped, or you need to inspect the contribution of each payment. You can also add a column for present value: with periods in A2:A4, payments in B2:B4, and the rate in E1, enter =B2/(1+$E$1)^A2 in C2 and fill it down. The three values in column C will add to the same total.

Use NPV for regular periods

When the cash flows occur at regular intervals, Excel’s NPV function is a shorter option. NPV discounts the first value in its list by one period, the next value by two periods, and so on. With the annual rate in E1 and the three year-end payments in B2:B4, enter =NPV(E1,B2:B4). It returns approximately 3,799.60, matching the SUMPRODUCT calculation.

The important detail is that NPV treats its first listed value as occurring at the end of period one. It does not treat the first value as money received today. If a cash flow occurs immediately at time zero, add it separately rather than putting it into the NPV list. For example, with an initial outlay in B1 and future flows in B2:B4, the net present value is the initial amount plus the discounted future flows. With the initial outlay in B1, the rate in E1, and future payments in B2:B4, enter =B1+NPV(E1,B2:B4). Use a negative initial outlay, such as -5,000, if it represents money paid today.

NPV also assumes that the entries represent consecutive periods. If payments happen in years 1, 2, and 5, placing only those three amounts in the NPV range would incorrectly treat them as years 1, 2, and 3. Use the explicit period-based SUMPRODUCT approach instead, or include zero values for the missing periods so that each position in the range corresponds to the intended year.

Use XNPV when dates are irregular

Some payment schedules do not fall neatly at monthly or annual intervals. Examples include milestone payments, uneven invoice dates, or a project that receives cash at irregular points. Excel’s XNPV function discounts cash flows according to their actual dates, using a 365-day year. Its values and dates ranges must have the same number of entries, and the dates must be valid Excel dates.

To make the value correspond to a valuation date, include that date as the earliest date in the schedule with a cash flow of zero, followed by the later payment dates and amounts. With the valuation date in A2, zero in B2, later dates in A3:A5, their cash flows in B3:B5, and the annual rate in E1, enter =XNPV(E1,B2:B5,A2:A5). Because the first date is the valuation date and its value is zero, the result discounts the later cash flows back to that date. If the first listed date instead belongs to a future payment, XNPV uses that earliest date as its starting point, which may not be the date you intended.

XNPV is not interchangeable with NPV: XNPV uses the date gaps between cash flows, while NPV assumes equal period spacing. For monthly payments on the same day of each month, NPV may be convenient if the rate is a monthly rate and each entry represents one month. For dates that vary, XNPV more closely reflects the calendar schedule. Check the rate’s time basis as well: an annual rate should be used with annual periods in NPV, while a monthly schedule needs a rate expressed per month.

Choose the right timing convention and rate

The payment timing changes present value. In PV, the optional type argument is 0 for payments at the end of each period and 1 for payments at the beginning. An ordinary loan installment is commonly modeled at period-end, while rent paid at the start of a month may be modeled at period-beginning. Moving all payments one period earlier increases their present value when the discount rate is positive.

With different payments, the same timing issue applies even though PV is not the right function. If the first payment arrives immediately, its period is zero and it is not discounted; payments one and two periods later use exponents one and two. With periods in A2:A4, payments in B2:B4, and the rate in E1, the explicit calculation remains =SUMPRODUCT(B2:B4/(1+$E$1)^A2:A4), provided A2:A4 contains the actual period numbers, such as 0, 1, and 2 for payments at the beginning of three consecutive periods.

Use a rate that matches the cash-flow frequency. If payments are monthly, a nominal annual rate divided by 12 can be used as a monthly rate only when that is the convention specified for the calculation. An effective annual rate has a different relationship to a monthly rate. The appropriate rate depends on the stated terms and assumptions, so do not mix annual rates with monthly periods without converting consistently.

Common errors and checks

Including today’s cash flow inside NPV: NPV discounts its first listed value by one period. Keep a time-zero amount outside the function and add it separately.
Using the wrong units: A rate of 8% per year does not match monthly periods unless converted to the appropriate monthly basis.
Leaving out time gaps: NPV assumes consecutive periods. Use zero placeholders or explicit period numbers where a year or month is skipped.
Reversing signs: A positive result is not automatically correct or incorrect. Decide whether payments are outflows or inflows and use consistent signs. The sign convention affects the displayed sign, not the underlying discounting logic.
Entering dates as text: XNPV needs actual Excel date values. If a date looks right but is stored as text, convert it to a real date before relying on the result.
Rounding each payment too soon: Keep full precision in the calculation and format the final result as currency or to the desired decimal places. Rounding intermediate cash flows can change the total.

For a quick reasonableness check, a positive future payment discounted at a positive rate should have a present value below its undiscounted amount. A set of positive payments should produce a positive total if all are entered as positive inflows. If the result differs greatly from expectations, verify the rate, periods, signs, and whether the first payment is immediate or delayed.

Which method should you use?

Choose based on whether the payments repeat, how often they occur, and whether the first one is today or later:

PV: One fixed payment repeated at a regular frequency, such as an equal-payment stream. Confirm whether the payments occur at period-end or period-beginning.
NPV: Different amounts at equally spaced future periods. Keep any time-zero cash flow outside the NPV range.
XNPV: Different amounts on irregular dates. Include a date anchor that matches the date at which you want the value measured.
SUMPRODUCT with period numbers: Different amounts at regular or uneven numbered periods when you want visible control over each discount exponent.

These functions calculate values from the assumptions supplied; they do not determine which discount rate is economically appropriate or guarantee a future outcome. For a loan or other financial decision, check the actual payment schedule, rate convention, and fees with the lender or a qualified professional. The examples here are illustrative spreadsheet calculations, not financial, tax, investment, or legal advice.

Frequently Asked Questions

Can Excel PV handle different payment amounts?
No. PV uses one repeated payment amount. For varying payments, use NPV for equally spaced periods, XNPV for irregular dates, or discount each cash flow with a period-based formula.

What is the difference between PV and NPV in Excel?
PV models a regular stream with a constant payment argument. NPV discounts a list of future cash flows that can have different values, assuming they are evenly spaced and the first listed value occurs one period from now.

How do I include a payment made today?
In an NPV calculation, keep the time-zero cash flow outside the function and add it to the NPV result. In a custom period-based calculation, assign that payment period zero so it is not discounted.

Should I use NPV or XNPV for monthly payments?
Use NPV when payments are treated as equally spaced monthly periods and the rate is monthly. Use XNPV if the actual dates vary and the calendar intervals matter.

Why is my Excel present value negative?
Excel follows cash-flow signs. If payments are entered as positive outflows, PV may display a negative result. Reverse the sign convention consistently if you want the present value of those payments displayed as positive.

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.