This quick and effortless Microsoft Excel tutorial shows how to calculate IRR in Excel.

You can calculate return on investment (ROI) in Excel in various ways. One such option is to calculate the fixed rate of return calculation. While you can always use the fixed rate-based method, that might not give you the right insight in complex business or investment scenarios where you invest a sum over a long period and also get a regular income.

Here steps in IRR. It considers the complex investment components in financial products like mutual funds, direct investment in a business, or 401(k).

Read the article until the end to learn all the tried and tested methods of calculating IRR in Excel with real-world datasets. I’ll also show short steps and illustrations so you can follow along with your own worksheet.

What Is IRR?

Internal Rate of Return (IRR) is a popular tool used to assess potential profitability. It calculates the discount rate that makes all future cash flows from an investment, both positive and negative, equal to zero. However, IRR has limitations. It doesn’t factor in external influences like inflation, risk, or the cost of capital.

For long-term investments, like business ventures, mutual funds, or retirement plans, IRR offers a starting point. But it shouldn’t be the sole deciding factor.

Suppose, a company offers you to buy a website for $100K. The seller shows a projected revenue of $15,000 for the next 10 years.

In another deal, a hedge fund asks you to invest $100K for 10 years. In return, you get $10,000 each year for the next 10 years and the principal amount back. Which one is a better deal?

Make an informed decision by choosing one of the above two investment options by calculating IRR in Excel.

Calculate IRR Using the IRR Function

Excel IRR function is the default formula to calculate the internal rate of return. It generates the annual return rate in percentage.

Before you can start using the IRR function, keep the following in mind:

• The Values part of the IRR function requires both income (positive cash flow) and expense (negative cash flow) for accurate processing.
• Only numerical values within the Values argument are processed by the IRR function. It disregards all types of logical values, empty cells, or text strings.
• The positive or negative cash flow must be at a regular interval, like weekly, fortnightly, monthly, quarterly, bi-yearly, yearly, etc.
• The Guess part of the function isn’t mandatory. However, if you use it, Excel can generate better results when not used.
• When utilizing a Guess value, the IRR formula approximates the nearest value to the Guess argument. If an error such as #NUM! occurs, adjust the Guess argument value.

📒 Read More: 6 Ways to Fix Microsoft Excel Cannot Calculate a Formula Error

Now that you’re aware of the guidelines for using the Excel IRR function, let’s see below its application:

Organize your investment and cash flow dataset as shown in the above example. Here’s an explanation of the data components:

• Years: It could be weeks, months, years, or whatever frequency cash flows out or in.
• Cash Flow: This column shows all the incomes and expenses related to the investment project.

Now, select the cell where you want to populate the IRR value. Into it, enter the following formula:

=IRR(B2:B12,0.1)

In the above formula, B2:B12 represents the Values argument and 0.1 represents the Guess argument of the IRR function.

Hit Enter to get the IRR value.

Use the Increase Decimal button inside the Number commands block of the Home tab to express the IRR in up to decimal places.

Calculate IRR Using the XIRR Function

If you’re investing in a business or mutual fund in regular intervals of months or years and would like to get a time-sensitive calculation of IRR, you should use the XIRR function.

For example, according to the above dataset, you’re investing $10,000 each year in a mutual fund. You expect to start investing from 1/5/2024 and it’d last until 1/5/2035.

Also, you forecast that by 2/5/2035, you can sell all the units of your mutual fund account at $200,000.

In this scenario, different portions of the entire investment value are generating returns for different periods. For instance, the investment of 1/5/2024 will last for 12 years, the next deposit of 1/5/2025 will last for 11 years, and so on.

So, calculating the overall IRR becomes highly complex.

Microsoft Excel included all the time-dependent considerations in the XIRR function. All you need to do is enter the Values, Dates, and Guess arguments to quickly fetch the internal rate of return, compensated for time.

Considering that your input dataset already is in the format shown above, highlight any cell and enter the following formula into it:

=XIRR(B2:B14,A2:A14,0.1)

