Home » VBA-Functions » VBA-IRR-Function

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:


VBA IRR Function Error

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

or
Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page