Net Present Value (NPV) and Internal Rate of Return (IRR) are two fundamental tools used in finance to assess and compare investment opportunities to make business decisions.

NPV is an absolute measure of the difference between the present value of cash inflows and the present value of cash outflows over a specific period of time. It considers the time value of money, which means that a dollar received in the future is worth less than a dollar received today due to risk factors, such as inflation, volatility or uncertainty, and the opportunity costs of investing the money elsewhere.

IRR is the discount rate at which the present value of an investment's cash inflows equals the present value of its cash outflows. It represents the rate of return that makes the NPV of an investment zero. In simpler terms, IRR is the rate of growth an investment is expected to generate.

How to Calculate NPV

In order to calculate the NPV, you need to:

1. Identify the cash flows for each year of the project.
2. Compute the present value of each cash flow using the discount rate, variously known as the hurdle rate or the weighted average cost of capital (WACC).
3. Add up the present values of all cash flows.

NPV Formula

The formula for calculating NPV is:

Where:

C = Net cash flow in a given year n

i = WACC or discount rate

Initial investment = Cash outflow

If the NPV is positive, it indicates that the project is expected to generate more cash inflows than outflows, given the project risk, making it financially favorable. A negative NPV suggests the opposite.

Example of Calculating NPV

Let’s walk through a real-world scenario to see how this works. Suppose a company is considering investing $100,000 in a new project that is expected to generate the following cash flows over a period of five years:

     Year 1: $30,000
     Year 2: $35,000
     Year 3: $40,000
     Year 4: $25,000
     Year 5: $20,000

The company's WACC is 10%.

When discounting future cash flows to their present value, use the formula for each time period:

     Present Value =  Future Value / (1+ Discount Rate)ⁿ

Where n equals the year/compounding effect over time. For example, if the discount rate is 10%, adding 1 to .10 represents adding 100% of the original value to account for the entire value plus the interest (or discount rate): (1 + .10) = 1.10.

Using the steps above, calculating the NPV looks like this: 

Discount rate = 10%	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
Identify cash flows for each period	($100,000)	$30,000	$35,000	$40,000	$25,000	$20,000
Calculate present value	($100,000)	$30,000/1.1 = 27,272.73	$35,000/1.12 = 28,925.62	$40,000/1.13 = 30,052.59	$25,000/1.14 = 17,075.34	$20,000/1.15 = 12,418.43
Add values	($100,000)	$115,744.71
NPV =	$15,744.71

The NPV is positive, meaning the project is expected to generate more cash inflows than outflows, making it financially viable.

Calculating NPV in Excel

You can calculate NPV using Excel's built-in function. Here are the steps:

1. Enter cash flows: In one row of the worksheet, list the cash flows of your investment project.
2. Enter the discount rate: Know the discount rate you want to use. This could be your required rate of return or the cost of capital.
3. Enter the NPV formula: In a cell where you want the NPV displayed, type: = NPV (discount rate, range of cash flows) + initial investment. Then press Enter. Important: If you have an initial investment that is not being discounted (i.e., Year 0 in cell B2), be sure to exclude it from the range of cash flows that are discounted (C2:G2).

Using the scenario above, it would look like this, with the formula in cell B4=NPV(B3,C2:G2)+B2:

Learn when and where to use key financial analytics and how to use pivot tables in Excel with the AFP Digital Badge: Applied Financial Data.

IRR Formula

The formula to calculate IRR involves finding the discount rate that satisfies the equation:

     0 = NPV = Cash Flown / (1 + IRR)ⁿ

Essentially, the IRR is the rate at which the investment breaks even. If the IRR is greater than the cost of capital or required rate of return, it means the investment is expected to generate returns greater than the cost of capital, making it potentially profitable. If the IRR is lower than the cost of capital, the investment may not be financially viable.

If you have a list of projects to invest in and want to know which one uses capital most efficiently, the IRR provides a convenient comparison because they are all calculated to the same standard of NPV = 0. Calculating the IRR requires trial and error by cycling through all of the possible combinations; fortunately, this can be accomplished through a spreadsheet.

Calculating IRR in Excel

The fastest way to calculate IRR is by using Excel.

The syntax for IRR is =IRR(values,[guess]), where values is the range of values and guess is an optional guess at what the IRR percentage should be. Note that if an investment has net costs in the future rather than just net revenue, it may have multiple IRRs and only the first rate found will be returned. In this case, a different function, such as MIRR, needs to be used because it can return multiple IRRs.

Below is an example of the basic IRR function using the same information from the initial NPV example. The formula in cell B5 = IRR(B2:G2)

The cost of capital represented by the discount rate is 10%. The project is expected to return 16% on that capital and, therefore, adds value.

Should I Use NPV or IRR?

NPV is generally the preferred approach in corporate finance when assessing project value due to its unique ability to effectively handle scenarios with fluctuating cash flows and diverse discount rates. Unlike IRR, NPV estimates a precise figure representing the present value of anticipated future cash flows, factoring in the time value of money and the associated risks.

Flexible in accommodating varying discount rates, NPV allows you to tailor the analysis to your specific risk profile and return expectations. Because of its ability to personalize the assessment, NPV offers a more accurate and relevant measure for comparing investment opportunities within capital budgeting decisions.

IRR is most helpful when comparing projects or investments or when finding the best discount rate proves elusive. Unlike NPV, it calculates a rate of return based on a project's cash flow and initial investment. This helps investors compare opportunities accurately, considering both returns and risks; private equity and hedge funds commonly use IRR for this very reason.

Often, it is helpful to use both measures at the same time. For example, a project may require a very large investment, consuming all of a company’s capital and producing a large NPV. That big, positive total value may make this look like a winner, but the project could have a low IRR, indicating the rate at which it generates those returns is low. It may be better to choose three smaller projects that generate the same total NPV if they all have higher IRRs.