Financial Functions: NPV, IRR, PMT

These are the functions that turn Excel from a calculator into an investment decision tool.

NPV — Net Present Value

Values a series of future cash flows by discounting them back to today at a given rate:

=NPV(discount_rate, year1_to_lastyear_cashflows) + initial_investment

Note that Excel’s NPV function does not include the year-zero cash flow inside the function itself — you add it manually afterward, since it isn’t discounted (it happens today).

Decision rule: if NPV is positive, the project adds value — worth investing in. If negative, it destroys value — reject it.

IRR — Internal Rate of Return

The discount rate that makes NPV exactly zero. In other words: “what’s the highest financing cost this project can bear and still be profitable?”

=IRR(range_of_all_cashflows_from_year0)

Decision rule: if IRR is greater than your cost of financing (the discount rate), accept the project; if lower, reject it. For simple cash flow patterns (one upfront investment, then positive returns), this gives the same decision as NPV.

PMT — loan payment

Calculates the fixed periodic payment needed to pay off a loan:

=PMT(rate_per_period, number_of_periods, loan_amount)

The result comes out negative because it represents an outflow from your perspective.

Try it yourself

The lab below calculates NPV and IRR live as you change the initial investment, the yearly cash flows, and the discount rate — exactly like Excel would.

Ready to apply these to real data? Head to the case study.