Microsoft KB Archive/106149: Difference between revisions
(importing KB archive) |
m (Text replacement - """ to """) |
||
Line 48: | Line 48: | ||
<pre> A1: Consistent_IRR | <pre> A1: Consistent_IRR | ||
A2: =RESULT(1) | A2: =RESULT(1) | ||
A3: =ARGUMENT( | A3: =ARGUMENT("Initial",1) | ||
A4: =ARGUMENT( | A4: =ARGUMENT("Payment",1) | ||
A5: =ARGUMENT( | A5: =ARGUMENT("Periods",1) | ||
A6: =ERROR(FALSE) | A6: =ERROR(FALSE) | ||
A7: =ARGUMENT( | A7: =ARGUMENT("Guess",1) | ||
A8: =IF(ISERROR(Guess),SET.NAME( | A8: =IF(ISERROR(Guess),SET.NAME("Guess",0.1)) | ||
A9: =SET.VALUE(C1,Initial) | A9: =SET.VALUE(C1,Initial) | ||
A10: =FOR( | A10: =FOR("x",1,Periods) | ||
A11: =SET.VALUE(OFFSET(C1,x,0,1,1),Payment) | A11: =SET.VALUE(OFFSET(C1,x,0,1,1),Payment) | ||
A12: =NEXT() | A12: =NEXT() | ||
Line 62: | Line 62: | ||
<li>Select cell A1 in the macro sheet.</li> | <li>Select cell A1 in the macro sheet.</li> | ||
<li>From the Formula menu, choose Define Name.</li> | <li>From the Formula menu, choose Define Name.</li> | ||
<li>In the name box, type the name | <li>In the name box, type the name "Consistent_IRR"(without the quotation marks) and select the Function option under Macro.</li> | ||
<li>Choose OK.</li></ol> | <li>Choose OK.</li></ol> | ||
Line 71: | Line 71: | ||
A3-A5: Defines data types of required input values. | A3-A5: Defines data types of required input values. | ||
A6: Turns off error checking. This allows for optional arguments. | A6: Turns off error checking. This allows for optional arguments. | ||
A7: Defines data type of optional | A7: Defines data type of optional "Guess" argument. | ||
A8: Sets a default Guess value if none specified. | A8: Sets a default Guess value if none specified. | ||
A9: Enter initial payment value.</pre> | A9: Enter initial payment value.</pre> | ||
Line 80: | Line 80: | ||
== REFERENCES == | == 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 | |||
{| | {| | ||
Line 93: | Line 93: | ||
</span><br /> | </span><br /> | ||
<br /> | <br /> | ||
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED | 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.</blockquote> | ||
<span>Last reviewed: June 30, 1997</span><br /> | <span>Last reviewed: June 30, 1997</span><br /> | ||
[[../cpyright|©1997 Microsoft Corporation. All rights reserved. Legal Notices]].<br /> | [[../cpyright|©1997 Microsoft Corporation. All rights reserved. Legal Notices]].<br /> | ||
|} | |} |
Latest revision as of 09:59, 20 July 2020
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:
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.
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 Last reviewed: June 30, 1997 |