For equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then add any cash flow that occurs today separately. If B1 contains the time-zero investment and B2:B13 contain months 1 through 12, the formula is =NPV(1%,B2:B13)+B1 when the monthly rate is 1%.
What Excel NPV means for monthly cash flows
Excel’s NPV function calculates the present value of a sequence of future cash flows that occur at regular intervals. The interval is not built into the function: Excel treats each value in the range as one period after the previous one. If the values are monthly, one period means one month, so the rate entered in NPV must also be a monthly rate.
That rate-and-period match is the key to using NPV correctly. Entering an annual rate while the cash flows are monthly tells Excel to apply the annual rate every month. That usually discounts the cash flows far too heavily. Conversely, entering a monthly rate for annual cash flows understates the discounting.
NPV is useful for comparing the present value of future receipts and payments with an initial outlay, or for calculating the net present value of a project. It is a calculation, not a verdict about whether a project is suitable. Its answer depends on the cash-flow estimates, timing, and discount rate supplied.
Set up a monthly cash-flow schedule
Put the initial amount and the monthly amounts in separate, clearly labeled cells. A simple layout might use column A for timing and column B for cash flow:
- B1: initial cash flow at month 0, such as an investment of -$1,000.
- B2:B13: cash flows at the ends of months 1 through 12.
- A separate cell, such as D1, can hold the monthly discount rate.
Use negative numbers for money paid out and positive numbers for money received. If the initial investment is a payment, enter it as a negative value; if there is an initial receipt, enter it as a positive value. NPV adds the discounted future values, so including a negative initial payment in the NPV range would make Excel discount it as though it occurred one month from now—not today.
For a schedule with B1 as the month-zero amount and B2:B13 as monthly amounts, the formula is =NPV(1%,B2:B13)+B1. The first value in the NPV range is treated as the month-one cash flow. The addition of B1 then includes the amount at month 0 without discounting it.
Worked example: twelve monthly receipts
Suppose a project costs $1,000 today and is expected to return $100 at the end of each of the next 12 months. For this illustration, assume a 1% monthly discount rate. Enter -1000 in B1 and 100 in each cell from B2 through B13.
With B1 holding the initial cost and B2:B13 holding the twelve future receipts, use =NPV(1%,B2:B13)+B1. The present value of the twelve receipts is approximately $1,125.51; adding the -$1,000 initial cash flow gives an NPV of approximately $125.51. A positive result here means the discounted receipts exceed the stated initial cost under the example’s assumptions. It does not establish that the assumptions are realistic.
You can check the schedule before trusting the result: there should be twelve receipt cells, not eleven or thirteen, and the first receipt should represent the end of month 1. If your receipts vary, enter the actual estimated amount in each month’s cell rather than repeating $100. The same NPV formula will discount each listed amount according to its position in the range.
Convert an annual rate into a monthly rate
Choose a monthly rate consistent with how the annual rate is quoted. For a nominal annual rate that is divided into 12 equal monthly periods, divide by 12. For example, if D1 contains a nominal annual rate of 12%, and D2 is the cell for the monthly rate, use =D1/12. This gives 1% per month.
An effective annual rate already reflects compounding over the year, so dividing it by 12 is not generally the equivalent monthly rate. If D1 contains a 12% effective annual rate and D2 is the monthly rate cell, use =(1+D1)^(1/12)-1. This produces a monthly rate of about 0.9489%, which compounds to 12% over twelve months. The two conversions differ because the quoted annual rates describe different conventions.
Do not choose between these methods just because one produces a preferred answer. Use the convention that matches the rate’s source and the cash flows being modeled. For a loan, investment, lease, or other financial arrangement, check the actual rate definition, compounding convention, and payment terms in the relevant documents or with a qualified professional. This example is for spreadsheet mechanics, not financial advice.
Timing: beginning, end, and month zero
Excel NPV assumes each cash flow in its range arrives at the end of a successive period. Thus, the first item in B2:B13 is month 1, the next is month 2, and the last is month 12. This assumption often fits a schedule of payments made at each month-end.
If a cash flow occurs at the beginning of month 1, it is one period earlier than an end-of-month payment. Do not silently treat it as month 1 end. You can model the timing by placing that amount at month 0 and handling it outside the NPV range, or by adjusting the schedule to match the actual dates and using a date-based method when periods are not equal. Be consistent about when each amount occurs; a one-month timing shift can change the present value.
A common misconception is that NPV automatically includes the first amount in the cash-flow list as the investment today. It does not. If the first cell in the NPV range is the initial outlay, Excel discounts that value one period. For a conventional time-zero investment, keep that amount separate and add it after NPV, as in the worked example.
Use XNPV when dates are irregular
NPV counts equal-length periods; it does not read calendar dates to decide how long to discount each amount. If cash flows arrive on uneven dates—for example, a payment after 20 days followed by another 50 days later—use XNPV when the dates matter. XNPV discounts using the actual dates and an annual rate, with its date-count convention based on a 365-day year.
Suppose A1:A13 contains the actual dates for the cash flows and B1:B13 contains the corresponding amounts, including the initial amount in B1. With an annual discount rate of 12%, use =XNPV(12%,B1:B13,A1:A13). Unlike the usual NPV setup, include the starting cash flow in the XNPV values range with its date. The first date anchors the calculation, and later values are discounted according to the time elapsed from that date.
For exactly monthly, equally spaced cash flows, NPV with a monthly rate is often the more direct model. XNPV can still be useful when the payment dates vary from month to month, but its annual rate and date-based convention should not be confused with NPV’s one-rate-per-period setup. Confirm that the dates are valid Excel dates and that each amount lines up with its intended date.
Common formula and modeling problems
- Using an annual rate in monthly NPV: Convert the rate to a monthly rate using the appropriate convention before applying NPV.
- Putting the initial amount inside the NPV range: A time-zero amount is not discounted by one month; add it separately for a regular NPV calculation.
- Omitting a month: Each position in the range represents a period. A blank cell can make the schedule harder to audit, so use one row per month and check that periods are sequential.
- Mixing payments and receipts signs: Payments are normally negative and receipts positive. Consistent signs make the resulting net value easier to interpret.
- Using the wrong timing assumption: NPV treats listed flows as end-of-period amounts. Move or model beginning-of-period flows accordingly.
- Rounding the rate too early: Keep the rate at appropriate precision in the cell and format its display separately. Rounding a monthly rate can affect a long schedule.
- Confusing NPV with total profit: NPV expresses cash flows in present-value terms at a selected rate; it is not simply the sum of nominal receipts and payments.
For a quick audit, count the future periods, verify the sign of each flow, inspect the rate’s time basis, and calculate a small sample independently. Also distinguish a displayed rounded result from the underlying value Excel uses in later calculations.
NPV versus IRR for monthly schedules
NPV answers a value question: what are these cash flows worth today at the specified discount rate? IRR answers a rate question: what periodic rate makes the NPV equal zero? For a monthly schedule, Excel’s IRR function returns a rate per period, so its result is monthly when the cash flows are monthly. Do not read that result as an annual rate without converting it under the applicable compounding convention.
For a regular schedule that includes the initial investment, IRR normally uses the full sequence, including the time-zero flow. NPV has different timing behavior: its range begins with the first future period, so the initial flow is normally added separately. The functions therefore should not be fed the same range without considering their different assumptions.
IRR can also have multiple answers or fail to converge for cash flows that change sign more than once. NPV at a stated rate is usually more straightforward to interpret, provided the rate and timing are appropriate. Neither measure removes uncertainty in forecasts or determines whether a decision meets a person’s objectives.
Frequently Asked Questions
Can I use Excel NPV for monthly cash flows?
Yes. Enter one value for each month in chronological order and use a monthly discount rate. Keep any cash flow at month 0 outside the NPV range and add it separately.
Does Excel NPV include the initial investment?
Not automatically as a time-zero amount. Excel treats the first value in the NPV range as arriving one period in the future. For a conventional initial investment, calculate NPV on future cash flows and add the initial amount separately.
Should I divide the annual discount rate by 12?
Only when that matches the rate convention. Dividing a nominal annual rate by 12 gives a nominal monthly rate. To convert an effective annual rate, use the twelfth-root conversion instead. Check how the rate was quoted.
What if monthly payments happen at the start of each month?
Excel NPV assumes end-of-period timing. A payment at the start is earlier than the corresponding month-end payment, so represent its timing explicitly rather than treating it as though it arrives one full month later.
When should I use XNPV instead of NPV?
Use XNPV when cash flows occur on irregular dates and the calendar spacing matters. Use NPV for equally spaced monthly flows with a monthly rate. XNPV takes dates and an annual rate; NPV counts regular periods.
Why is my NPV negative?
A negative result means the discounted cash outflows exceed the discounted inflows at the rate and timing you entered. Check the rate units, signs, initial amount, and period count before interpreting it; the result is only as useful as those inputs.
Related
- Excel SUM Visible Rows: Add Filtered Data Correctly
- Excel Sum Text? What SUM Does With Text Cells
- Excel Sum Table Column: How to Total Values Correctly
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUBTOTAL for Columns: Formulas, Filters, and TipsTo subtotal a column in Excel, enter a formula such as =SUBTOTAL(9,B2:B20) in a cell below the values;…
- SUMIFS Excel Criteria Range: How to Set It UpIn Excel, a SUMIFS criteria range is the range of cells that SUMIFS checks against a condition before…
- Excel Text Expansion: AutoCorrect, Formulas, and MoreExcel has no single feature called “text expansion,” but you can make a short abbreviation expand as you…
- Excel Table Array Lookup: Using VLOOKUP in a TableAn Excel table array lookup usually means using the table_array argument in VLOOKUP: it tells Excel which range…
- Combine Cells in Excel Without Merging: Formulas and StepsTo combine the contents of cells without merging them, put the result in a separate cell with a…
- Excel Compare Two Sheets Cell by Cell: Formulas and ToolsYes. In Excel for Microsoft 365 on Windows, compare matching cells on two sheets with a formula such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.