In the given formula, B2:B14, A2:A14, and 0.1 represent the Values, Dates, and Guess arguments of the XIRR formula.

Hit Enter to get the ROI you’ve been looking for.

Don’t forget to round up the value up to two decimal places to create an accurate representation of the rate of interest value.

Calculate IRR Using the MIRR Function

Often, you borrow money from a lender and invest that into a business or any other investment plan to get a regular income. Using that income, you aim to repay the borrowing and also make a profit.

So, the IRR of the investment must be greater than the interest on the borrowed amount. This ensures that you can profit from this venture. In this case, you can use the MIRR function.

To calculate MIRR, in addition to the Values argument, you also need the weighted average cost of capital (WACC) or financing rate and reinvestment rate.

WACC is the rate at which you borrow capital from the lender. The reinvestment rate is at which ROI you’ll reinvest the return from the first investment plan.

Consider the investment dataset shown above. You invest $100,000 in the 0th year. From the next year onwards, you start earning $15,000 per year for the next 10 years.

The database also shows the following required inputs for MIRR:

• Annual Finance Rate: 6% annually
• Annual Reinvest Rate: 10% annually

Now, I can calculate the MIRR using the following formula:

=MIRR(B2:B12,E2,E3)

The cell range B2:B12 tells Excel the source of the Values argument, E2 represents the financing rate, and E3 represents the reinvestment rate.

I entered the formula in E4 and hit Enter to get the MIRR value of 9.11%.

I used the Increase Decimal button to express the percentage value to up to two decimal places.

From the findings of the MIRR data, you can see that the investment project is profitable since you can borrow at 6% and earn at 9.11%, with a profit margin of 3.11%.

Calculate IRR to Compare Multiple Options

You can’t just decide where to invest considering the IRR values of different projects. You also need to look at the total return the project is generating, the stability of the project, and many more.

For example, the above dataset compares three investment projects. Here’s a simple analysis of the data:

• Option 1: Fetching a return of $1,680.30 at an IRR of 49%.
• Option 2: Giving a return of $1,015.00 at an IRR of 12%.
• Option 3: Generating a total income of $3,570.00 at an IRR of 32%.

You should definitely avoid Option 2 since it doesn’t generate enough returns for you.

Option 1 does show a whopping 49% IRR but the accumulated return is nominal, $1,680.30 after 7 years of investment.

Option 3 gives an ever-increasing return at an IRR of 32%. The accumulated sum is also higher than all other options. So, you can go with Option 3.

This is how you can organize the investment and return data from multiple investment options and use the IRR formula as well as the sum returned to choose an option.

Find Out When an Investment Yields a Positive IRR

When choosing the best investment plan, it’s also important to know how soon the project will start generating a positive IRR.

Go to your input dataset where you’ve already organized the investment details in Years and Cash Flow columns.

Create a new column to the right side of Cash Flow and name it Periodical IRR.

You need to calculate IRR from the first year when you start receiving an ROI, which is represented in cell B3.

Select cell C3 and enter the following formula into it:

=IRR($B$2:B3,0.1)

Hit Enter to get IRR for the first year.

Now, drag the fill handle down from the cell C3 until the cell C12. This will copy the formula and calculate the IRR values for the rest of the years.

From this IRR data analysis, you can see that the investment project started giving positive returns in the 7th year.

Before using the above formula, adjust it according to your worksheet so it calculates an accurate value.

Calculate IRR Using Solver

You can also use the Solver add-in of Excel to calculate IRR and create an itemized list of returns for an investment project. In this method, you mainly use the PV formula to calculate an NPV. Then, use the Solver tool to perform iterative calculations to find the IRR when NPV becomes zero.

Does the method sound complicated? Don’t worry! Just try the steps below and you’ll find that it’s fairly simple.

I’m considering that you already have an investment planning database as shown above.

Now, create the column and row headers as shown in the above image.

In the first cell of the Present Value (PV) column, enter the PV formula as shown below:

=B2/(1+$E$2)^A2

Hit Enter to calculate PV for the negative cash flow.

