How to Do IRR Sensitivity Analysis in Excel (with Detailed Steps)

Sensitivity analysis studies how different sources of uncertainty can affect the final output of a mathematical model, and the internal rate of return (IRR) is a discount rate that causes a series of investments to have a zero net present value. If you are looking for some special tricks to know how to do IRR sensitivity analysis in Excel, you’ve come to the right place. There is one way to do analysis in Excel. This article will discuss every step of this method to do this analysis in Excel. Let’s follow the complete guide to learn all of this.

What Is IRR?

The internal rate of return is referred to as the IRR. This is a discount rate that causes a series of investments to have a zero net present value, or NPV. Additionally, an IRR can also be viewed as the compound annual return that a project or investment is expected to generate. So IRR calculations follow the same formula as NPV. In fact, it is the annual return rate of the formula that makes NPV equal to zero. The formula for NPV is as follows.

In this formula:

NPV = Net Present Value,

N = Total number of periods

Cn= cash flow

r = Internal Rate of Return

It is not possible to calculate the IRR directly from the formula due to the summation and the nature of the formula. So we need to approach it from a trial and error perspective while calculating the value of IRR manually. With different values of r, the process iterates until the NPV value of the initial investment is reached or zero, depending on how the problem is approached.

What Is Sensitivity Analysis?

A sensitivity evaluation, in any other case referred to as a “what-if” evaluation or a data table, is any other in an extended line of effective Excel equipment which permits a person to look at what the favored end result of the economic model could be under specific circumstances. Sensitivity analysis studies how different sources of uncertainty can affect the final output of a mathematical model. Excel sensitivity analysis is crucial for any business model. Any financial model’s desired outcome is displayed using the What If command tab. It not only helps in taking crucial decisions for the business’s growth.

In sensitivity analysis, one variable should be used if there is only one requirement, two variables should be used if there are two requirements, and goal seeking can be helpful if there is a sudden change that is required but the desired outcome is already known.

How to Do IRR Sensitivity Analysis in Excel: with Detailed Steps

In the following section, we will use one effective and tricky method to analyze IRR sensitivity in Excel. To do an IRR sensitivity analysis, first we have to insert particulars for IRR sensitivity analysis in Excel, and then we will evaluate EBITDA, calculate IRR, and finally create an IRR sensitivity table. This section provides extensive details on this method. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Step 1: Input Basic Particulars

Here, we will demonstrate how to do IRR sensitivity analysis in Excel. The first step is to input particulars for IRR sensitivity analysis in Excel, and then we will evaluate EBITDA, calculate IRR, and finally create an IRR sensitivity table. To create an IRR sensitivity table in Excel, we have to follow some specified rules. To do this, we have to follow the following rules.

Firstly, write ‘IRR Sensitivity Analysis’ in some merged cells at a larger font size, That will make the heading more attractive. Then, type in your required headline fields for your data. Click here to see a screenshot that illustrates what the fields look like.
Now, after completing the heading part, you have to enter the following Particular, Value, Calculated (Year), and Projected (Year) columns.
Next, you have to enter the EBIT value which you will obtain from the income statement.
Then, type the depreciation and amortization value.
Afterward, you will get the EBITDA by adding the EBIT with depreciation and amortization.

Step 2: Evaluate EBITDA and Equity Value

In this step, we are going to calculate EBITDA and equity value. We will get the EBITDA by adding the EBIT with depreciation and amortization. Here, we use the IF function to calculate Inflows. To calculate EBITDA and equity, you have to follow the following process.

First of all, to calculate the EBITDA, we have to type the following formula.

=G6+G8

Then, press Enter.
Therefore, you will get the EBITDA for the year 2020.

Then, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the other year’s EBITDA.

Then, you have to input the EBITDA Multiple as shown below.
Next, to calculate the Inflows, we have to type the following formula.

=IF(G5=$C$6,G10*G9,0)

Then, press Enter.
Therefore, you will get the Inflows for the year 2020.

Then, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the other year’s Inflows.

Then, to calculate the Ownership, we have to type the following formula.

=G11*$F$12

Then, press Enter.
Therefore, you will get the Ownership for the year 2020.

Then, drag the Fill Handle icon to the right to fill other cells with the formula.
Consequently, you will get the other year’s Ownership.

To find out the value of equity, we have to copy the ownership value and then paste it.

Step 3: Calculate IRR

There are three Excel functions dedicated to calculating IRR directly. In order to determine the IRR for a cash flow, you must determine the period and the type of cash flow. There is a slight difference in the results returned by each function. Consider your desired outcome carefully.

Here, the function we are going to use is the IRR function. For a series of cash flows, this function returns the internal rate of return. The amounts of these cash flows do not have to be equal. However, their intervals should be equal. This function does not take into account time periods- it only considers cash flows. If you have an irregularity in the payments, the function will not calculate their time value correctly. Resulting in a slight error. In spite of this, the result can be rounded up to an appropriate IRR value based on the apparent result. For arguments, the function takes two values. The primary one is a range of values and the optional one is called the guess which is the estimation of expected IRR.

To calculate the IRR, we have to type the following formula.

=IRR(F13:L13)

Then, press Enter.
Therefore, you will get the IRR value.
Here, we assume that our target IRR value is 45%.

To calculate the Difference, we have to type the following formula.

=C9-C10

Then, press Enter.
Therefore, you will get the 3% difference between the actual and target IRR.

💡 Note:

To calculate the IRR value, we can also use MIRR and XIRR functions and also use the conventional formula.
The conventional formula is the NPV formula which we describe at the start of the article. In this formula, you have to find the value of IRR by trials for which the summation of all the cash flow values gets closest to zero (NPV to zero).

Step 4: Create IRR Sensitivity Table

Now we are going to create an IRR sensitivity table. To do this, you have to follow the following process.

First of all, you have to copy and paste the actual IRR value into cell B16 using the following formula.

=$C$9

Next, press Enter.

Then, select the range of the cells as shown below, and go to the Data tab.
Next, select What-If-Analysis and select the Data Table.

Therefore, the Data Table window will appear.
Then, insert the desired cells into the Row input cell and the Column input cell as in the below image and click on OK.

Therefore, you will get the following IRR sensitivity table. If you change input values in a worksheet, the values calculated by a data table change too.

💡 Note:

You cannot delete or edit a portion of a data table. If you select a cell in the data table range and edit it accidentally, the Excel file will prompt a warning message and you can’t save, change, or even close the file anymore. The only way that you can close it is by ending the task from task management. It means that your time and effort will be wasted if you did not save the file before making that mistake.
The automatic calculation is enabled by default, and that’s the reason why any change in the inputs can cause all the data in the data table to be recalculated. This is a fantastic feature. However, sometimes we’d like to disable this feature, especially when the data tables are large and automatic recalculation is extremely slow. In this situation, how can you disable automatic calculation? Just click the File tab on the ribbon, choose Options, and then click the Formulas tab. Select Automatic Except For Data Tables. Now all your data in the data table will be recalculated only when you press the F9 (recalculation) key.

💬 Things to Remember

✎ No further operation is allowed in a data table as it has a fixed structure. Inserting or deleting a row or column will show a warning message.

✎ The data table and the input variables for the formula must be in the same worksheet.

✎ When making a data table, do not mix up your row input cell and column input cell. This kind of mistake can result in a big error and nonsensical results.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

IRR Sensitivity Analysis.xlsx

Conclusion

That’s the end of today’s session. I strongly believe that from now you may be able to do IRR sensitivity analysis in Excel. If you have any queries or recommendations, please share them in the comments section below. Keep learning new methods and keep growing!

<< Go Back to What-If Analysis in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!