# 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 DoubleDim irrVal As DoublecashFlows(0) = -100   ' Initial investment of \$100cashFlows(1) = 20   ' Return from year 1cashFlows(2) = 24   ' Return from year 2cashFlows(3) = 28.8   ' Return from year 3cashFlows(4) = 34.56   ' Return from year 4cashFlows(5) = 41.47   ' Return from year 5irrVal = 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.