No. CPAs with an elementary knowledge of Lotus 123 or any comparable spreadsheet can, in five minutes, create a spreadsheet that will calculate the annualized internal rate of return (IRR) on any investment, even on those where payments vary in amount or come at irregular intervals. Knowledge of the spreadsheet's financial functions is not required. Results of the analysis can be printed in a table that can easily be understood by clients and quickly updated as new information becomes available. Potential uses of the spreadsheet are--

* mutual fund investment evaluation;

* evaluation of a proposed investment project;

* evaluation of a pass-through security;

* evaluation of a bond sold prior to maturity; and

* financial planning for college or retirement.

Setting up the Spreadsheet

The spreadsheet should first be set to the proper recalculation mode (manual and iterative). This is done by pressing the following keys: /W G R M/ W G R I 1 5 ENTER. Recalculation will occur only when function key 9 F9 is pressed. The spreadsheet is set up by entering the following expressions into the cells indicated below:

1. D9

2. E1:+E1+F4

3. E3:

4. E4:1

5. E9:+F9+B9+C9

6. F3:(1+E3/100)(1/365)-1

7. F4:(E4-G$30)/E4*10

8. F9:+E8*(1+$F$3)(D9-D8)

9. G9:

10. Copy cells D9, E9, F9, and G9 to rows 10 through 30.

11. (OPTIONAL) Change the global column width to 14 by pressing the following keys: /W G C 14 ENTER.

12. (OPTIONAL) Change the global format to "commas" with two decimal points by pressing: /W G F, 2 ENTER.

13. (OPTIONAL) Change the decimal setting for cell E3 to 4 by pressing: FUNCTION KEY 5 E 3 ENTER/ R F , 4 ENTER ENTER.

Once the above steps have been completed, any number of separate investments can be evaluated by saving each investment in a separate file or by using the spreadsheet's copy function to duplicate these cells in another range of the spreadsheet.

TABLE1

EVALUATIONOFINVESTMENTSINAMUTUALFUND

AnnualizedRateofReturn3.81%

CurrentMarketValue$47,298.00

InitialReturnEstimate10.00%

DateContributionsWithdrawalsDateCodeProjectedValue

1/15/88$20,000.0032,15720,000.00

2/16/881,500.0032,18921,565.74

4/12/881,000.0032,24522,689.94

9/6/884,000.0032,39227,034.56

10/8/881,000.0032,42428,123.42

11/7/882,000.0032,45430,210.07

12/1/88$1,500.0032,47828,784.52

12/9/881,000.0032,48629,808.14

3/4/892,000.0032,57128,069.09

2/17/905,000.0032,92134,094.82

2/25/913,000.0033,29438,424.20

1/18/924,500.0033,62144,234.51

11/1/9334,27447,298.00

43,000.003,500.00

TABLE2

EVALUATIONOFAPROPOSEDINVESTMENTPROJECT

AnnualizedRateofReturn3.21%

CurrentMarketValue$40,000.00

InitialReturnEstimate10.00

DateContributionsWithdrawalsDateCodeProjected

Value

1/18/94$200,000.0034,352$200,000.00

6/21/9430,000.0034,506232,683.53

1/4/95$10,000.0034,762227,896.48

6/7/9530,000.0034,857199,777.98

9/15/9540,000.0034,957161,514.53

9/15/9635,000.0035,323131,712.66

8/12/9730,000.0035,654105,540.45

9/24/9850,000.0036,06259,333.84

1/31/9920,000.0036,19140,000.00

Evaluating an Investment

Once the spreadsheet has been set up, an investment may be evaluated by performing the following steps:

