VBA MIRR Function

Visual Basic for Applications (VBA) is a programming language that is commonly used in Microsoft Office applications such as Excel, Word, and PowerPoint. It allows users to automate tasks, create custom functions, and perform complex calculations. One of the functions available in VBA is the MIRR function, which stands for Modified Internal Rate of Return.

VBA MIRR Function – Purpose, Syntax and Arguments

Purpose

The purpose of the MIRR function is to calculate the rate of return for an investment based on both the initial cost and the final value, taking into account both the inflows and outflows of cash during the investment period. This makes it a more accurate and comprehensive measure of the investment’s profitability compared to simply calculating the internal rate of return (IRR), which only considers the initial investment amount.

Syntax

The syntax for the MIRR function is as follows:

MIRR (values, finance_rate, reinvest_rate)

Arguments

The MIRR function takes in three arguments:

• values: This is a required argument and refers to the range of values that represent the cash flow for the investment. It can be entered manually or selected using the range selector.
• finance_rate: This is also a required argument and represents the interest rate or cost of borrowing funds used in the investment. It is expressed as a percentage.
• reinvest_rate: This is the optional argument that specifies the interest rate earned on the cash flows that are reinvested during the investment period. If this argument is omitted, the default value of 10% is used.

Example

Suppose we have an investment with an initial cost of $10,000 and the following cash flows over the next five years:
Year 1: $4,000
Year 2: $2,000
Year 3: $3,000
Year 4: $6,000
Year 5: $8,000
To calculate the MIRR for this investment, we can use the following formula:

=MIRR(A1:A6, 5%, 8%)

Where A1:A6 is the range of values containing the cash flows, 5% is the finance rate, and 8% is the reinvestment rate.
This would give us a MIRR of 10.09%, indicating that the investment is earning a return of 10.09% per year on the amount initially invested.

Remarks and Important Notes

• The MIRR function assumes that cash flows occur at regular intervals (usually annual). If the cash flows occur at different intervals, the function will not be accurate.
• If both the finance rate and reinvestment rate are positive, the MIRR will be greater than the IRR. If both rates are negative, the MIRR will be less than the IRR.
• If the investment has only a single positive cash flow, the MIRR will return a #DIV/0! error as there is no reinvestment involved.
• The MIRR function can also be used to compare the profitability of different investment options by entering their respective cash flows in the values argument and using the same finance and reinvestment rates.

The VBA MIRR function is a useful tool for calculating the modified internal rate of return for investments, taking into account the cash flows and rates of return. It provides a more accurate measure of profitability compared to the traditional IRR function and can be easily integrated into VBA code for automation and customization purposes.

Understanding VBA MIRR Function with Examples

Example 1: Calculating MIRR for a Single Range of Cash Flows

The MIRR (Modified Internal Rate of Return) function in VBA is used to calculate the internal rate of return for a series of cash flows, taking into account both the initial investment and the reinvestment rate. This function is useful when comparing investment alternatives with different cash flow patterns, as it takes into consideration the opportunity cost of reinvesting the cash flows.
Let’s take a look at an example of how to use the MIRR function in VBA to calculate the internal rate of return for a single range of cash flows.

Option Explicit
Sub Calculate_MIRR()
    
    Dim rng As Range
    Dim Initial_Investment As Double
    Dim Reinvestment_Rate As Double
    Dim MIRR As Double
    
    'Set the range of cash flows
    Set rng = Range("A1:A10")
    
    'Set the initial investment
    Initial_Investment = Range("B1").Value
    
    'Set the reinvestment rate
    Reinvestment_Rate = Range("B2").Value
    
    'Calculate MIRR
    MIRR = Application.WorksheetFunction.MIRR(rng, Reinvestment_Rate, Initial_Investment)
    
    'Display the result
    MsgBox "The MIRR for the given cash flows is: " & MIRR
    
End Sub

1. Firstly, we declare the variables to be used in the code. This includes the range of cash flows (rng), the initial investment amount (Initial_Investment), the reinvestment rate (Reinvestment_Rate), and the MIRR (MIRR).
2. We then set the range of cash flows, in this case, it is from cell A1 to A10.
3. Next, we set the initial investment amount, which is the negative cash flow at time 0.
4. Similarly, we set the reinvestment rate, which is the rate at which the cash flows will be reinvested.
5. Finally, we use the MIRR function to calculate the internal rate of return for the given cash flows, taking into consideration the initial investment and the reinvestment rate.
6. The result of the calculation is then displayed using a message box.

