NPV & IRR practice exercises
4 graded problems with realistic data, all medium.
NPV discounts a series of future cash flows back to today's value at a chosen rate; IRR finds the rate that would make that same series' NPV exactly zero. The 4 exercises below come from LearnExcel's practice library. Each one gives you a realistic table, a cell to fill, and a hint if you get stuck. Read them here, then solve them in the interactive grid, where your formula is checked the moment you press Enter.
New to the function? Read the NPV & IRR guide first: syntax, a worked example and the mistakes people make.
In this set
- Find the net present value of a project
- Use NPV to compare two projects
- Find a project's internal rate of return
- Use IRR to screen a project against a target return
The exercises
A project costs $5,000 upfront and returns $2,000, $2,500, and $3,000 over the following three years. At an 8% discount rate, what's the project's NPV?
| A | B | |
|---|---|---|
| 1 | Upfront cost | 5000 |
| 2 | Discount rate | 0.08 |
| 3 | Year 1 | 2000 |
| 4 | Year 2 | 2500 |
| 5 | Year 3 | 3000 |
| 6 | NPV | ? |
Enter a formula in B6.
Show a hint
NPV only discounts the range you give it, starting one period out; it doesn't know about the upfront cost. Subtract that separately: =NPV(B2,B3:B5)-B1.
This is Excel's one real NPV quirk: NPV() discounts its first value by one full period, as if it happens a year from now, so a cost that happens right now (time zero) has to be subtracted outside the function, not included inside it.
Project A costs $8,000 upfront and returns $3,000, $3,000, $3,000, and $3,000 over four years. At a 10% discount rate, is Project A worth doing (does it have a positive NPV)? Calculate its NPV.
| A | B | |
|---|---|---|
| 1 | Upfront cost | 8000 |
| 2 | Discount rate | 0.1 |
| 3 | Year 1 | 3000 |
| 4 | Year 2 | 3000 |
| 5 | Year 3 | 3000 |
| 6 | Year 4 | 3000 |
| 7 | NPV | ? |
Enter a formula in B7.
Show a hint
=NPV(B2,B3:B6)-B1, same pattern as before, just a longer range of returns.
A positive NPV means the project returns more than the discount rate demands; this is literally how real capital-budgeting decisions get made.
A project requires a $10,000 investment today and returns $3,000, $4,000, $4,000, and $3,500 over the next four years. What's its internal rate of return?
| A | B | |
|---|---|---|
| 1 | Year 0 (investment) | -10000 |
| 2 | Year 1 | 3000 |
| 3 | Year 2 | 4000 |
| 4 | Year 3 | 4000 |
| 5 | Year 4 | 3500 |
| 6 | IRR | ? |
Enter a formula in B6.
Show a hint
Unlike NPV, IRR wants the time-zero investment included right in the range, as a negative number: =IRR(B1:B5).
IRR answers a different question than NPV: not "is this worth it at my discount rate" but "what discount rate would make this project break exactly even", the number lenders and investors actually quote back and forth.
Your company only approves projects with an IRR above 12%. A proposed project needs a $20,000 investment today and returns $6,000, $7,000, $7,000, $6,000, and $5,500 over the next five years. Calculate its IRR to see whether it clears the bar.
| A | B | |
|---|---|---|
| 1 | Year 0 (investment) | -20000 |
| 2 | Year 1 | 6000 |
| 3 | Year 2 | 7000 |
| 4 | Year 3 | 7000 |
| 5 | Year 4 | 6000 |
| 6 | Year 5 | 5500 |
| 7 | IRR | ? |
Enter a formula in B7.
Show a hint
=IRR(B1:B6), same shape, the investment is already built into the range as a negative, just one more year of returns in the range.
A company-wide "hurdle rate" like 12% is exactly what IRR is for: one number per project, directly comparable, no discount rate to argue about first.
Solve these in a real grid
Type your formula, get graded instantly. Your first 10 exercises are free, no signup.
Start the NPV & IRR exercises →