Microsoft KB Archive/106149
Microsoft Knowledge Base
Excel: Macro to Calculate IRR() with Consistent Returns
Last reviewed: June 30, 1997
Article ID: Q106149
The information in this article applies to:
- Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
- Microsoft Excel for OS/2, version 2.2, 3.0
- Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0
Often when you calculate the internal rate of return for an item, you may have an initial payment with consistent periodic payments. If you use the IRR() function to make this calculation, you must repeat each payment.
The following a custom function macro may help you simplify this process.
Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.
Syntax of Custom Function
The syntax of this function is as follows
Initial is a required argument referring to the initial payment. (Enter initial payment as a negative value.) Payment is a required argument referring to the amount of the consistent payments Periods is a required argument referring to the number of payment periods. Guess is an optional argument referring to the guess argument for the IRR() function.
To create this custom function:
Enter the following code on a new macro sheet:
A1: Consistent_IRR A2: =RESULT(1) A3: =ARGUMENT("Initial",1) A4: =ARGUMENT("Payment",1) A5: =ARGUMENT("Periods",1) A6: =ERROR(FALSE) A7: =ARGUMENT("Guess",1) A8: =IF(ISERROR(Guess),SET.NAME("Guess",0.1)) A9: =SET.VALUE(C1,Initial) A10: =FOR("x",1,Periods) A11: =SET.VALUE(OFFSET(C1,x,0,1,1),Payment) A12: =NEXT() A13: =RETURN(IRR(OFFSET(C1,0,0,Periods+1,1),Guess))
NOTE: Column c is used in the example above and any information in cells in this column may be overwritten. To keep this information from being overwritten, make sure column c is not already in use.
- Select cell A1 in the macro sheet.
- From the Formula menu, choose Define Name.
- In the name box, type the name "Consistent_IRR"(without the quotation marks) and select the Function option under Macro.
- Choose OK.
Explanation of macro
A1: The Name of the Macro. A2: Defines data type of returned value. A3-A5: Defines data types of required input values. A6: Turns off error checking. This allows for optional arguments. A7: Defines data type of optional "Guess" argument. A8: Sets a default Guess value if none specified. A9: Enter initial payment value.
A10-A12: For Next loop to enter all payment values.
A13: Returns calculated IRR() value to worksheet.
"Function Reference," version 4.0, pages 25, 247-247 "Function Reference," version 3.0, pages 15-16, 134-135 "Function and Macros," version 2.1, pages 251, 310
Additional reference words: 2.00 2.00c 2.00c 2.0 3.00 4.00 4.00a
Last reviewed: June 30, 1997