Bookshelf Home | Contents | Index | PDF

## IRR Function

This standard VB function returns the internal rate of return for a stream of periodic cash flows.

##### Syntax

IRR(valuearray( ), guess)

Argument
Description

valuearray( )

An array containing cash-flow values

guess

An estimate of the value returned by IRR

##### Returns

The internal rate of return for a stream of periodic cash flows.

##### Usage

Valuearray( ) must have at least one positive value (representing a receipt) and one negative value (representing a payment). Payments and receipts must be represented in the exact sequence. The value returned by IRR varies with the change in the sequence of cash flows.

In general, a guess value of between 0.1 (10 percent) and 0.15 (15 percent) is a reasonable estimate.

IRR is an iterative function. It improves a given guess over several iterations until the result is within 0.00001 percent. If it does not converge to a result within 20 iterations, it signals failure.

##### Example

This example calculates an internal rate of return (expressed as an interest rate percentage) for a series of business transactions (income and costs). The first value entered must be a negative amount, or IRR generates an "Illegal Function Call" error.

Sub Button_Click
Dim cashflows() as Double
Dim guess, count as Integer
Dim i as Integer
Dim intnl as Single
Dim msgtext as String
guess = .15
count = 2
ReDim cashflows(count + 1)
For i = 0 to count-1
cashflows(i) = 3000
Next i
intnl = IRR(cashflows(),guess)
msgtext = "The IRR for your cash flow amounts is: "
msgtext = msgtext & Format(intnl, "Percent")
End Sub