Magazine article The CPA Journal

Irregular Cash Flows and Rates of Return: A Spreadsheet Solution

Magazine article The CPA Journal

Irregular Cash Flows and Rates of Return: A Spreadsheet Solution

Article excerpt

It is not uncommon for clients to ask their CPAs to analyze investments for them. These may be investments that were made a number of years ago or merely proposed investments. With investments that involve uniform payments at regular intervals the return can easily be calculated with a simple business calculator. But with investments where contributions or repayments are made in nonuniform amounts at irregular intervals, evaluation is significantly more troublesome. Must the CPA defer this evaluation to a financial analyst, or purchase and study sophisticated financial software to calculate the return on his clients' investments?

No. CPAs with an elementary knowledge of Lotus 123 or any comparable spreadsheet can, in five minutes, create a spread sheet 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 (at)DATEVALUE(A9)'

2. E1:+E1+F4

3: E3:(at)IF(E1+E5>O,E1+E5,.5)

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:(at)IF(D9>O,E9,G8)

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.

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 E1's value to zero. The second investment can now be evaluated by repeating steps 1 through 4.

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

Search by... Author
Show... All Results Primary Sources Peer-reviewed

Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.