# The VBA IRR Function

Related Function:
VBA MIRR

## Description

The VBA IRR function calculates the Internal Rate of Return for a supplied series of periodic cash flows (i.e. a series of payments and returns).

The syntax of the function is:

IRR( ValueArray, [Guess] )

Where the function arguments are:

 ValueArray - An array of cash flows, representing a series of payments and income, where: Negative values are treated as payments; Positive values are treated as income. This array must contain at least one negative and at least one positive value. [Guess] - An initial estimate at what the IRR will be. If this argument is omitted, it will take on the default value of 10% (=0.1). (Note this is only a value for the function to start off working with - the IRR function then uses an iterative procedure to converge to the correct rate).

## VBA IRR Function Example

In the following example, the VBA IRR function is used to calculate the internal rate of return for an initial investment of \$100, that generates a series of cash returns over 5 years.

 ' Calculate the internal rate of return of an initial investment of \$100, ' that generates a series of cash returns over 5 years. Dim cashFlows(0 to 5) As Double Dim irrVal As Double cashFlows(0) = -100   ' Initial investment of \$100 cashFlows(1) = 20   ' Return from year 1 cashFlows(2) = 24   ' Return from year 2 cashFlows(3) = 28.8   ' Return from year 3 cashFlows(4) = 34.56   ' Return from year 4 cashFlows(5) = 41.47   ' Return from year 5 irrVal = IRR( cashFlows ) ' irrVal is calculated to be 0.130575756375562.

The above VBA code calculates the internal rate of return for the investment to be 0.130575756375562 (13.1%).

Note that:

• As the initial investment of \$100 is an outgoing payment, this is supplied to the function as a negative value;
• As the returns during years 1-5 are incoming payments, these are supplied to the function as positive values.

## VBA IRR Function Error

The VBA IRR function produces the Run-time error '5': Invalid procedure call or argument if either:

• The supplied ValueArray does not contain at least one negative value and at least one positive value
or
• The function fails to converge after 20 iterations.