Can You Afford College? File COLLEGE.ARC on Side 1 of your START disk.

Welcome to our third installment of this spreadsheet column. While spreadsheets are used primarily in businesses, their power can also be applied to solve financial questions in personal finance. After all, managing your own money effectively should always be your first order of business!

This time I'd like to discuss some of the things spreadsheets do well -and some they don't do well at all. In order to understand the limitations of spreadsheets, it's helpful to look at their design philosophy. The granddaddy of spreadsheets, Visicalc was designed to solve a series of financial problems. These financial problems - theretofore solvable only with an electronic calculator -called for a super calculator, one with 50 or even 100 displays.

The designers of the first spreadsheet called it Visible Calculator or Visicalc. It was composed of an
array of calculator displays, each containing its own mini set of programming steps, but even more revolutionary was that each display (cell) could refer to the calculation result in any other display. It was truly a "visible calculator." This design philosophy has withstood the test of time. Most spreadsheets today adhere closely to this design philosophy and thus still have the same limitations, LDW Power included.

Spreadsheets are essentially an array of programmable calculators,
each with the ability to look at and use the contents of or results from
any of the other thousands of similar calculators in the spreadsheet.

The Limitations of Spreadsheets
It is useful to think about spreadsheets this way, because it gives you a good idea of what you can and can't do with one. One thing that spreadsheets do not perform gracefully is iteration, the repeated recalculation of a formula where the results of the previous calculation are a part of the next. Early on, the term circular reference described an error condition. (Circular reference is where a series of cells point in a circle or where every cell depends on every other cell).

Later on, more sophisticated spreadsheets began to be able to cope with this condition - an indicator informed you of the circular condition and the spreadsheet was forced into manual recalculation mode. Pressing the recalculation key repeatedly would produce successive results of all circular references involved. LDW Power can be set to recalculate a set number of times from one to 50 with the /WGRI command. This performs the set number of recalculations for each press of the RECALC button, (F9). It's still a rather crude way to approach the problem.

There are some functions in the Lotus 1-2-3 command language (Recalc and a For-Next statement) that take the problem of iteration one step closer to a satisfactory conclusion. Unfortunately, these commands don't yet exist in LDW Power. (I have already approached Logical Design Works on this matter, and their answer was simple: if their spreadsheet sells, they'll upgrade it.)

Several commands should be included in spreadsheets to handle iteration. The For-Next loop is one that's necessary. There should also be a simple iteration command such as @ITER(expression,number,limit), in which you could specify something like @ITER(A2=A2+1/A2,100,0.001) to calculate the expression 100 times or until the difference between successive values is less than 0.001.

What You Can Do in the Meantime...
You have several options. One of the most innovative and easiest to use iteration machines is the human brain. Your brain, when used with a computer to take the drudgery out of performing intermediate calculations, can solve a wide variety of problems.

The spreadsheet on this month's START disk illustrates another solution. It solves a problem that does not have a simple, one-formula answer, but relies on iteration values supplied by you. But don't worry -it'll be easy enough.

The spreadsheet itself approaches a modern problem: the rising cost of college education and how much you should save if you have children or are contemplating starting a family.

Getting Started
To get started, boot this month's START disk; the START Menu program runs automatically. After following the instructions to backup your START disk, click on Prepare Disk and select "College Spreadsheet;" the file COLLEGE ARC will automatically un-ARC onto the disk you choose. In the file COLLEGE.ARC is an LDW Power spreadsheet file called COLLEGE.LDW. (Editor's Note: We have decided to standardize on the .LDW file format, since it has become the spreadsheet of choice for ST users; we strongly recommend that if you need a spreadsheet for your ST buy LDW Power for its speed and, well, power!)

Let's begin our examination of the spreadsheet. Boot LDW Power and load the file COLLEGE.LDW. You can either use the mouse and drop-down menus to select Retrieve from the File menu or use the keystroke sequence [/] (to bring up the Lotus-like menus, [F] (to select the File menu) and [R] (to Retrieve a file). Whichever method you use, you'll be presented with the file-selector box from which you can load COLLEGE.LDW. After the file is loaded, it should appear like the illustration. (We've shown the worksheet in compressed view mode in order to fit it all in the illustration; the keystrokes to toggle the compressed and normal views are: [/], [W] (Worksheet), [W] (Window) and [V] (View).)

Here is what the spreadsheet tells you: rows 6 through 12 are your work area where you can consider the choice of college. The figures given are undoubtedly out-of-date, but typical for a public college when I went to school. You might check Money or U.S. News & World Report for college costs; both magazines have run annual issues on that topic. Whatever you put into cells D7 to D10 is summed up in cell D12 and used by the rest of the spreadsheet. This is the amount the spreadsheet assumes is needed now. Then, you specify the number of years until the money is needed; the spreadsheet applies your assumed rate of inflation over this period.

Meanwhile, you'll be investing monthly at some rate of investment to obtain the amount you'll need in the future. If seven percent seems a bit high, try six percent - or if you have access to high-interest investments, try a higher rate. The spreadsheet then takes the monthly investment amount and converts it into a future value after 17 years (or however many you specified). It then subtracts the yearly inflation-adjusted college costs and adds year-end interest to the account, four times.

But wait: the monthly level of investment was precisely what you didn't know! just insert any number that's halfway reasonable and let the spreadsheet go its merry way. After the fourth year of withdrawal, you'll want the net value to be positive, but barely so to insure that your investment will just exceed all of the withdrawals. This last year-end-value is calculated in cell E28 and repeated in cell F5.

Brainial Iteration
With cell Al in the upper left corner of the screen, page down until the line containing the monthly investment appears on the screen. Notice that the final balance is still visible at the upper right corner of the screen. Now place the cell pointer in the cell containing the monthly investment and modify it until the final balance is barely positive. If it winds up too large, reduce your monthly payment; if the result is negative, increase your monthly investment - or find a higher rate of return and change the assumed rate accordingly.

This is how you iterate the spreadsheet manually ("brainially"). You will find that only three or four guesses will get you into the general ballpark you want to use for investment considerations.

Try this modification: erase all the dashes in cells A4 to F4, but leave the blank line for visual effect. Now place the cursor in cell A3 and notice the label in it. It has a label prefix of '. Replace this label prefix with a vertical dash (|) and press Return. Nothing happens. Now try printing the spreadsheet again. Make sure the print range is Al to F29. This is how you hide lines containing comments to the spreadsheet user. The cell containing the "hiding vertical bar prefix" must be in the very first column of the print range, however.

There's another way to arbitrarily exclude information on the spreadsheet from being printed - which I'll save for an upcoming installment so that you can see it in action with a useful sample spreadsheet.

Coming Attractions
Next column, I'll present another "one-pager," which will analyze the feasabiity of refinancing an investment, be it your house, car or anything else financed at a fixed interest rate.

Christoph Herd (previously misidentified as Christopher) lives and works in Colorado Springs, Colorado. This is the third installment of his introductory column on spreadsheets.

PRODUCTS MENTIONED

LDW Power, $149.95. Logical Design Works, Inc., 780 Montague Expressway, Suite 103, San Jose, CA 95131, (408)435-1445.