How to Calculate Net Present Value (NPV) and Internal Rate of Return (IRR) in Excel

What is Net Present Value (NPV)?

Net Present Value (NPV) is a financial metric used to determine the current value of a series of cash inflows and outflows. It takes into account the time value of money, which means that a dollar received in the future is worth less than a dollar received today due to factors like inflation and the opportunity cost of not having that money available to invest in other projects.

The calculation of NPV involves discounting the expected future cash flows of a project or investment back to their present value using a specified discount rate. The result is the difference between the present value of the expected cash inflows and outflows.

NPV is an important calculation because it helps you evaluate the profitability and feasibility of an investment. It can also allow you to compare the expected returns of different investment opportunities, and to make informed decisions about which projects to pursue.

If the NPV is positive, it means that the project is expected to generate more cash inflows than outflows, and thus, it’s a profitable investment opportunity. However, if the NPV is negative, the project is expected to result in a net loss and is therefore not considered a viable option.

The NPV calculation is an important tool in finance as it can help decision makers determine whether to move forward on a project.

What is the Internal Rate of Return (IRR)?

The Internal Rate of Return (IRR) is used to measure the profitability of an investment project or opportunity, often in conjunction with calculating NPV. It is the discount rate where the present value of expected cash inflows equals the present value of expected cash outflows, or when NPV is equal to 0.

IRR represents the rate of return at which an investment will break even over its lifetime. It is shown as a percentage. And if you use the IRR percentage as your discount rate in the NPV calculation, the result will be an NPV of 0.

With Excel, you can quickly calculate the IRR through a simple formula, rather than having to go through a time-consuming process that might otherwise involve trial and error.

Calculating NPV and IRR in Excel

To illustrate how to calculate NPV and IRR, I’ll use the following example. Suppose that you are investing $1,000 into a project that will generate the following cost savings:

  • Year 1: $50
  • Year 2: $100
  • Year 3: $250
  • Year 4: $300
  • Year 5: $600

In total, that is $1,300 in cost savings. Although that’s more than the original $1,000 investment, those savings are spread out over a period of five years. To get a true picture of whether the project is worthwhile, you need to adjust for the time value of money and adjust those amounts and calculate their present values — what their values are today. This is where the NPV function comes into play.

However, before using the NPV function, you need to determine the discount rate that you are going to use. The discount rate is important as it tells you the interest rate that you will be using when adjusting the cost savings back to today, and to calculate the present value. If the discount rate is high, then it’ll be more difficult for the NPV calculation to be positive (and hence, suggest that the investment should be taken on). And if the discount rate is too low, then it could be too easy to clear the bar and for the NPV formula to suggest the project is worthwhile.

The discount rate should be higher than the risk-free rate since you are taking on some risk, and thus, you should be compensated for doing so. If you were to use the same rate as what you could earn on a treasury bill or a bank deposit, there would be little incentive to go ahead with the project even with a positive NPV. After all, what’s the point of taking on the risk if you’re not getting a better return?

In this example, I’m using a discount rate of 5%. This is what the NPV formula will look like with all of the inputs:


As you can see, the order of the values is important as that will determine how many periods each value will be discounted by. The result of this formula is a value of $71.21. It’s a positive amount, indicating that the project should be undertaken as the present value of the future cost savings offset the current investment.

To prove that calculation out, I’ll show you how this calculation could be done manually. Here, for example, is how the present value would be calculated for the $50 in cost savings that is achieved in year 1:


One plus the discount rate is raised to a power of negative one to bring the value back one period, using the discount rate. That returns a value of $47.619. Here are the other present value calculations:

  • Year 2 ($100) : $90.703
  • Year 3 ($250) : $215.959
  • Year 4 ($300) : $246.811
  • Year 5 ($600): $471.116

If you add all of these present values up, they total $1,071.21. And that is $71.21 more than the $1,000 initial investment, which is the same result as the NPV formula.

One thing you may be wondering is at what point does the value equal 0 — where is the breakeven? This can be calculated using the IRR formula. In Excel, this is a simple formula that just takes all the inflows and outflows. For example, if you had the negative investment amount of $1,000 in cell A1 followed by the cost savings in the the adjacent columns (until column F), then the formula for IRR would be as follows:


The end result is a value of 6.8576%. If you use this as the discount rate in the NPV calculation, you will get an NPV value of 0. This tells you that if you use a discount rate higher than this percentage, your NPV value will be negative as the level of discounting will be too high for the project to have a positive NPV value. On the other hand, anything below the IRR rate will result in a positive NPV value and thus indicate that the project should move forward.

If you liked this post on How to Calculate Net Present Value and Internal Rate of Return in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Comments are closed.