ON DISK!

Business Applications

Doing Spreadsheets On The ST

BY CHRISTOPHER L. HERD

Get started in the world of spreadsheets with FIGLS.ARC on your START disk.

Spreadsheets are marvelous tools with uses limited by your imagination alone. In this column and the ones that follow, I'm going to take you on a guided tour through the fascinating world of spreadsheets. What I cover will hopefully give you some insight into the "black magic" of spreadsheet template writing. And when I'm finished you'll have an entire spreadsheet ready for use and modification.

I used LDW Power to write the templates, but they'll be generic enough to transfer to your favorite spreadsheet program. If you're serious about learning spreadsheets, however, I recommend that you buy one of the Lotus 1-2-3 clones, LDW Power or VIP Professional.

You'll find this month's spreadsheet template on your START disk in the file FIGLS.ARC. Copy this file to a blank, formatted disk and then un-ARC it following the Disk Instructions elsewhere in this issue. You'll need either LDW Power or VIP Professional to load this template. Follow the programs directions for loading a template file and load in FlGLS.WKS--it's in Lotus 1-2-3 version 1.0 format.

All Misconceptions Aside

Let's first clear the air of some of the basic misconceptions about spreadsheets. The first misconception is that spreadsheets are monstrous things maintained by huge accounting departments. Not so--several of the templates I'll be presenting here will be small enough to fit onto one screen, and they'll be plenty useful for your personal finances or small business.

The second misconception is that you must use a spreadsheet to balance your checkbook. This normally causes uninitiated skeptics to respond with something like "I balance my checkbook a lot faster with my pocket calculator, thank you." Yes, you can balance your checkbook with a spreadsheet template, but you can also do a whole lot more. And that takes us to the subject of this column.

The Gain/loss Statement

Before you can run, you should first learn to walk. So, before we delve into business spreadsheets, we'll start with a basic template that you can use at home. I call this one-page template my "Financed Investment Gain/Loss Statement." As you work with it, it will give you some insight into the functions and flexibility of spreadsheets.

Here's the situation: Have you ever wondered how much you could realize if you sold your house? The template as set up, is applicable to any financed investment that either appreciates or depreciates.

The Details

Now, let's discuss some of the more important details of our template. The numbers you must input are the purchase price, financing interest rate, financing term, appreciation, the date of first payment and the date of the most recent payment. From these last two dates and the entry in the monthly payment schedule, the present age of the property will be calculated. If your program doesn't have the date functions necessary to perform this calculation (both VIP Professional and LDW Power do), you may omit lines nine and 10 and input the age of the property in months into the current cell C12.

Similarly, the entry in cell E12 is just a reminder of the mortgage's age--in months; it can be input manually as a label if you don't have the proper date function.

Another product of this spreadsheet is the exact amount of the payment in the top right corner of the output section. Like the rest of these numbers, it's calculated by the worksheet. Note the combined use of the IF function and the very handy payment (PMT) and present value (PV) functions. This helps to preclude spurious results.

Analyzing the Results

Assume that you bought the house for $84,600, financed it at 10 percent for 30 years, expect the property to appreciate at four percent per year and you've just made the January 1989 payment.

Your property is now worth $89,524.00, according to your appreciation rate. You still owe $83,919.53 on the $84,600 loan. The difference, presumably, is what you'd net (without considering closing costs or commissions) if you sold the house now for the market value and paid off the loan. This is your "Cash-Back Value."

Of course, that amount isn't straight profit. Over the last 17 months, you've been paying a horrendous amount of interest and not much toward equity. In fact, the interest payments alone amounted to $11,940.76, the "Accum Interest Value" as noted on the spreadsheet. So, to gain a cash-back value of $5,604.47, you spent $11,940.76 in interest payments, which means that your net gain is ($6,336.30), the spreadsheet's way of telling you minus $6,336.30!

Now, look at the last line. Your $6,336.30 loss, distributed over the 17 months you've been making payments, comes to only $372.72 per month (compare this to $400-per-month rent payment for a two-bedroom apartment). Without even factoring in the income tax savings for this mortgage interest deduction, you still came out ahead under these assumptions.

Try a little Experimenting

As an experiment, see what happens if the house appreciates at only two percent per year or how fast you can build up equity by getting an 8.5 percent mortgage for 15 years with 30 percent down (just multiply the purchase price by 0.7).

The template I've prepared is even more flexible than that. Maybe you don't own a house. Just plug in the purchase price of your new (or old) car, the finance rate and its age in the payment schedule. For appreciation, try -20 percent or -25 percent.

Christopher L. Herd lives and works in Colorado Springs, Colorado. This is his first article in START.

PRODUCTS MENTIONED

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

VIP Professional, $149.95. ISD Marketing, 2651 John Street, Unit 3 Markham, Onatario L3R 2W5, (416) 479-1880.