Now, use the fill handle to generate the PV for the rest of the cells of the Present Value (PV) column.

Now, calculate NPV using the following formula:

=SUM(C2:C12)

Go to the Data tab and click on the Solver command button to launch the add-in. If you don’t see the option, check out this Excel tutorial:

📒 Read More: 2 Ways to Install Solver in Microsoft Excel

Here’s how you should configure the Solver dialog box:

• Set Objective: Select the calculated NPV cell, which is C13.
• Value Of: Set it to 0.
• By Changing Variable Cells: Select the cell where you want the IRR value to be calculated. be mindful that you must have also used this cell to calculate PV in the Present Value (PV) column.

Now, hit the Solve button.

Excel Solver add-in shall calculate the IRR and put it in the cell E2.

Subsequently, Excel shall automatically calculate the PVs in column C.

Moreover, you’ll see that the value of NPV is either 0 or close to it.

Calculate IRR Using Excel VBA

If you use Excel VBA you can effortlessly calculate IRR without remembering any formula and their arguments. Also, if you don’t know much about IRR, NPV, etc., you can still calculate IRR by simply following the visual instructions shown by the VBA macro.

Find below a VBA script that guides you through calculating IRR visually by choosing inputs from your own worksheet:

Sub CalculateIRR()

    ' Declare variables
    Dim rngValues As Range
    Dim dblGuess As Double
    Dim rngDestination As Range

    ' Prompt user to select range of values
    On Error Resume Next
    Set rngValues = Application.InputBox("Please select the range of values for the IRR calculation:", Type:=8)
    On Error GoTo 0

    ' Exit if no range selected
    If rngValues Is Nothing Then Exit Sub

    ' Prompt user to input guess value
    dblGuess = Application.InputBox("Please enter your guess value for the IRR calculation:", Type:=1)

    ' Prompt user to select destination cell
    On Error Resume Next
    Set rngDestination = Application.InputBox("Please select the destination cell for the IRR result:", Type:=8)
    On Error GoTo 0

    ' Exit if no cell selected
    If rngDestination Is Nothing Then Exit Sub

    ' Calculate and print IRR in the destination cell
    rngDestination.Value = Application.WorksheetFunction.IRR(rngValues, dblGuess)

End Sub

Go through this Microsoft Excel tutorial to learn the steps to create a VBA macro using the above script:

📒 Read More: How To Use The VBA Code You Find Online

When you run the script, you shall see the following input boxes:

• A prompt to enter the Values argument, like the periodical negative and positive cash flows.

• An input box so you can choose the Guess value.

• A prompt to let you choose the destination for IRR value.

If you enter the values as requested, Excel shall calculate the IRR as shown above.

If you wish to calculate XIRR programmatically using a VBA script, you can use this code:

Sub CalculateXIRR()
    ' Declare variables
    Dim rngValues As Range
    Dim rngDates As Range
    Dim dblGuess As Double
    Dim rngDestination As Range
    
    ' Prompt user to select range for Values
    On Error Resume Next
    Set rngValues = Application.InputBox("Please select the range for Values:", Type:=8)
    On Error GoTo 0
    If rngValues Is Nothing Then Exit Sub
    
    ' Prompt user to select range for Dates
    On Error Resume Next
    Set rngDates = Application.InputBox("Please select the range for Dates:", Type:=8)
    On Error GoTo 0
    If rngDates Is Nothing Then Exit Sub
    
    ' Prompt user to enter Guess value
    dblGuess = Application.InputBox("Please enter the Guess value:", Type:=1)
    If dblGuess = False Then Exit Sub
    
    ' Prompt user to select destination cell
    On Error Resume Next
    Set rngDestination = Application.InputBox("Please select the destination cell for XIRR value:", Type:=8)
    On Error GoTo 0
    If rngDestination Is Nothing Then Exit Sub
    
    ' Calculate and print XIRR in the destination cell
    rngDestination.Value = Application.WorksheetFunction.Xirr(rngValues, rngDates, dblGuess)
    
    ' Format the XIRR result as a percentage with up to two decimal points
    rngDestination.NumberFormat = "0.00%"