It is important to note that the MIRR function in VBA uses the NPV (Net Present Value) function to calculate the present value of the cash flows, and then uses the IRR (Internal Rate of Return) function to calculate the internal rate of return. This is done by assuming that the negative cash flow occurs at the beginning of the period.

Example 2: Calculating MIRR for Multiple Ranges of Cash Flows

In some cases, we may need to calculate the MIRR for multiple ranges of cash flows, where each range represents a different project or investment alternative. In such cases, we can use the MIRR function in a loop to calculate the MIRR for each range of cash flows.

Option Explicit
Sub Calculate_MIRR()
    Dim rng As Range
    Dim i As Integer
    Dim Initial_Investment As Double
    Dim Reinvestment_Rate As Double
    Dim MIRR As Double
    
    'Loop through the different cash flow ranges
    For i = 1 To 3
        'Set the range of cash flows
        Set rng = Range("A" & i & ":A" & i + 9)
    
        'Set the initial investment
        Initial_Investment = Range("B" & i).Value
    
        'Set the reinvestment rate
        Reinvestment_Rate = Range("C" & i).Value
    
        'Calculate MIRR
        MIRR = Application.WorksheetFunction.MIRR(rng, Reinvestment_Rate, Initial_Investment)
    
        'Display the result
        MsgBox "The MIRR for cash flow range " & i & " is: " & MIRR
    Next i
    
End Sub

1. Similar to the previous example, we declare the variables to be used in the code.
2. In this case, we then use a loop to loop through the different ranges of cash flows. This is done by incrementing the value of the variable “i” by 1 in each iteration.
3. Within the loop, we set the range of cash flows, the initial investment amount, and the reinvestment rate for each range of cash flows.
4. The MIRR function is then used to calculate the internal rate of return for each range of cash flows.
5. The results are then displayed using a message box for each range of cash flows.

It is important to note that the reinvestment rate used in the MIRR calculation should be the same for all the ranges of cash flows. This ensures that we are comparing the returns of the investment alternatives taking into account the same opportunity cost of reinvestment.

Example 3: Handling Errors in the MIRR Calculation

As with any function, the MIRR function in VBA may also encounter errors. Some common errors that can occur while using the MIRR function include #VALUE (when the given range of cash flows contains non-numeric values), #NUM (when the MIRR cannot be calculated), and #DIV/0 (when the reinvestment rate or initial investment amount is 0).
To handle these errors, we can use the VBA code to check if an error has occurred and handle it accordingly. Let’s take a look at an example of how this can be done.

Option Explicit
Sub Calculate_MIRR()
    
    Dim rng As Range
    Dim Initial_Investment As Double
    Dim Reinvestment_Rate As Double
    Dim MIRR As Double
    
    'Set the range of cash flows
    Set rng = Range("A1:A10")
    
    'Set the initial investment
    Initial_Investment = Range("B1").Value
    
    'Set the reinvestment rate
    Reinvestment_Rate = Range("B2").Value
    
    'Calculate MIRR
    On Error Resume Next
    MIRR = Application.WorksheetFunction.MIRR(rng, Reinvestment_Rate, Initial_Investment)
    On Error GoTo 0
    
    'Check for errors
    If Err.Number > 0 Then
        'Display the error message
        MsgBox "An error has occurred: " & Err.Description
    Else
        'Display the result
        MsgBox "The MIRR for the given cash flows is: " & MIRR
    End If
    
End Sub

1. In this example, we use the On Error Resume Next statement to resume the code execution in case an error occurs while calculating the MIRR. This will ensure that the code doesn’t stop running and allows us to handle the error.
2. We then check for any errors using the Err.Number property. If an error has occurred, we display the error message using the Err.Description property.
3. If no error has occurred, the MIRR is calculated and the result is displayed using a message box.

In case of an error, we can also use the Err.Clear method to clear the error and continue with the code execution. This can be useful in situations where we want to handle the error and then continue with the next iteration of the loop.

Conclusion

The MIRR function in VBA is a powerful tool in calculating the internal rate of return, taking into account both the initial investment and reinvestment rate. It is particularly useful in comparing investment alternatives with different cash flow patterns. By understanding how to use this function with different examples and handling errors, we can effectively use it in our financial analysis and make informed investment decisions.