Irr To Work In Excel

Posted : admin | On 17-08-2021

Microsoft Excel provides 3 functions for finding the internal rate of return: IRR - the most commonly used function to calculate the internal rate of return for a series of cash flows that occur at regular intervals. XIRR – finds IRR for a series of cash flows that occur at irregular intervals.

Description

  1. The IRR Function Calculates the internal rate of return for a series of periodic (consistent timing) cash flows. If the cash flows are not periodic, use the XIRR function instead. To use the IRR Excel Worksheet Function, select a cell and type: (Notice how the formula inputs appear).
  2. I want to use the IRR function in Excel; however, my inputs exists in a non-contiguous range. For example, I want to know the return of an asset over time (each row is a time period). Column A has the cost (cash outflow) of owning the asset/ Column B is the price of the asset (cash inflow if liquidated).
  3. IRR in Excel is one of the built-in functions available in Microsoft Excel. IRR on excel falls under the category of Financial Functions. The Internal Rate of Return is the rate of interest received for the investment done.
  4. If the second parameter is not used in the function, Excel will find an IRR of 10%. On the other hand, if the second parameter is used (i.e., = IRR ($ C $ 6: $ F $ 6, C12)), there are two IRRs.

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.
Work

How To Use Irr In Excel

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

Irr To Work In Excel

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

Calculating Irr In Excel

or
  • The function fails to converge after 20 iterations.

Irr Excel Template

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