How to Use the IRR Formula in Excel

The IRR formula in Excel is used to determine the discount rate which makes the NPV equal to zero. If you calculate the IRR and it is greater than your expected rate of return (discount rate) then you should accept the project as an investment.

Higher IRR projects have higher growth potential.

Formula Explanation:

• Rate: In the IRR calculation, the discount rate is not known and is a variable that is being solved for.
• Value: The amount of cash flows that will be returned by the investment

Recall that the NPV formula was as follows:

• NPV = – (initial cash outflow) + (sum of discounted future cash flows)
• NPV = – (initial cash outflow) + ( (cash inflow year 1) / (1 + discount rate) ) + ( (cash inflow year 2) / (1 + discount rate) )…etc, depending on number of years of future cash inflows

The IRR formula is similar to the NPV formula except the NPV is replaced with zero and the rate of return is an unknown and replaced with the “IRR” variable to find. Therefore:

• 0 = – (initial cash outflow) + (sum of discounted future cash flows)
• 0 = – (initial cash outflow) + ( (cash inflow year 1) / (1 + discount rate)) + ( (cash inflow year 2) / (1 + discount rate) )…etc, depending on number of years of future cash inflows

Note:

• In the case of calculating the NPV, the discount rate is known as it is considered the expected rate of return on the investment
• Conversely, in the case of calculating the IRR, the discount rate to make NPV zero is not initially known. That is the value we are seeking to understand. Once we have it, we can then compare the IRR % to the NPV discount rate %. If it is higher than the acceptable rate of return, then it is a good investment decision.
• ‘If a project is undertaken, and the IRR is equal to the company’s cost of capital (acceptable discount rate), it means the present value of future cash flows is only covering the cost of capital and no accretion is given to shareholder value. Therefore, IRR must be higher than the cost of capital in order to have a project that will create value for shareholders.
• For example, if a company’s weighted average cost of capital is 8% and the IRR is 7%, the project showing a potential IRR of 7% should be declined because it would cost the company more money through its debt and equity financing than it would yield benefit.
• IRR can be calculated in Excel using the XIRR formula. XIRR is more accurate than the IRR function in Excel. IRR in Excel considers all time periods equal whereas XIRR allows for specific dates to be considered.

Formula

Formula Explanation:

• Values (required): Amount of cash flows returned
• Dates (required): The dates the cash flow will be realized
• Guess (optional): Guess means the assumption of users about the possible internal rate of return.

Example #1:

Assume upfront investment on a project is $100 and the return in year 1 is $120. The company’s cost of capital is 8%. What are the NPV and IRR?

Solution:

Therefore, since the IRR is 20% and the company’s cost of capital is 8%, they should pursue this project.

Example #2:

The upfront investment on a project is $120k. The company’s weighted average cost of capital is 8%. The timing of future cash flows is in the below table. Should the company accept the project?

Solution:

1) Net Cash Flow Calculation:

2) NPV Calculation:

3) IRR Calculation:

Therefore, the IRR of 7.53% is less than the discount rate of 8% and the project should not be pursued. The NPV is negative even though it yields positive total undiscounted future cash flows.

Example #3:

Apple branch & Co. made two investments named Zebra and Xacto. Owners of the company want to find out which investment provided them with more return.

The cash flows along with dates of returns are given below:

Solution:

Example #4:

A company is considering two mutually exclusive investment options, Option A and Option B. The cash flows for each are as follows:

Which option (A or B) will be preferred by using IRR as the basis of decision making?

Solution:

(i) Manual method

We can calculate the IRR manually using the following formula:

IRR=A+(P/(P+N))*(B-A)

Where,

• A is the (lower) rate of return with a positive NPV
• B is the (higher) rate of return with a negative NPV
• P is the value of the positive NPV
• N is the absolute value of the negative NPV

We also have to use a discount factor which is formulated by 1/(1+r)^n

Step 1: Finding two NPV (a negative value that is close to zero and a positive value that is close to zero) by using discounted cash flows.

Step 2: Calculating the IRR by using the above formula with NPVs and discount rates.

For Option A

Here, the total receipts are 14,000 (6,000+5,000+3,000) giving a total profit of 3,800 and an average profit of 1,267 per year.

The average investment (opening + closing / 2) is $5,100.

The average return is $1,267/$5,100 = 25%.

We can take two-thirds of ARR for the initial estimate of the IRR that will be approximately 17%.

For Option B

Here, the total receipts are $48,000 (18,000+15,000+15,000) giving a total profit of $12,750 and average profit of $4,250. The average investment is $16,000. The ARR is $4,250/$16,000= 27%.

We can take two-thirds of ARR for the initial estimate of the IRR that will be approximately 18%.

(ii) IRR calculation by Excel

The calculation of IRR is given below:

Here, Option A is the profitable option, because it gives higher IRR than Option B. The formula used is =IRR(B24:B34)

Example #5:

Hazzard Group is considering a proposal to purchase and install a new machine at a cost of $50,000. The machine has a life expectancy of five years and no salvage value.

The company’s tax rate is 35%. Assume the company uses straight-line depreciation and the same is allowed for tax purposes. The estimated profit before depreciation and before tax from the investment proposal is as follows:

The Company’s weighted average cost of capital is 6%. The Company would like to make an investment decision using IRR.

Solution:

Calculation of profit after tax:

Calculation of Internal Rate of Return:

Therefore, the proposal should be accepted as the IRR at 8% is higher than the company weighted average cost of capital of 6%.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!