Ravindra BagaleCourses & study guides Track your progress

Guides

NPV and IRR in Excel: Is This Project Worth the Money?

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Ravina is thinking of buying a ₹5,00,000 printing machine for her Kolhapur shop. She expects extra cash of ₹1,50,000, ₹1,80,000, ₹2,00,000 and ₹1,60,000 over the next four years. That adds up to ₹6,90,000, but money in year 4 is worth less than money today. NPV and IRR tell her if the machine beats a 12% return she could get elsewhere.

NPV (net present value) converts future cash flows to today's money at a chosen rate and subtracts the investment. NPV above 0 means the project earns more than that rate. IRR (internal rate of return) is the rate at which NPV is exactly 0.

Download the practice file (npv-irr.xlsx)

Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. List the cash flows in order: year 0 cost as a negative number in B2, years 1-4 in B3:B6.
  2. Put the required return (12%) in E1.
  3. NPV: =NPV(E1,B3:B6)+B2. Do not put B2 inside NPV.
  4. IRR: =IRR(B2:B6).
  5. Accept the project if NPV > 0, or IRR > 12%.
=NPV(E1,B3:B6)+B2
=IRR(B2:B6)

The numbers

YearCash flowValue today at 12%
0₹-5,00,000₹-5,00,000
1₹1,50,000₹1,33,929
2₹1,80,000₹1,43,495
3₹2,00,000₹1,42,356
4₹1,60,000₹1,01,683

The values today add up to ₹21,462. That is the NPV. It is positive, so the machine earns more than 12%. =IRR(B2:B6) returns 13.98%, which is above 12%, so both tests agree.

The year-0 trap

Excel's NPV function assumes the first value comes one period from now. If you write =NPV(12%,B2:B6) with the cost included, Excel discounts the cost too and the answer is wrong.

FormulaResultCorrect?
=NPV(12%,B3:B6)+B2₹21,462Yes
=NPV(12%,B2:B6)₹19,163No: the year-0 cost was discounted

When the dates are uneven: XNPV and XIRR

NPV and IRR assume equal gaps (every year, or every month). For real dates use =XNPV(rate, values, dates) and =XIRR(values, dates). In XNPV the first value is on the first date and is not discounted, so you include the cost in the range.

Reading the answer

  • NPV is in rupees: ₹ value created above the required return.
  • IRR is a percentage and is easy to compare, but it can mislead when two projects have very different sizes. Prefer the one with the higher NPV.
  • Cash flows that switch sign more than once (−, +, −) can have more than one IRR. Use NPV for those.
  • The forecast drives everything: change the cash flows by 10% and see if the decision still holds.

Ravindra Bagale's Tip

Show NPV at two or three rates (10%, 12%, 15%) next to each other. If the answer is positive at all of them, the decision is easy; if it flips, you know which assumption to discuss.

Practice task

  1. Open the Project sheet and check B2:B6 hold the cash flows.
  2. Check E2 shows the NPV ₹21,462 and E3 the IRR 13.98%.
  3. Look at the wrong formula in E5 and compare.
  4. Change E1 to 15% and see whether NPV stays positive.
  5. Reduce every yearly cash flow by 10% and check the decision again.

Learn it properly

Course lessons:

Related guides: PMT, IPMT and PPMT: Calculate EMI and Build a Loan Schedule · Goal Seek in Excel: Work Backwards to the Number You Need · Finance and Accounting Excel Template (Budget vs Actual + GST Register) · Percentage Formulas in Excel: Growth, Share, Discount, Margin and GST

Frequently asked questions

Why is Excel's NPV different from my calculator?

Most likely the year-0 cost is inside the NPV range. Add it outside: =NPV(rate,B3:B6)+B2.

What rate should I use?

Your required return: the cost of borrowing or the return you could get on a similar investment.

Why does IRR show #NUM!?

Excel could not find a rate. Make sure there is at least one negative and one positive cash flow, or give a guess: =IRR(B2:B6,10%).

Can I use monthly cash flows?

Yes. Use a monthly rate in NPV, and IRR then returns a monthly rate. Convert it to yearly with =(1+IRR)^12-1.