Excel NPV Function

last modified April 4, 2025

The NPV function calculates the net present value of an investment based on a series of future cash flows and a discount rate. This tutorial provides a comprehensive guide to using the NPV function with detailed examples. You’ll learn basic syntax, practical applications, and advanced techniques to master this essential financial function.

NPV Function Basics

The NPV function determines the present value of future cash flows by discounting them at a specified rate. It helps evaluate investment profitability. The syntax requires a discount rate and cash flow values.

Component
Description


Function Name
NPV


Syntax
=NPV(rate, value1, [value2], ...)


Arguments
rate (discount rate), value1+ (cash flows)


Return Value
Net present value of cash flows

This table breaks down the essential components of the NPV function. It shows the function name, basic syntax format, required arguments, and return value characteristics.

Basic NPV Example

This example demonstrates the simplest use of the NPV function with a discount rate and three future cash flows.

Basic NPV formula

=NPV(0.1, 1000, 2000, 3000)

This formula calculates NPV with a 10% discount rate and three cash flows. The result is $4,815.93. Each cash flow is discounted back to present value and summed.

NPV with Initial Investment

A common application calculates NPV including an initial investment. This requires adding the initial cost outside the NPV function.

A
B


-5000
Initial Investment


1000
Year 1 Cash Flow


2000
Year 2 Cash Flow


3000
Year 3 Cash Flow



=A1 + NPV(0.1, A2:A4)

The table shows a typical investment scenario with initial outlay and future cash flows. The NPV formula combines these to evaluate the investment.

NPV with initial investment

=A1 + NPV(0.1, A2:A4)

This formula adds the initial investment (-$5,000) to the NPV of future cash flows. The result is -$184.07, indicating a slightly negative net present value at 10% discount rate.

NPV with Uneven Cash Flows

NPV handles uneven cash flow patterns effectively. This example shows NPV with varying positive and negative cash flows.

A
B


-10000
Initial Investment


5000
Year 1


-2000
Year 2


8000
Year 3


4000
Year 4



=A1 + NPV(0.12, A2:A5)

This table demonstrates NPV calculation with an initial investment and uneven future cash flows, including a negative cash flow in Year 2.

NPV with uneven cash flows

=A1 + NPV(0.12, A2:A5)

The formula evaluates an investment with a 12% discount rate. The result is $2,081.91, indicating a positive NPV. Negative intermediate cash flows are handled naturally in the calculation.

NPV with Monthly Cash Flows

For monthly cash flows, adjust the discount rate to a monthly equivalent. This example shows proper rate conversion.

A
B


-5000
Initial Investment


500
Month 1


600
Month 2


700
Month 3



=A1 + NPV(0.1/12, A2:A4)

The table illustrates NPV calculation for monthly cash flows. The annual 10% rate is divided by 12 to get the monthly discount rate.

Monthly NPV calculation

=A1 + NPV(0.1/12, A2:A4)

This formula calculates NPV for monthly cash flows using a monthly discount rate (10%/12). The result is -$3,223.60. Always match the rate period to cash flow frequency.

NPV vs. IRR Comparison

This example compares NPV with IRR (Internal Rate of Return) for decision making. Both metrics help evaluate investments but provide different insights.

A
B
C


-10000
Initial



3000
Year 1



4000
Year 2



5000
Year 3




NPV (10%)
=A1 + NPV(0.1, A2:A4)



IRR
=IRR(A1:A4)

The table shows both NPV and IRR calculations for the same cash flows. NPV uses a 10% discount rate while IRR calculates the breakeven rate.

NPV and IRR comparison

=A1 + NPV(0.1, A2:A4) =IRR(A1:A4)

The NPV formula returns $698.05, indicating a positive value at 10%. The IRR formula returns 14.49%, the rate making NPV zero. Together they provide complementary investment insights.

NPV with Growing Cash Flows

For cash flows growing at a constant rate, combine NPV with growth calculations. This example models a growing perpetuity.

NPV with growth rate

=NPV(0.1, 1000, 10001.05, 10001.05^2, 1000*1.05^3)

This formula calculates NPV for cash flows growing at 5% annually, discounted at 10%. The result is $3,019.63. Growth rates can be incorporated directly into cash flow projections.

The NPV function is essential for financial analysis in Excel. From basic investment appraisal to complex cash flow modeling, NPV provides critical insights. Remember to match discount rate periods to cash flow frequency and properly account for initial investments outside the NPV calculation.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.