Whether you’re an in-house accountant or function as an accountant consultant, you should be able to support with your clients with financial viability of projects using both the internal rate of return (IRR) and the net present value (NPV). To help you provide the best support, we're sharing how to calculate internal rate of return and net present value, and how to navigate these calculations in Microsoft Excel.
Follow along in the IRR worksheet of the workbook IRR.xlsx. This spreadsheet shows annual cash flows from two projects, as depicted in Figure 1.
Figure 1 Evaluation of two projects by NPV and IRR
Jump to calculating IRR in Excel
Download our FREE ebook Excel automation for accountants
With step-by-step tutorials and real world examples, learn valuable automation functions in Excel that save time, improve accuracy, and and enhance your skills!
NPV meaning and formula
Before we look at how to calculate NPV in Excel, we're sharing the definition and standard NPV formula in case you need a refresher.
What is net present value?
Net present value is the present value of the cash flows at the required rate of return of your project, compared to your initial investment. Using this in capital budgeting and investment planning allows you to analyze the profitability of the investment.
How to calculate net present value
To calculate NPV, you are seeking to find the current value of a future stream of payments. The traditional formula is:
- i equals the required return or discount rate
- t equals number of time periods
The simpler formula to calculate NPV can be written out as:
- NPV = Time period's value of expected cash flows - Time period's value of investment
IRR meaning and formula
Before we dive into how to calculate the internal rate of return, get a refresher for what this is and the formula for calculating the internal rate of return.
What is the internal rate of return?
The internal rate of return is a metric used to evaluate the profitability of an investment that accounts for the time value of money (specifically the link between present value of money and the future value of money for the investment). It is the rate at which the present value of the investment's cash inflows equals the present value of its cash outflows.
Formula for calculating internal rate of return
The formula for calculating IRR is the same as NPV where NPV value is equal to zero. The formula for IRR can be written: 
In the formula:
- N equals the number of periods
- n equals a positive integer
- C equals cash flow
- r equals the internal rate of return or discounted rate
- NPV equals net present value
In simpler terms, you can write the formula for n cash flows as: 0 = C1/(1+r) + C2/(1+r)2 + C3/(1+r)3 - C0
Or you can take the much easier route and calculate IRR in Excel!
Calculating NPV in Excel
To utilize NPV a company assigns a discount rate to a project’s cash flows. A project with an NPV>0 helps the company. A project with an NPV<0 hurts the company.
The formula =NPV(0.2,C2:I2) in cell J2 computes the value of Project 1 in today’s dollars, using a 20 percent annual discount rate. We find an NPV of $266.54, under the assumption that $1 a year from now is calculated with formula =$1/1.20.
Keep in mind: cells J1:K2 account for a discount rate of 20 percent. With this discount rate, Project 2 has a larger NPV. If we use a discount rate of 2 percent, Project 1 has a larger NPV. It’s often difficult to agree on a discount rate. This is when IRR becomes useful.
The IRR of a sequence of cash flows is the discount rate that makes the NPV of the cash flows equal to 0. There are several problems that can occur with IRR that we will discuss later, but in most situations, IRR tells you the rate of return you earn on your investment.
How to calculate internal rate of return in Excel
The syntax of the IRR function is IRR(range of cash flows, [guess]). The guess argument is optional and should be a “guess” of what you think the IRR is. In most situations, you don’t need to enter the guess argument, but it is can sometimes be important.
To be on the safe side, you should enter a range of guesses. Copying from B8 to B9:B17 the formula =IRR($C$2:$I$2,A8) shows us the IRR for Project 1 is 47.5 percent. Similarly, the formulas in C8:C17 show that for all guesses, our computed IRR is 80.1 percent. Essentially, this means that our investment in Project 1 earns an annual 47.5 percent rate of return, and our Investment in Project 2 earns an annual rate of return of 80.1 percent
How to calculate internal rate of return with cash flows
How can we calculate IRR when cash flows don’t occur at regular intervals? Excel’s amazing XIRR function comes to the rescue. Simply input the dates and values of the cash flows and Excel will return an investment’s annual rate of return. For example, in our worksheet, XIRR we find that the cash flows occurring on the displayed dates yield an annual IRR of 18.3 percent.
Figure 2 An example of the XIRR function
Does every investment have a unique IRR?
The three-period project shown in the No IRR worksheet of our spreadsheet has no IRR. This means there is no discount rate that will make the NPV of this project equal to 0.
Figure 3 A project with no IRR
As shown in the Multiple IRR worksheet, the project shown has two IRRs: -9.6 percent and 216.1 percent. We found their IRRs by varying our guess for the project’s IRRs. Both these discount rates yield an NPV of 0. There is no practical interpretation of IRR in this situation.
If a sequence of cash flow begins with a negative cash flow and is followed by all non-negative cash flows, then you are guaranteed a unique IRR. Many projects meet this requirement because they start with a negative cash flow and then later cash flows are the positive returns on the investment.
Figure 4: A project with two IRR’s
How are NPV and IRR related?
A sequence of cash flows has a positive NPV if and only if its IRR is greater than the discount rate. For example, a project with a 9 percent IRR will have a positive NPV for a discount rate less than 9 percent and will have a negative NPV for a discount rate of 9 percent.
Should we always choose the project with the larger IRR?
Consider the two sequences of cash flows in the Which Project worksheet. Project 2 has an IRR of 50 percent, because $1 grows in one year by 50 percent. Similarly, Project 1 has an IRR of 40 percent. If we were only allowed to choose one project, basing our decision on IRR would result in us choosing Project 2. This would be foolish, however, because Project 1 would put us in a much better Time 1 cash position than Project 2. This simple example shows that the IRR criterion may ignore the scale or size of the projects and yield to poor decision-making.
Figure 5 Don’t choose the project with largest IRR!
IRR and NPV are essential functions in Microsoft Excel because they can help provide insight into financial forecasting for projects. Keep this resource handy and learn more Microsoft Excel functions for accountants with a CPE course.
Take the next step with CPE courses
If you want to learn more about how to calculate the internal rate of return or leverage Excel's other data analysis capabilities, Becker has a wide variety of CPE courses to help you build your knowledge and skills, including:
Get these Excel courses plus over 700 on-demand CPE courses, 1,000 live webcasts each year, plus weekly CPE podcasts with a Prime CPE subscription. This subscription offers unlimited credits plus the best value in continuing professional education.
Related Articles
Share
