Returns the net present value of an investment based on a discount rate and a series of future cash flows. Essential for capital budgeting, investment analysis, project evaluation, and determining the profitability of investments by discounting future cash flows to present value.
Master the fundamentals of Excel NPV function
The NPV (Net Present Value) function calculates the net present value of an investment by discounting a series of future cash flows to their present value using a specified discount rate, then subtracting (or including) the initial investment. NPV is the cornerstone of capital budgeting and investment analysis, providing a single metric that captures the time value of money and investment profitability. A positive NPV indicates the investment exceeds the required rate of return (discount rate), while negative NPV suggests the investment should be rejected. NPV enables objective comparison of different investment opportunities regardless of their cash flow patterns.
NPV discounts each future cash flow using the formula: NPV = Σ(CFt/(1+r)^t) where CFt is cash flow at time t and r is discount rate. Earlier cash flows are worth more than later ones.
In Excel NPV, first cash flow occurs at END of first period (not time 0). This differs from some financial calculators. For time-0 investments, include in first cash flow or add separately.
NPV > 0: Accept investment (exceeds required return). NPV < 0: Reject investment (below required return). NPV = 0: Indifferent (exactly meets required return). Higher NPV = better investment.
Discount rate reflects opportunity cost of capital, required rate of return, or WACC. Higher rates reduce NPV as future cash flows are discounted more heavily. Rate selection is critical.
Supports up to 254 cash flow values, enabling complex investment analysis with varying cash flows over time. Can use cell ranges for flexibility.
Handles any cash flow pattern (positive, negative, varying amounts). Unlike annuities, NPV works with real-world irregular cash flows. For date-based irregular flows, use XNPV.
Function-specific parameters
Function-specific return type
Evaluate and rank capital investment projects based on value creation
Determine profitability of investments and compare investment opportunities
Assess project feasibility and financial viability before committing resources
Value businesses and assets based on projected future cash flows
Evaluate mergers and acquisitions based on expected cash flow benefits
Compare and select best investment opportunities from multiple options
Exact matching required
Returns numeric position
Handles missing text gracefully
=NPV(rate, value1, value2, ...)The discount rate per period. Enter as decimal (10% = 0.10). Should reflect opportunity cost of capital, required rate of return, or weighted average cost of capital (WACC). Must match cash flow period frequency.
The first cash flow value. Can be positive (inflow) or negative (outflow). In Excel NPV, first cash flow occurs at end of first period (not time 0).
Additional cash flow values (up to 254 values). Can include both positive and negative values. Each cash flow occurs at end of subsequent periods. Can use cell ranges like A1:A10.
The net present value (positive = profitable investment, negative = unprofitable)
Description: Calculates net present value of investment with multiple cash flows
Evaluate investment profitability with cash flows
Returns -$49.04 NPV, indicating investment returns less than 10% discount rate. Negative NPV means investment should be rejected (doesn't meet required return).
Using NPV function in VBA through WorksheetFunction
Sub NPVExample()
Dim result As Double
Dim cashFlows As Variant
cashFlows = Array(-1000, 300, 300, 300, 300)
result = Application.WorksheetFunction.NPV(0.1, cashFlows)
Range("A1").Value = result
Range("A1").NumberFormat = "$#,##0.00"
MsgBox "NPV: quot; & Format(result, "#,##0.00")
End Sub
' Calculate NPV from cell range
Sub NPVFromRange()
Dim discountRate As Double
Dim npvResult As Double
discountRate = Range("B1").Value
npvResult = Application.WorksheetFunction.NPV(discountRate, Range("B2:B7"))
Range("B8").Value = npvResult
Range("B8").NumberFormat = "$#,##0.00"
End Sub
' Compare multiple projects
Sub CompareProjects()
Dim projectA As Double, projectB As Double
projectA = Application.WorksheetFunction.NPV(0.1, Range("A2:A6"))
projectB = Application.WorksheetFunction.NPV(0.1, Range("B2:B6"))
Range("C1").Value = IIf(projectA > projectB, "Project A", "Project B")
Range("C2").Value = "A: quot; & Format(projectA, "#,##0.00")
Range("C3").Value = "B: quot; & Format(projectB, "#,##0.00")
End Sub
' NPV sensitivity analysis
Sub NPVSensitivity()
Dim baseRate As Double, i As Integer
Dim npv As Double
baseRate = 0.1
For i = 1 To 10
npv = Application.WorksheetFunction.NPV(baseRate + (i * 0.01), Range("A2:A6"))
Range("B" & (i + 1)).Value = npv
Next i
End SubEvaluate investment profitability
Rank projects by NPV value
Assess project financial viability
Value business from projected cash flows
Analyze NPV sensitivity to discount rate
Evaluate acquisition opportunities
NPV returns negative value
=NPV(0.10, -1000, 300, 300, 300, 300)Solution: Negative NPV means investment returns less than discount rate. This may be correct - investment doesn't meet required return. Verify: 1) Discount rate is appropriate (not too high), 2) Cash flow projections are realistic, 3) All cash flows are included. Negative NPV suggests rejecting the investment.
NPV results don't match expected values
=NPV(0.10, -1000, 300, 300)Solution: Excel NPV treats first cash flow as occurring at END of period 1, not time 0. If you have time-0 investment, either: 1) Include it in first cash flow position, 2) Use: Initial_Investment + NPV(rate, CF1, CF2...), 3) Use XNPV for exact date-based calculations.
NPV results seem incorrect or unrealistic
=NPV(0.10, A1:A5)Solution: Verify: 1) Discount rate is decimal (10% = 0.10), 2) Cash flow signs are correct (negative for outflows, positive for inflows), 3) All cash flows are included, 4) Cash flow order is correct (chronological). Test with simple known example first.
NPV returns error values
=NPV(0.10, A1:A10)Solution: #NUM! indicates invalid discount rate (negative or non-numeric). #VALUE! means non-numeric cash flow values. Verify all inputs are numbers, discount rate is positive decimal, cash flows are numeric. Check cell ranges contain valid numbers.
Uncertain which discount rate to use
=NPV(WACC, cashFlows)Solution: Use: WACC for company-wide projects, required rate of return for investor perspective, opportunity cost of capital, or risk-adjusted rate. Higher risk = higher discount rate. Compare NPV at different rates for sensitivity analysis.