home *** CD-ROM | disk | FTP | other *** search
/ Computerized Investing - Spreadsheet Collection / Spreadsheet Collection.iso / pc / ibm12 / rrr.doc < prev    next >
Encoding:
Text File  |  1991-07-12  |  12.9 KB  |  214 lines
  1. Realized Rates of Return Revisited
  2. By Robert Osterlund
  3. Computerized Investing, May 1987
  4.  
  5. For the May 1985 issue of Computerized Investing, I wrote a BASIC program
  6. (RRR.BAS) to compute the realized rate of return, or internal rate of return
  7. (IRR), for any investment situation involving uneven, irregularly timed cash
  8. flows.  For this issue I have rewritten the RRR program in spreadsheet form.
  9. This new version fixes the main defect of the earlier BASIC version--its lack
  10. of a data saving capability--and it improves upon the built-in spreadsheet IRR
  11. function, which only works for investments having regularly spaced cash flows.
  12.  
  13. Before proceeding, I want to thank CI reader Kirk Randall, whose ideas served
  14. as both inspiration and outline for the resultant spreadsheet model.
  15.  
  16. Also, I should define what is meant by "internal rate of return."  For
  17. prospective investments, IRR is the uniform discount rate that equates the
  18. present value of an investment's expected returns with its cost--in other
  19. words, the discount rate that reduces the net present value (NPV) of an
  20. investment to zero.  IRR may also be applied to past investments or investments
  21. in progress.  Think of an investment as a continuously compounded savings
  22. account with the same pattern of deposits (inflows) and withdrawals (outflows)
  23. as the investment.  Then its IRR is the compound effective interest rate that
  24. the account would have to earn in order to grow to its final, or current,
  25. value.
  26.  
  27. The IRR model appears below.  Formulas are provided for the two spreadsheet
  28. programs most popular with our readers:  Lotus 1-2-3 and Microsoft Multiplan.
  29. (Owners of other spreadsheet programs should have no trouble designing their
  30. own templates using the versions given here as guides.)  Copy the formulas in
  31. cells D5 (R5C4) and E5 (R5C5) down to cell ranges D6..D14 (R6:14C4) and E6..E14
  32. (R6:14C5).  (If you need space for more than ten cash flows, insert as many
  33. rows as needed before row 15, then copy the formulas in D5 and E5 down their
  34. columns to the newly created blank cells.)  Named cell ranges include three
  35. cells containing macros--A18 (R18C1), A20 (R20C1), and A22 (R22C1), invoked by
  36. ALT-R, ALT-C, and ALT-I respectively.  (If your spreadsheet doesn't support
  37. named cell ranges, simply substitute the appropriate numbered cell reference
  38. for any name appearing in a formula.)  The other cells are either left blank or
  39. filled with the text (alpha) labels shown.
  40.  
  41.  
  42.        A/1             B/2               C/3           D/4           E/5
  43.  1                           INTERNAL RATE OF RETURN
  44.  2 --------------------------------------------------------------------------
  45.  3    Date    Description                Amount          V(I)         V'(I)
  46.  4 --------------------------------------------------------------------------
  47.  5  9-Mar-82  1981 Contribution        $2,000.00     $2,000.00         $0.00
  48.  6 23-Mar-83  1982 Contribution        $2,000.00     $1,674.66    ($1,738.89)
  49.  7 25-Feb-84  1983 Contribution        $2,000.00     $1,428.76    ($2,810.55)
  50.  8 15-Apr-85  1984 Contribution        $2,000.00     $1,176.34    ($3,651.48)
  51.  9 27-Mar-87  1986 Contribution        $2,000.00       $843.11    ($4,259.46)
  52. 10 20-May-87  Current Balance        ($17,329.45)   ($7,122.87)   $37,038.93
  53. 11                                                       $0.00         $0.00
  54. 12                                                       $0.00         $0.00
  55. 13                                                       $0.00         $0.00
  56. 14                                                       $0.00         $0.00
  57. 15                                                ------------- -------------
  58. 16                                         Totals        $0.00    $24,578.55
  59. 17 --------------------------------------------------------------------------
  60. 18 'RC'IF(ISN                Iante:         0.00%        Ipost:        17.10%
  61. 19 'QU
  62. 20 'VAWhat is         IRRestimated:         0.00%    IRRactual:        18.65%
  63.  
  64.  
  65.  
  66.  
  67. ------------ LOTUS 1-2-3 ------------    ------------- MULTIPLAN -------------
  68.  
  69. Named Ranges--                           Named Ranges--
  70.  
  71. V: D16            T1: A5                 V: R16C4          t1: R5C1
  72. VPRIME: E16       IANTE: C18             Vprime: R16C5     Iante: R18C3
  73. \R: A18           IPOST: E18             RUN: R18C1        Ipost: R18C5
  74. \C: A20           IRRESTIMATED: C20      CONT: R20C1       IRRestimated: R20C3
  75. \I: A22           IRRACTUAL: E20         INIT: R22C1       IRRactual: R20C5
  76.  
  77. Formulas--                               Formulas--
  78.  
  79. D5:  +C5*@EXP($IANTE*($T1-A5)/365)       R5C4:  RC[-1]*EXP(Iante*(t1-RC[-3])
  80. E5:  +D5*($T1-A5)/365                           /365)
  81. D16: @SUM(D5..D14)                       R5C5:  RC[-1]*(t1-RC[-4])/365
  82. E16: @SUM(E5..E14)                       R16C4: SUM(R[-11]C:R[-2]C)
  83. C18: @IF(@ISNA($IRRACTUAL),$IPOST,       R16C5: SUM(R[-11]C:R[-2]C)
  84.      @LN(1+$IRRESTIMATED))               R18C3: IF(ISNA(IRRactual),Ipost,
  85. E18: +$IANTE-@IF(@ABS($VPRIME)<0.1,             LN(1+IRRestimated))
  86.      0.01*($V/(@ABS($V)+0.0001))*        R18C5: Iante-IF(ABS(Vprime)<0.1,
  87.      ($VPRIME/(@ABS($VPRIME)+0.0001)),          0.01*SIGN(V)*SIGN(Vprime),
  88.      $V/$VPRIME)                                V/Vprime)
  89. E20: @IF(@ABS($IANTE-$IPOST)<            R20C5: IF(ABS(Iante-Ipost)<1E-08,
  90.      0.00000001,@EXP($IPOST)-1,@NA)             EXP(Ipost)-1,NA())
  91. A18: '{calc}/xi@ISNA($IRRACTUAL)~        R18C1: 'RC'IF(ISNA(IRRactual))'
  92.      /xg\r~                                     'GORUN'
  93. A19: '/xq~                               R19C1: 'QU
  94. A20: '/xnWhat is the estimated rate?~    R20C1: 'VAWhat is the estimated
  95.      IRRESTIMATED~{goto}IANTE~                  rate?'IRRestimated'gnIante
  96.      @LN(1+$IRRESTIMATED)~{calc}                'RTvLN(1+IRRestimated)'RT'RC
  97.      @IF(@ISNA($IRRACTUAL),$IPOST,              vIF(ISNA(IRRactual),Ipost,
  98.      @LN(1+$IRRESTIMATED))~/xg\r~               LN(1+IRRestimated))'RT'GORUN'
  99. A22: '/wgrm/wgrc/wgri1~                  R22C1: 'CNon'TB'TBn'RT
  100.  
  101.  
  102. To ensure that you have set up the model correctly, enter the test data in the
  103. figure.  (Note that, in this example, no contribution was made to the IRA in
  104. 1985.)  Then, after solving for the rate of return, if all of your displayed
  105. values match those in the figure identically (or nearly so), you can blank all
  106. the data ranges and save the template to disk.
  107.  
  108. You use the DATE function to enter the dates in column A (1).  With Lotus 1-2-
  109. 3, for example, you would enter the formula @DATE(82,3,9) in cell A5.  To
  110. display dates in the proper format, apply 1-2-3's /Range Format Date command to
  111. this and all other cells containing dates.  (With Multiplan, the formula
  112. DATE(1982,3,9) would go in cell R5C1.  Use the Format Time-Date Cells option to
  113. format all the date cells.)  The text values in column B (2) are short
  114. descriptions of each transaction or cash flow.  Cash flow amounts are entered
  115. in column C (3).
  116.  
  117. Pay special attention to the question of sign as you enter cash flow amounts.
  118.  
  119. When evaluating the historical performance of a stock portfolio or mutual fund,
  120. it makes sense to use the savings account analogy.  Then "deposits"--the
  121. initial account balance and any other infusions of cash from the outside--
  122. should be positive in sign, while "withdrawals"--income not reinvested--should
  123. be negative in sign.  The final, or current, account value should also be
  124. negative (as if you closed out the account at the end).
  125.  
  126. When determining the yield-to-maturity for a bond, on the other hand, you might
  127. find it more sensible to think in terms of investment costs and benefits.  Then
  128. the bond's purchase price would be a negative amount, while its face value and
  129. any interest received would be positive.
  130.  
  131. The question arises:  How do you handle income reinvestment?  If interest or
  132. dividends are immediately reinvested in the same investment vehicle, just
  133. ignore them; they are accounted for implicitly in the final investment value.
  134. If you apply them to some other investment or take them in the form of a cash
  135. benefit (i.e., you "consume" them), you should record that as an external cash
  136. flow.  Income reinvested after some delay should be counted as two separate
  137. cash flows--the first occurring when the income is earned and the second (which
  138. should be opposite in sign from the first) when it is reinvested.
  139.  
  140. What about taxes and incidental costs, such as commissions and other service
  141. fees?  If you pay for these out-of-pocket, then enter each instance of them as
  142. a separate cash flow.  If, however, they take the form of deductions from the
  143. investment's value, then there is no need to account for them explicitly.
  144.  
  145. Before solving for the IRR, make sure you set the recalculation parameters
  146. correctly!  For Lotus 1-2-3, set the recalculation method to Manual, the order
  147. of recalculation to Columnwise, and the iteration count to 1.  For Multiplan,
  148. automatic recalculation and iteration should both be turned off.  If you press
  149. the ALT-I key combination, this invokes the initialization macro in cell A22
  150. (R22C1), which adjusts the recalculation parameters to their proper settings
  151. automatically.
  152.  
  153. With all the cash flow data in place and the recalculation parameters properly
  154. set, the next thing to do is enter 0 (zero)--the best initial estimate for the
  155. IRR--in cell C20 (R20C3).  Then, press the ALT-R key chord to start the program
  156. running.  At first, the value NA (#N/A)--meaning "the answer is not yet
  157. available"--shows up in cell E20 (R20C5).  Before too long, a percentage figure
  158. should appear in that cell.  This figure is the calculated and actual (correct
  159. to the nearest basis point) internal rate of return.
  160.  
  161. Most investments are "well-behaved" in the sense that given an initial IRR
  162. estimate of 0%, the model will produce the actual IRR within five to ten
  163. program iterations.  For certain "ill-behaved" investments, however, the model
  164. will just keep looping and looping and never reach an answer.  To break out of
  165. the endless loop, press CTRL-BRK (for Lotus 1-2-3; for Multiplan, just press
  166. the ESC key).  To continue, press ALT-C.  You will be asked to supply another
  167. estimated rate.  This time, choose one different from 0%, say 50%.  The program
  168. then resumes operation.  If the second estimate fails also, keep interrupting
  169. the program and trying different estimated rates (possibly even negative ones,
  170. for unprofitable investments) until you find an estimate that works.
  171.  
  172. If the program fails to converge on an answer, or if it halts due to an an
  173. error condition (overflow, for instance), bear in mind:  You may have entered
  174. an amount (or amounts) as positive (negative) when it should have been negative
  175. (positive).  It is a common mistake to get the sign of the final investment
  176. value wrong.  Or it could be that you entered one or more formulas incorrectly.
  177. Double-check, also, to see that the recalculation parameters were set properly.
  178.  
  179. In some rare investment situations, more than one IRR may be "correct," in the
  180. sense that it results in a zero NPV.  Consider, for example, the following
  181. sequence of cash flows (with two changes of sign):  100, -300, 200, each one
  182. year apart.  There are two different discount rates that reduce this sequence
  183. of cash flows to zero NPV:
  184.  
  185.                                 2            1            0
  186.                   0%:  100*(1+0)  - 300*(1+0)  + 200*(1+0)  = 0
  187.  
  188.                                 2            1            0
  189.                 100%:  100*(1+1)  - 300*(1+1)  + 200*(1+1)  = 0
  190.  
  191. The greater the number of sign changes, the higher the likelihood of there
  192. being multiple "correct" rates of return.  In unusual cases like these, if you
  193. get one answer but suspect there might be another, you can rerun the program
  194. with different estimated rates (100% or -50%, for example).  Usually, though, a
  195. 0% estimated rate will result in a single, unique, and best answer.
  196.  
  197. What if your spreadsheet doesn't support macros?  First, enter the model minus
  198. the macros, then save it to disk.  Next enter 0% as the estimated IRR.  Then,
  199. press the "calc" key (F9 for 1-2-3; F4 or ! for Multiplan) as many times as
  200. necessary until an answer appears in cell E20 (R20C5).  With luck, you will
  201. never encounter an "ill-behaved" investment.  But if you do, and the value in
  202. D16 (R16C4) trends stubbornly away from zero or flip-flops between the same two
  203. nonzero values, then you will have to try the following:  (1) enter a different
  204. estimated IRR in C20 (R20C3); (2) enter the formula @LN(1+IRRestimated) in C18
  205. (R18C3; for Multiplan, omit the "@"); (3) initiate one, and only one, manual
  206. recalculation; (4) now reenter the formula shown in the figure for cell C18
  207. (R18C3); and finally, (5) press the "calc" key as many times as necessary until
  208. the IRRactual figure appears in E20.  If none appears after ten or so
  209. iterations, then you will have to go back to step (1) and start the process all
  210. over again with some other estimated rate.  Do this enough times, and you'll
  211. soon be longing for a spreadsheet program with macros.
  212.  
  213. (c) Copyright 1987 by the
  214. American Association of Individual Investors