End Sub

Use this VBA script to calculate MIRR in Excel:

Sub CalculateMIRR()
    ' Prompt to select the Values range
    Dim ValuesRange As Range
    Set ValuesRange = Application.InputBox("Select the Values range", Type:=8)

    ' Prompt to select the Finance Rate cell
    Dim FinanceRateCell As Range
    Set FinanceRateCell = Application.InputBox("Select the Finance Rate cell", Type:=8)

    ' Prompt to select the Reinvest Rate cell
    Dim ReinvestRateCell As Range
    Set ReinvestRateCell = Application.InputBox("Select the Reinvest Rate cell", Type:=8)

    ' Prompt to select the Destination cell
    Dim DestinationCell As Range
    Set DestinationCell = Application.InputBox("Select the Destination cell", Type:=8)

    ' Calculate MIRR and print in the Destination cell
    DestinationCell.Value = Application.WorksheetFunction.MIRR(ValuesRange, FinanceRateCell.Value, ReinvestRateCell.Value)
End Sub

Calculate IRR Using Office Scripts

Since you can’t use VBA on Excel for the web app, you can choose Office Scripts as an alternative.

Go to the Automate tab and click on the New Script button.

In the Code Editor panel, copy and paste this script:

function main(workbook: ExcelScript.Workbook) {
    // Get the currently active worksheet
    let activeSheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();

    // Get the currently selected range
    let selectedRange: ExcelScript.Range = workbook.getSelectedRange();

    // Get the values in the selected range
    let cashFlows: (number[][]) = selectedRange.getValues();

    // Flatten the 2D array to 1D array
    let cashFlowsFlat: number[] = [].concat(...cashFlows);

    // Calculate the IRR
    let irr: number = calculateIRR(cashFlowsFlat);

    // Convert the IRR to a percentage and round to two decimal places
    let irrPercentage: number = parseFloat((irr * 100).toFixed(2));

    // Log the IRR
    console.log(`The IRR for the selected range is ${irrPercentage}%`);
}

// Function to calculate IRR
function calculateIRR(cashFlows: number[]) {
    let guess: number = 0.1;  // Initial guess for IRR
    let maxIter: number = 100;  // Maximum number of iterations
    let tol: number = 0.00001;  // Tolerance

    for (let i = 0; i < maxIter; i++) {
        let f: number = 0;
        let df: number = 0;
        for (let j = 0; j < cashFlows.length; j++) {
            f += cashFlows[j] / Math.pow(1 + guess, j);
            df += -j * cashFlows[j] / Math.pow(1 + guess, j + 1);
        }
        let newGuess: number = guess - f / df;
        if (Math.abs(newGuess - guess) < tol) {
            return newGuess;
        }
        guess = newGuess;
    }

    throw new Error('IRR calculation did not converge');
}

Click the Save script button.

Now, select the column where input values of the IRR function are available.

Hit the Run button to calculate IRR.

You can see the calculated value in the Output interface of Code Editor.

Limitations of IRR

Here are the drawbacks of the IRR formula:

• IRR doesn’t consider the absolute value of the investment.
• It often shows a higher percentage of return for a project of low dollar value.
• Venture capitalists and individual investors typically prefer projects with a higher dollar value, even if the ROI percentage is lower than smaller projects. Hence, IRR isn’t the only metric to test investment performance.
• The IRR formula assumes reinvestment of capital or cash flows at the same rate as the calculated IRR. This isn’t always feasible due to fluctuating return rates in the capital market.
• If the investment plan or project involves alternating positive and negative cash flows, it can result in more than one IRR for the same project.

Conclusions

You can now easily find out if an investment is worth selecting or not by calculating IRR, XIRR, or MIRR, using any of the methods mentioned in this article.

Did the article help you? Do you know a better way to fetch IRR? Comment below! Also, if you’ve figured out which investment is better between the website option and the hedge fund deal, add that in your comment.