VBA IRR Function

The IRR (Internal Rate of Return) function in VBA is used to calculate the internal rate of return for a series of cash flows. It measures the estimated return rate of an investment by taking into account the initial investment, subsequent cash flows, and the time value of money.

VBA IRR Function – Purpose, Syntax and Arguments

Purpose:

The purpose of the IRR function is to help make financial decisions by determining the potential profitability of a project or investment. It helps in comparing different investment options and choosing the one with the highest return. It is commonly used in financial modeling, budgeting, and investment analysis.

Syntax:

  IRR (values, [guess])

The IRR function takes two arguments, ‘values’ and ‘guess’. The ‘values’ argument is a required input that includes a range of cash flows in a worksheet or an array. The ‘guess’ argument is an optional input that provides an initial guess for the internal rate of return calculation. The default value for the ‘guess’ argument is 0.1 (10%).

Arguments:

• values: This is a mandatory argument that specifies the range of cash flows for which the internal rate of return needs to be calculated. It can be a range in a worksheet or an array.
• guess: This is an optional argument that provides an initial guess for the internal rate of return calculation. The default value for this argument is 0.1 (10%).

Example:

Assume we want to calculate the internal rate of return for an investment that requires an initial investment of $10,000 and expected cash flows of $2,000, $3,000, $4,000, and $5,000 at the end of each year for the next four years. The IRR function can be used as follows:

  =IRR(-10000, 2000, 3000, 4000, 5000)

The function will return the internal rate of return as 12.2%.

Remarks and Important Notes:

• The IRR function returns the annualized internal rate of return. Therefore, the time period for cash flows should be consistent, such as all monthly, quarterly, or annual cash flows.
• The function assumes that the first cash flow occurs at the beginning of the first period and the last cash flow occurs at the end of the last period.
• If the IRR function is not able to calculate the internal rate of return, it will return the #NUM! error. This can happen if there are no positive cash flows or the initial guess provided is not close enough to the actual internal rate of return.

The IRR function in VBA is a powerful tool for analyzing and comparing different investment options. It helps in making informed financial decisions by considering the time value of money and providing an estimated return rate for a series of cash flows. With the proper understanding of the function and its inputs, it can be a valuable addition to any financial analysis or modeling project.

Understanding VBA IRR Function with Examples

Example 1: Calculating IRR for an Investment

In this example, we will use the IRR function to calculate the internal rate of return for a hypothetical investment. As a reminder, internal rate of return is the discount rate that makes the net present value of all cash flows from the investment equal to zero. This essentially tells us the return on our investment.

Dim cashFlows(5) As Double 'array to store cash flows
cashFlows(0) = -1000 'initial investment
cashFlows(1) = 200 'year 1 cash flow
cashFlows(2) = 300 'year 2 cash flow
cashFlows(3) = 400 'year 3 cash flow
cashFlows(4) = 500 'year 4 cash flow
cashFlows(5) = 600 'year 5 cash flow
Dim investmentIRR As Double 'variable to store IRR
investmentIRR = IRR(cashFlows) 'using IRR function to calculate IRR

1. The first step is to declare an array to hold the cash flows. In this example, we have an initial investment of -1000, followed by cash flows of 200, 300, 400, and 500 for the next 4 years. The last element of the array, cashFlows(5), represents the cash flow in the 5th year.
2. Next, we declare a variable to store the IRR. The IRR function in VBA takes in an array of cash flows as its argument.
3. We use the IRR function and pass in the cashFlows array to calculate the IRR.

The result will be stored in the investmentIRR variable, which we can then use for further calculations or display to the user.

Example 2: Calculating IRR with Variable Cash Flows

In the previous example, we had a fixed set of cash flows to calculate the IRR. However, in real-world scenarios, cash flows may vary depending on different scenarios or assumptions. Let’s look at an example where we have variable cash flows for different years.

Dim year As Integer 'variable to store year for loop
Dim cashFlows(10) As Double 'array to store cash flows
For year = 1 To 10 'looping through 10 years
  cashFlows(year) = InputBox("Enter cash flow for year " & year) 'using input box to get user input for cash flow
