Excel NPV With Different Rates: Formula, Steps, Example

Excel’s NPV function accepts one discount rate, so it cannot directly calculate net present value when the rate changes from period to period. To handle different rates, calculate a cumulative discount factor for each future period, multiply each cash flow by its factor, and add any time-zero cash flow separately.

Why the regular NPV function is limited

Excel’s NPV function discounts a series of future cash flows using the same rate for every period. That is appropriate when one rate applies throughout the forecast, but it does not represent a schedule such as 10% in year one, 8% in year two, and 12% in year three. Changing the rate argument for each cash flow is not an option: the function has only one rate argument.

When rates differ, each future cash flow must be discounted by the combined effect of all the rates from the start through that cash flow’s period. The second-period cash flow is discounted by both period one’s and period two’s rates; the third is discounted by all three rates. This cumulative effect is the key to the correct formula.

For a cash flow at the end of period t, the discount factor is 1 divided by the product of 1 plus each period’s rate through t. The present value is that period’s cash flow multiplied by its discount factor. Add the present values of the future flows, then include the initial cash flow at time zero.

Set up the worksheet

Use one row for the initial investment and one row for each later period. The following layout assumes a project with three future annual cash flows:

Put the initial cash flow, such as an outlay of -300, in B1. This is the time-zero amount.
Put future cash flows of 100, 120, and 150 in B2:B4, in chronological order.
Put the rate applying to each period in C2:C4: 10%, 8%, and 12%, respectively.
Use D2:D4 for the cumulative discount factors.

Enter rates as percentages or decimal values: 10% and 0.10 mean the same thing in Excel. Make sure the cash flow in each row matches the rate for the period in that row. For example, C3 should contain the rate applying to the period-three? No: with this layout, C3 applies to period two, because row 2 is period one. Labels in column A can prevent that off-by-one confusion; use period numbers 0, 1, 2, and 3 alongside the cash flows.

Calculate cumulative discount factors

With the period-one rate in C2, enter this formula in D2:

=1/(1+C2)

With the period-two rate in C3 and the previous cumulative factor in D2, enter this formula in D3:

=D2/(1+C3)

Copy the D3 formula down through D4. Excel updates the references as it fills, so D4 becomes the previous factor divided by 1 plus the rate in C4. The resulting factors are approximately 0.909091, 0.841751, and 0.751563. Each later factor includes all earlier rates, not just the rate in its own row.

You can verify the recurrence another way. The period-two factor is 1 divided by 1.10 and then divided by 1.08; it is not simply 1 divided by 1.08. Continuing the calculation for period three divides the period-two factor by 1.12. This method works for any number of periods and makes the rate-to-period mapping visible for review.

Calculate NPV with changing rates

With the initial cash flow in B1, future cash flows in B2:B4, and cumulative factors in D2:D4, enter this formula in a result cell:

=B1+SUMPRODUCT(B2:B4,D2:D4)

For the example, the present values of the three future flows are about 90.909, 101.010, and 112.734. Their total is about 304.654. Adding the initial cash flow of -300 gives an NPV of approximately 4.654. Small differences in the last decimal place can occur from rounding displayed values; the worksheet calculates with the underlying precision.

The result is positive because, using the stated cash flows and period-specific rates, the discounted future inflows exceed the initial outflow by about 4.65. This is a calculation example, not a recommendation to accept or reject a real project. The conclusion depends on the accuracy and suitability of the cash flows, rates, timing, and assumptions.

What happens if one rate applies throughout?

If the same rate applies to every period, Excel’s regular NPV function is simpler. With the initial -300 in B1, future flows in B2:B4, and a constant 10% rate, use:

=NPV(10%,B2:B4)+B1

This returns approximately 2.780. Excel NPV treats the first value in its values argument as a cash flow one period from now, not as a time-zero cash flow. That is why B1 is added outside the function. Putting the initial outflow inside the NPV range would discount it by one period and give the wrong timing.

Do not use the constant-rate NPV formula just because the rate cells are next to the cash flows. If the rates vary, a single rate argument cannot apply each row’s distinct rate. Use the cumulative-factor method instead, or build a clearly checked formula that calculates the same cumulative products.

Choose rates and periods consistently