1. Enter the first transaction on line 9, putting the date in column A preceded by a single quotation mark ('), the amount of contributions in column B, and the amount of withdrawals in column C. After all transactions have been entered, enter the most recent date at which the investment's market value was known.

2. Enter in cell E4 the investment's market value as of this most recent date.

3. Enter in cell E5 a guess as to the annualized rate of return earned on the investment.

4. Recalculate by pressing FUNCTION KEY 9 until the projected value at the most recent date in column E equals the current market value in cell E4. When these values are equal, the internal rate of return on the investment has been found and is shown in cell E3. (One or two recalculations is usually sufficient.)

5. To evaluate a second investment press: FUNCTION KEY 5 E 1 ENTER FUNCTION KEY 2 ENTER. This resets cell El's value to zero. The second investment can now be evaluated by repeating steps 1 through 4.

TABLE3

EVALUATIONOFAPASS-THROUGHSECURITY

AnnualizedRateofReturn6.20%

CurrentMarketValue$0.00

InitialReturnEstimate8.00%

DateContributionsWithdrawalsDateCodeProjectedValue

1/15/90$70,000.0032,888$70,000.00

4/15/90$850.0032,97870,196.92

7/15/90872.0033,06970,386.54

10/15/90900.0033,16170,562.82

1/15/911,500.0033,25370,141.79

4/15/912,028.0033,34369,162.83

7/15/913,200.0033,43467,008.81

10/15/914,100.0033,52663,933.44

1/15/926,432.0033,61858,479.04

4/15/928,184.0033,70951,179.44

7/15/929,342.0033,80042,611.45

10/15/929,972.0033,89233,291.02

1/15/9310,524.0033,98423,276.07

4/15/9311,129.0034,07412,495.19

7/15/9312,433.0034,165251.16

TABLE4

EVALUATIONOFABONDSOLDPRIORTOMATURITY

AnnualizedRateofReturn9.96%

CurrentMarketValue$75,000.00

InitialReturnEstimate6.00%

DateContributionWithdrawalsDateCodeProjectedValue

1/15/90$70,000.0032,888$70,000.00

4/15/90$1,400.0032,97870,257.46

7/15/901,400.0033,06970,539.73

10/15/901,400.0033,16170,847.53

1/15/911,400.0033,25371,162.79

4/15/911,400.0033,34371,447.79

7/15/911,400.0033,43471,758.56

10/15/911,400.0033,52672,095.87

1/15/921,400.0033,61872,441.35

4/15/921,400.0033,70972,775.91

7/15/921,400.0033,80073,118.47

10/15/921,400.0033,89273,488.71

1/15/931,400.0033,98473,867.92

4/15/931,400.0034,07474,216.97

7/15/931,400.0034,16574,594.04

10/15/931,400.0034,25775,000.00

TABLE5

FINANCIALPLANNINGFORCOLLEGEORRETIREMENT

AnnualizedRateofReturn9.719%

CurrentMarketValue$60,000.00

InitialReturnEstimate3.00%

DateContributionsWithdrawalsDateCodeProjectedValue

12/30/943,000.0034,689$3,000.00

12/30/953,000.0035,0636,291.16

12/30/963,500.0035,42910,403.48

12/30/973,500.0035,79414,913.16

12/30/984,000.0036,15920,360.52

12/30/994,000.0036,52426,336.56

12/30/004,500.0036,89033,399.92

12/30/014,500.0037,25541,141.46

12/30/025,000.0037,62050,134.34

12/30/035,000.0037,98560,000.00

TABLE6

FINANCIALPLANNINGFORCOLLEGEORRETIREMENT

AnnualizedRateofReturn5.00%

CurtainMarketValue$0.00

InitialReturnEstimate0.00%

DateContributionsWithdrawalsDateCodeProjectedValue

12/30/94$3,000.0034,689$3,000.00

12/30/953,000.0035,0636,150.00

12/30/963,500.0035,4299,985.30

12/30/973,500.0035,79413,956.28

12/30/984,000.0036,15918,654.10

12/30/994,000.0036,52423,586.80

12/30/004,500.0036,89029,269.45

12/30/014,500.0037,25535,232.92

12/30/025,000.0037,62041,994.57

12/30/035,000.0037,98549,094.30

Return on Investments in Mutual Funds or Individual Stocks

The spreadsheet can be used to evaluate the return on a stream of past investments in a mutual fund or individual stock whose current market value is known. In this context the IRR can be thought of as the discount rate at which an investor would have been indifferent between investing in that fund and in the best alternative investment option. If the investor's discount rate (cost of capital) for the period was less than the investment's IRR, he or she benefitted from making the investment. If the discount rate was greater than the investment's IRR, the investor would have fared better by investing in the best alternative investment option.

Table 1 shows a series of investments in a mutual fund. Although $39,500 in net contributions grew to $47,298, a total return of 19.7%, the spreadsheet shows that this represented an annual rate of return of only 3.81%. If cost of capital exceeded 3.81% or if a better return could have been earned, the investor would have fared better elsewhere.

Return on a Proposed Investment

A similar use for the spreadsheet is in calculating the IRR for a proposed investment project. Here the IRR represents the discount rate that produces a net present value (NPV) of zero, i.e., the present value of cash outlays equals the present value of cash inflows. If the IRR of a proposed project exceeds the investor's cost of capital and the project has a positive and acceptable NPV it should be undertaken. If the IRR falls shorts, the project should be rejected.

To perform this analysis the expression in cell F4 of the spreadsheet should be modified by replacing the second E4 with B9. Cell F4 should now read: (E4-G$30)/B9*10.

Table 2 shows the expected cash flows associated with a project being considered by an engineering firm. Cash outlays are shown under CONTRIBUTIONS and cash inflows are shown under WITHDRAWALS. After receiving the last of these inflows the firm expects the project to have a market value of $40,000, which is entered in cell E4. The spreadsheet shows this project's IRR to be 3.21%. If this firm's cost of capital is less than 3.21%, the project will produce a profit and the project should be undertaken.

After a project has been completed the spreadsheet could be used to determine the annualized return actually earned by the firm on its investment.

Return on a Pass-Through Security

The spreadsheet can also be used to calculate the return on a pass- through security after all payments have been received. The most popular of these securities, especially among retirees, are those guaranteed by the Government National Mortgage Association (Ginnie Maes) and the Federal National Mortgage Association (Fannie Maes). These securities are known for their complexity because of the irregular income payments they provide. Pass-through securities represent pooled debt obligations, such as home mortgages, that have been repackaged as shares. Income from this debt is passed from debtors through the lending intermediary to holders of the security. Since all income from these securities is derived from the repayment of the underlying home mortgage loans, income depends on the rate those loans are repaid. A pass-through expected to provide income over the next eight years may turn out to have a repayment period that is significantly shorter or longer than eight years. If the repayment period differs significantly from the expected, the actual yield on the investment may also differ. Once an investor's pass-through security has been paid off, he or she knows the amount paid for it, the cash flows received, and the yield expected, but does not know the yield actually received.

This yield can easily be calculated with the modified spreadsheet used in Table 2. The purchase price of the security is recorded under CONTRIBUTIONS and interest and principal payments are recorded under WITHDRAWALS as negative amounts. Zero is entered in cell E4 CURRENT MARKET VALUE since the security's value is zero once all principal and interest payments have been received. PROJECTED VALUE in column E shows the projected value of the amount invested and principal payments received. The rate of return that equates the projected value of the purchase price with the projected value of interest and principal payments after all payments have been received produces a projected value of zero and is the internal rate of return. Table 3 shows the income flows generated by the purchase of a $70,000 pass-through security. These payments represent an IRR of 6.21%. The IRR here has the same meaning as shown in Table 1 with mutual fund investments. An IRR greater than the best available return on an alternative investment means this security was a profitable investment. One less than the best available alternative return implies a superior investment was available.

Yield on a Bond Sold Prior to Maturity

Analysis of this investment makes use of the modified spreadsheet shown in Table 2. The price paid for the bond is entered under CONTRIBUTIONS (column B), all principal and interest payments are entered as negative amounts under WITHDRAWALS (Column C), and the price at which the bond can now be sold is entered as CURRENT MARKET VALUE (cell E4). In Table 4 a bond purchased for $70,000 is sold several years later for $75,000. This investment produced an IRR (yield) of 9.96%.

Financial Planning for College or Retirement

Investors who save for their children's education or for retirement struggle with the relationship between their yearly savings, the return on their investments, and the amount of funds that will be available when those events eventually transpire. This spreadsheet can determine 1) the rate of return required for a given savings profile to grow to a target level as of some future date, 2) the amount of funds that will be available at a future date, given a savings profile and an assumed rate of return, or 3) savings profiles that will generate a target level of funds by a specified date given an assumed rate of return.

Table 5 shows the spreadsheet's first use. The client's investment target is entered as CURRENT MARKEr VALUE in cell E4 and planned savings are entered as CONTRIBUTIONS in column B. By recalculating (pressing function key 9) until the final PROJECTED VALUE equals the investment target in cell E4, the spreadsheet identifies the required rate of return. With the savings profile shown in the table, a client must obtain a minimum 9.71% return on investments, as shown under ANNUAL RATE OF RETURN in cell E3.

Table 6 shows the amount of funds that will be available at a specified future date based on the assumed savings profile and a 5% rate of return. This analysis is done by entering the assumed rate of return directly into cell E3. The final entry under PROJECTED VALUE shows the amount of funds that will be available at the specified future date. If this PROJECTED VALUE is less than the client's target level, the savings profile can be adjusted in a variety of ways in search of the one that will best achieve investment objectives.