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.

## 0 Comments