Why is Excel's NPV formula mathematically different from textbook NPV?

Excel's NPV function discounts the first value in the range by 1 period. Therefore, Year 0 (today's initial cash outlay) must NEVER be included inside the NPV() argument; it must be added outside the function: =NPV(rate, Yr1:Yr5) + Yr0.

When should I use XIRR and XNPV instead of IRR and NPV?

Use IRR and NPV when cash flows occur at strictly equal annual or monthly intervals. Use XIRR and XNPV when cash flows happen on irregular calendar dates.

What does an IRR greater than the discount rate indicate?

If IRR is greater than the company's cost of capital (hurdle rate / discount rate), the project has a positive NPV and creates shareholder value.

How to Calculate NPV and IRR in Excel & Google Sheets

Capital budgeting decisions determine how companies allocate capital toward major projects, acquisitions, and new product launches. The two benchmark financial metrics are:

Net Present Value (NPV): The dollar value added today by future project cash flows discounted at the required hurdle rate.
Internal Rate of Return (IRR): The annualized discount rate that makes the project’s NPV exactly equal to zero.

⚠️ The Universal “Excel NPV Trap” (Avoid This Mistake)

The most common mistake in financial modeling is including Year 0 cash flow inside the NPV() argument:

$$\text{❌ Incorrect: } =NPV(\text{Rate}, \text{Year0_to_Year5})$$ This erroneously discounts Year 0 cash outlay by one full year!

$$\text{✅ Correct: } =NPV(\text{Rate}, \text{Year1_to_Year5}) + \text{Year0_Outlay}$$

Step-by-Step Capital Project Valuation Model

Suppose a company evaluates purchasing a new automated manufacturing line requiring an initial upfront cost of -$250,000 with a 10.0% corporate discount rate (WACC):

Year / Period (A)	Date (B)	Net Cash Flow (C)	Present Value (PV) Formula (D)	Discounted Cash Flow
Year 0 (Today)	`01/01/2026`	`-$250,000.00`	`=C2` (Undiscounted)	`-$250,000.00`
Year 1	`12/31/2026`	`$65,000.00`	`=C3 / (1 + $B$1)^1`	`$59,090.91`
Year 2	`12/31/2027`	`$85,000.00`	`=C4 / (1 + $B$1)^2`	`$70,247.93`
Year 3	`12/31/2028`	`$95,000.00`	`=C5 / (1 + $B$1)^3`	`$71,374.91`
Year 4	`12/31/2029`	`$90,000.00`	`=C6 / (1 + $B$1)^4`	`$61,471.21`
Year 5	`12/31/2030`	`$70,000.00`	`=C7 / (1 + $B$1)^5`	`$43,464.49`

Valuation Results:

=NPV(10%, C3:C7) + C2

Net Present Value (NPV): +$55,649.45 (Positive $\rightarrow$ Project is profitable)

=IRR(C2:C7)

Internal Rate of Return (IRR): 18.23% (Exceeds 10% hurdle rate by 8.23%)

Irregular Cash Flows: XNPV and XIRR

In private equity, venture capital, and real estate, cash flows do not occur on neat 365-day annual intervals. For real-world dates, use XNPV and XIRR.

=XNPV(Discount_Rate, Cash_Flow_Values, Dates_Range)
=XIRR(Cash_Flow_Values, Dates_Range)

[!NOTE] Unlike standard NPV(), XNPV() DOES include Year 0 in the range because it discounts each cash flow explicitly based on the exact number of days elapsed between dates.

Real Estate Syndicate Example:

Transaction Date (A)	Event Description (B)	Cash Flow (C)
`03/15/2026`	Initial LP Equity Contribution	`-$500,000.00`
`09/30/2026`	Q3 Rental Distribution	`$18,500.00`
`03/31/2027`	Q1 Rental Distribution	`$22,000.00`
`11/15/2027`	Supplemental Refinance Distribution	`$110,000.00`
`08/20/2028`	Property Sale / Final Exit	`$640,000.00`

Formulas:

XIRR (Annualized Return):
```
=XIRR(C2:C6, A2:A6)
```
Returns: 20.48% annualized return.
XNPV (at 12% Cost of Capital):
```
=XNPV(12%, C2:C6, A2:A6)
```
Returns: +$89,416.73 present value created.

Decision Matrix: Accept or Reject Project?

Metric	Rule	Interpretation
NPV > $0	✅ Accept	Investment generates return in excess of discount rate
NPV = $0	🟡 Indifferent	Investment exactly matches cost of capital
NPV < $0	❌ Reject	Investment destroys financial value
IRR > Hurdle Rate	✅ Accept	Return percentage clears required cost of capital
IRR < Hurdle Rate	❌ Reject	Return is lower than alternative safe investments

How to Calculate NPV and IRR in Excel & Google Sheets (Capital Budgeting)