Spreadsheet Practice

Lesson 27 of 29, Finance

NPV & IRR

Is this project worth doing?

Pro10 minutes, 2 exercisesNPVIRR

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

NPV(rate, values) discounts future cash flows back to today. Excel's NPV assumes the first value arrives one period from now, so add the year-0 investment outside the function.

IRR(values) is the discount rate that makes NPV exactly zero. If it beats your required return, the project clears the bar.

Syntax

=NPV(E2,B3:B7)+B2NPV with year 0 outside
=IRR(B2:B7)Internal rate of return

Watch out for

  • =NPV(E2,B2:B7) discounts the year-0 investment by a year, which overstates the result.
  • IRR needs at least one negative and one positive cash flow.

The exercises

  1. Net present value. In E4, calculate the project's NPV at the discount rate in E2. Year 0 is an upfront investment.
  2. Internal rate of return. In E5, calculate the project's IRR.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Net present value
  2. Internal rate of return
Try these functions in the Formula Lab