Methods For Using Excel to Determine The Internal Rate Of Return

Jan 13, 2024 By Triston Martin

Whenever a sequence of cash flows in the future is discounted at a rate other than zero, that rate is known as the internal rate of return. Selecting investments with the highest expected returns requires the usage of the IRR and net present value.

Comparison Between IRR And NPV

The predicted interest yield as a percentage is the IRR, whereas the net present value is the major difference between the two. When choosing investments, financiers choose projects with an internal rate of return higher than the cost of capital.

However, the possibility of generating a return on investment larger than the weighted average costs of capital but less than the current return on existing assets increases if projects are selected based on maximizing the IRR rather than the NPV.

Only when the project generates no intermediate cash flows, or if the capital can be reinvested at the current IRR, does IRR represent the true yearly return on investment? Therefore, optimizing NPV is not the objective.

What Is Present Value Net?

The formula for determining the net present value is to take the present value of cash inflows and subtract it from the present value of cash outflows. Any project's net present value may be calculated by adjusting the discount rate. The choice of the discount rate, which is typically dependent on a degree of uncertainty, will thus have a major influence when evaluating two investment prospects.

Using a discount rate of 20%, the example below demonstrates that Investment #2 is the more profitable option. At the more conservative discount rate of 1%, investment #1 again outperforms investment #2. The cash flows of a project, their relative importance, and the discount rate used all influence how profitable the project will be.

How Does the Internal Rate of Return Work?

The IRR is the discount rate at which the NPV of an investment may be reduced to zero. It's more engaging to compare the profitability of various investments using this criterion when the IRR has only one value.

The IRR on Investment #2 is 80%, whereas that on Investment #1 is 48%. As a result, under the first investment scenario, a 2013 investment of $2,000 will provide a yearly return of 48%. In the second investing scenario, if you put away $1,000 in 2013 and collect the income, you'll earn 80% yearly.

Without any input, Excel will begin trying out various IRR values for the entered series of cash flows, stopping when a rate is chosen that results in an NPV of zero. When Excel cannot discover a discount rate that brings the NPV down to zero, the error "#NUM" is shown. Without the second option, Excel will only show the first rate it finds that gets the NPV to zero; thus, we won't know whether the investment has several IRR values.

Variations in the Internal Rate of Return

The cash flows in the next example are not distributed annually at the same time as in the preceding cases. It's more accurate to say that they're occurring at separate times. To finish this math, we will use the XIRR function.

First, we decide on a range of cash flows, and then we decide on a range of realization dates. Though more common, Excel lacks tools that may be used to account for investments that have cash flows received or cashed at different times for a company that has variable borrowing rates and reinvestments. 2

Example of Determining the Internal Rate of Return

The $85 million we assumed for Year 0 equity investment is set in stone. The initial investment will be worth the same amount in whichever year the company chooses to cash out. A negative indicator will be used since money is being spent on the investment. The exit cash inflows, meanwhile, are the buyer's share of the sale price.

Specifically, from the original $85 million invested, the exit revenues are expected to grow by $25 million annually. Therefore, in Year 1, the exit proceeds are $110m, and in Year 3, they are $160m. To determine the IRR and MoM for this investment, we first need to create a table showing the cash outflow in Year 0 and the cash inflows at various periods over the holding period.

Related articles
Budgeting for the Financial Phases of Retirement
Your retirement years might easily stretch into the decades if you are physically well and have sufficient savings. Many people in the United States cannot save enough money to retire, and one in four Americans has no savings.

Oct 06, 2023 Triston Martin

See: How To Get Car Insurance With No License?
For various reasons, even those without a driving license may need auto insurance. For instance, you may no longer be able to drive for health or age-related reasons, but you may still own the car that a loved one uses to transport you to doctor's appointments. A judge may have ordered you to get car insurance even though your license is suspended.

Nov 29, 2023 Susan Kelly

Deciding Between Savings Accounts and Bonds: What You Need to Know
Understanding the differences between savings accounts and bonds. Make informed financial decisions.

Feb 05, 2024 Triston Martin

The Evolution of Economic Policies Under Alan Greenspan
Explore the journey of Alan Greenspan, his economic policies, and their profound impact on the American and global economy. A deep dive into his legacy and controversies.

Dec 29, 2023 Susan Kelly

How Do Credit Card Companies Make Money: A Complete Guide
Fees are how credit card companies generate money. Interest costs are the largest source of revenue among the numerous fees. When credit card customers do not pay their bills in full at the end of the month, the bank is permitted to impose interest on the borrowed amount. Other expenses, such as yearly and late fees, contribute as well, but to a smaller level

Dec 26, 2023 Triston Martin

A Complete Overview of Municipal Bond
Municipalities, including states and cities issue bonds as well, although they don't garner as much publicity as corporate bonds. Municipal bonds, sometimes known as "muni" bonds, have a sizable market. The same is true for municipal bonds, which have several advantages over commercial and federal govt bonds

Jan 13, 2024 Triston Martin