Microsoft Knowledge Base

• 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

SUMMARY

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.

MORE INFORMATION

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

    Consistent_IRR(initial,payment,periods,guess)

   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.

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

2. Select cell A1 in the macro sheet.

3. From the Formula menu, choose Define Name.

4. In the name box, type the name "Consistent_IRR"(without the quotation marks) and select the Function option under Macro.

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

A13:     Returns calculated IRR() value to worksheet.

REFERENCES

"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