The rate in each row must cover the interval between the preceding cash-flow date and the date of that row’s cash flow. If cash flows are annual and occur at each year-end, use annual rates for the corresponding years. If cash flows are monthly, use rates that apply to monthly intervals. Mixing annual rates with monthly cash-flow periods without converting them creates incorrect discount factors.

Rates can vary for different reasons, including changes in a forecast discount-rate assumption or a contractually specified rate schedule. Use the rate convention that matches the problem: for example, effective annual rates should not be casually mixed with nominal rates that require compounding. If a period is irregular, a simple row-by-row period model may not match a date-based calculation; document the timing convention and make the intervals explicit.

Keep rate assumptions separate from cash-flow inputs where possible. Add labels for period, date, cash flow, rate, and discount factor. This makes it easier to see whether a rate has been assigned to the right interval and to change an assumption without rewriting formulas. For real financial decisions, verify the relevant cash-flow and rate terms with the lender or a qualified professional; this spreadsheet explanation is not financial, tax, or investment advice.

Timing, signs, and rounding

NPV depends on when cash flows occur, not just their amounts. A cash flow at time zero is not discounted. A cash flow at the end of the first period is discounted once; one at the end of the second period is discounted twice, using both period rates. If a project begins with an outflow, the usual convention is to enter it as a negative amount and later receipts as positive amounts.

Do not round the discount factors in the formulas just because the cells display six decimal places. Excel can keep more precision than it displays. Set the number of visible decimal places with Home > Number, or use percentage and number formats appropriate to the inputs, while keeping full precision in calculations. If you need a reported result rounded to cents, round the final presentation or use a separate reporting formula; changing intermediate calculations can create small differences.

Common errors to check

Discounting the initial amount: keep the time-zero cash flow outside the future-period factors and add it once.
Using only the current period’s rate: later cash flows require the cumulative product of all rates up to that period.
Misaligning rates and cash flows: confirm that each rate covers the interval ending at its matching cash-flow row.
Mixing periods: annual rates need annual intervals unless converted appropriately for a shorter period.
Reversing signs: check that outflows and inflows use a consistent sign convention.
Overwriting the first factor: D2 starts the chain; each following factor must reference the previous factor as well as the new rate.

A useful audit is to calculate the first two factors by hand, then compare them with the worksheet. Also check a simple special case: if every rate is identical, the changing-rate method should agree with the ordinary NPV calculation when the time-zero cash flow is added separately. That comparison can reveal an off-by-one error or a rate in the wrong row.

How this differs from XNPV

Excel’s XNPV function is designed for cash flows that occur on specific, potentially irregular dates. It accepts one rate and uses the dates to account for the spacing between cash flows. It does not let you supply a different discount rate for each interval. Therefore, XNPV is useful when dates are irregular but one rate is appropriate; it is not a direct replacement for the cumulative-factor method when rates vary.

If both the dates and rates vary, first decide how each interval’s rate should be applied and how the date-count convention should work. Then calculate an interval-specific discount factor and multiply the factors cumulatively. A standard annual, equal-period worksheet may not capture irregular dates correctly, so make the chosen convention explicit rather than assuming the rows represent equal intervals.

Frequently Asked Questions

Can Excel NPV use a different rate for every cash flow?

No. NPV takes one rate for all values in its argument. To use changing rates, calculate cumulative discount factors for each period and multiply each future cash flow by its corresponding factor.

Why is the initial investment outside the NPV formula?

Excel treats the first value inside NPV as occurring at the end of period one. An initial investment occurs at time zero, so add it separately without discounting it.

Do I multiply or add the different rates?

For successive periods, compound the discount effects by multiplying the factors 1 plus each period’s rate. Equivalently, divide the previous cumulative discount factor by 1 plus the next rate. Adding the rates does not produce the correct cumulative discount factor.

Can I use XNPV when rates change?

Not directly. XNPV accepts one rate and accommodates dated cash flows. For varying rates, calculate period-specific factors yourself; if dates are irregular, define the interval and day-count treatment as part of that calculation.

What if rates are monthly but cash flows are annual?

Use rates that correspond to the cash-flow intervals, or convert the monthly assumptions into a consistent annual effective rate using the applicable compounding convention. The conversion depends on what the quoted rate means, so check the underlying terms before applying it.

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.