Calculating IRR manually is time-consuming and complicated. Math-based solutions will require you first to calculate the NPV (net present value) and then use the given interest rates to find IRR on a trial and error basis. Fortunately, the process is made simple with the availability of Excel. Three functions for calculating IRR in Excel are illustrated below.
Excel’s IRR Function
The function calculates IRR from a series of cash flows by assuming the payments are equal per cash flow. Nevertheless, calculation of IRR in Excel can be challenging because of the slight differences in the number of days in each month. Some months have 30days, whereas others have 31. To find IRR using Excel's IRR function;
1. Type the formula
IRR formula is given as IRR (values, [guess]). Make any dynamic range of values on your worksheet based on this formula.
2. Find the values
Values represent a series of cash flows whose IRR is to be calculated. The range of values can be investments and incomes. Give your values some names if needed; note that naming is optional.
3. Find the guess
Guess values that are close to the expected internal rate of return. Remember IRR usually has two solutions or even more; one can be positive and the other negative.
Excel’s XIRR Function
The XIRR function calculates IRR accurately compared to the IRR function, considering the number of days per month. To find IRR using the XIRR function in Excel;
1. Type the formula
XIRR formula is given as: (values, dates, [guess]
=XIRR(B2:B6,C2:C6,1)
2. Find the values
The values represent a series of corresponding cash flows to be used. They can be payments or incomes both for regular and irregular intervals. All values should be numeric, or else there will be an error, and you will not arrive at any result.
3. Find the dates
The XIRR function calculates the IRR for a series of regular dates and periods. Each cash flow is not needed to be arranged chronologically.
4. Find the guess
With XIRR, finding the guess is optional. You can use Excel's default value of 0% or estimate any other value
Excel’s MIRR Function
The MIRR (modified internal rate of return) works similarly to the IRR and the XIRR function. It produces the most accurate figure considering the cost of borrowing, the initial investment, and the compound interest. To find IRR using the MIRR function;
1. Type the formula
MIRR formula is given as (values, finance rate, reinvest rate)
=MIRR(B2:B6,6,8)
2. Find the values
The values entail a reference to cells that represent the cash flows. Just like the other functions, the MIRR should have at least one positive and one negative value; otherwise, you will not get a solution
3. Find the finance rate
The finance rate is the percentage of the required rate of return. Precisely, it is the interest or cost of borrowing that an investor uses to finance an investment. You can type any name for the finance rate, i.e., cell B1
4. Find the reinvest rate
The reinvest rate is the sum of interest received on all the given cash flows as a percentage or decimal numeral. You can also use the weighted average cost of capital as the reinvest rate. Optionally, name the reinvest rate, i.e., cell B2.
