How Do You Calculate the Internal Rate of Return?

How Do You Calculate the Internal Rate of Return

Calculating the Internal Rate of Return (IRR) can be quite complex as it involves finding the discount rate that makes the net present value of a series of cash flows equal to zero. In other words, it is the rate at which the present value of the projected future cash inflows equals the present value of the projected future cash outflows.

Mathematically, this is expressed as:

0 = ∑ [Ct / (1 + IRR)^t] – C0

where:

• Ct is the net cash inflow during the period t
• C0 is the initial investment
• IRR is the internal rate of return
• t is the time period

However, since the IRR is not easily solved algebraically when there are multiple cash flows, it is usually calculated using numerical methods, or, more commonly, by using financial calculators or financial software like Microsoft Excel, where you can simply use the IRR function.

For example, using Excel, if you have the initial investment and the series of future cash flows, you can calculate the IRR by using the following steps:

1. In Excel, input the series of cash flows in a column or row. Remember to input the initial investment as a negative number because it’s a cash outflow.
2. Next, use the IRR function to calculate the IRR. The syntax for the function is IRR(values, [guess]). For example, if you’ve inputted your cash flows in cells A1 to A6, you would write the formula =IRR(A1:A6).
3. Press Enter, and Excel will calculate the IRR for you.
4. If you’d like to see the IRR as a percentage, you can change the cell format to a percentage.

Remember, IRR is a sophisticated tool for comparing the profitability of different investments or projects. However, it does have its limitations and should be used in conjunction with other financial analysis tools.

Example of How to Calculate the Internal Rate of Return

Let’s use a simple example to illustrate how the IRR is calculated using Excel.

Let’s assume that you’re considering an investment that requires an initial outlay of $100,000 and is expected to generate returns of $40,000 per year for the next 3 years. Here’s how you would calculate the IRR:

1. First, you need to list your cash flows in Excel. Remember to enter the initial investment as a negative value since it’s a cash outflow:

	A
1	-100,000
2	40,000
3	40,000
4	40,000

2. Next, you use the IRR function to calculate the IRR. If your cash flows are in cells A1 to A4, you can enter the following formula in a new cell:
   =IRR(A1:A4)
3. After you press Enter, Excel will calculate the IRR. In this case, it would return a value of 0.2306 or 23.06% when formatted to display as a percentage. This means that the investment is expected to generate an annual return of 23.06%.

So, if your required rate of return (the minimum rate of return at which you’d accept an investment) is less than 23.06%, this would be a good investment based on the IRR. But if your required rate of return is higher than 23.06%, you should reject the investment.