VBA IRR Function

HomeVBA Functions LIST (Category Wise)How to use the VBA IRR Function (Syntax + Example)

What is VBA IRR Function

The VBA IRR function is listed under the financial category of VBA functions. When you use it in a VBA code, it calculates the internal rate of return (i.e. a series of payments and returns) for an investment. The calculation done by the IRR is a measure of an investment’s rate of return.

how to use it

To use VBA's IRR function you need to understand its syntax and arguments:


IRR(ValueArray, [Guess])


  • ValueArray: An array of cash flow which represents the payments and income. Payments would be in negative values and incomes would be in positive values [It must contain at least one negative and at least one positive value].
  • [Guess]: An initial estimate (guess) what will be the IRR [This is an optional argument and if omitted VBA takes 10% (=0.1) by default].

Example to use IRR Function in VBA

To practically understand how to use VBA IRR function, you need to go through the below example where we have written a vba code by using it:

Sub example_IRR()
Dim cF(0 To 9) As Double
cF(0) = -1000
cF(1) = 213.6
cF(2) = 259.22
cF(3) = 314.6
cF(4) = 381.79
cF(5) = 463.34
cF(6) = 562.31
cF(7) = 682.42
cF(8) = 828.19
cF(9) = 1005.09
Range("A1").Value = IRR(cF)
End Sub

In the above code, we have used IRR to get the interest rate of return from an array (cF) in which we have an initial investment of -1000 and then all the cash returns in the next 9 years. So when we ran this code, it has returned 34% in the result.


Below are some important points which you need to take care while using IRR function in VBA.

  • The first value in the arrays needs to a negative value as it’s the initial amount paid for the investment.
  • If the array we supplied doesn’t have the one value in negative and one value in positive or not able to find results after 20 iterations, VBA will return a run-time 5 error.

Related Functions

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.