Microsoft KB Archive/106149

From BetaArchive Wiki

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

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)

where:

   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:

  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.

A10-A12: For Next loop to enter all payment values.

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


Additional reference words: 2.00 2.00c 2.00c 2.0 3.00 4.00 4.00a

Keywords : kbmacro kbprb kbprg
Version : 2.x 3.00 4.00 | 2.20 2.21 3.00 |
Platform : MACINTOSH OS/2 WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 30, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.