Next year
Dim investmentIRR As Double
investmentIRR = IRR(cashFlows) 'using IRR function to calculate IRR

1. We start by declaring a variable, “year”, to be used in a loop.
2. Next, we declare an array, cashFlows, to store the 10 cash flows for the next 10 years.
3. In the loop, we use an InputBox to get user input for the cash flow in each year. The loop will run for 10 years, and the value entered by the user will be stored in the corresponding element of the cashFlows array.
4. After the loop, we use the IRR function and pass in the cashFlows array to calculate the IRR.

Depending on the user’s input, the result will be stored in the investmentIRR variable. The variable can then be used for further calculations or display.

Example 3: Using IRR for Multiple Investments

The IRR function can also be used to calculate the internal rate of return for multiple investments. Let’s look at an example where we have 2 investments and want to compare their returns.

Dim investment1CashFlows(5) As Double 'array for first investment cash flows
investment1CashFlows(0) = -1000 'initial investment
investment1CashFlows(1) = 200 'year 1 cash flow
investment1CashFlows(2) = 300 'year 2 cash flow
investment1CashFlows(3) = 400 'year 3 cash flow
investment1CashFlows(4) = 500 'year 4 cash flow
investment1CashFlows(5) = 600 'year 5 cash flow
Dim investment2CashFlows(5) As Double 'array for second investment cash flows
investment2CashFlows(0) = -2000 'initial investment
investment2CashFlows(1) = 500 'year 1 cash flow
investment2CashFlows(2) = 600 'year 2 cash flow
investment2CashFlows(3) = 700 'year 3 cash flow
investment2CashFlows(4) = 800 'year 4 cash flow
investment2CashFlows(5) = 900 'year 5 cash flow
Dim investment1IRR As Double 'variable to store IRR for first investment
investment1IRR = IRR(investment1CashFlows) 'calculating IRR for first investment
Dim investment2IRR As Double 'variable to store IRR for second investment
investment2IRR = IRR(investment2CashFlows) 'calculating IRR for second investment

1. First, we declare arrays for each investment- investment1CashFlows for the first investment, and investment2CashFlows for the second.
2. We assign the values for the cash flows for each investment in the respective arrays.
3. Next, we declare variables to store the IRR for each investment, investment1IRR and investment2IRR.
4. We use the IRR function to calculate the IRR for each investment, by passing in the respective cash flow arrays as arguments.

The IRR values for each investment will be stored in the respective variables, which we can use for further calculations or comparison.

Example 4: Handling Error Values

It is common to have certain error values or missing data in a set of cash flows when using the IRR function. In such cases, the function may return an error message. Let’s look at an example of how to handle this situation.

Dim cashFlows(5) As Double 'array to store cash flows
cashFlows(0) = -1000 'initial investment
cashFlows(1) = 200 'year 1 cash flow
cashFlows(2) = 300 'year 2 cash flow
cashFlows(3) = 400 'year 3 cash flow
cashFlows(4) = Error 'represents missing data for year 4
cashFlows(5) = 600 'year 5 cash flow
Dim investmentIRR As Double 'variable to store IRR
investmentIRR = IRR(cashFlows) 'calculating IRR
If IsError(investmentIRR) Then 'checking for error value
  MsgBox "Error: Missing or invalid data." 'displaying error message
Else
  MsgBox "The internal rate of return is " & investmentIRR 'displaying IRR
End If

1. We declare an array, cashFlows, to store the cash flows for the investment. In this example, we have an error value (Error) in the 4th element, representing missing data.
2. We use the IRR function to calculate the IRR, and store it in the investmentIRR variable.
3. Using the IsError function, we check if the investmentIRR variable contains an error value.
4. If it does, we display an error message to the user. Otherwise, we display the calculated IRR.

This is a simple way to handle error values that may occur while using the IRR function.

Conclusion

The IRR function is a useful tool in VBA for calculating the internal rate of return for investments. It takes in an array of cash flows as input and returns the IRR as its output. With different examples, we have seen how this function can be used to calculate the IRR for different scenarios. It is essential to understand the function and its arguments to use it effectively in financial analysis and decision making.