Let me describe a feature of the solve program that will make your life in the math lane much simpler. This technique ties together Lotus 1-2-3 and HP CALC's SOLVE function. Once you have seen this technique, you will be able to design interchangeable solution programs in SOLVE with one or two lines of code.
To show you how this technique works, I'll use INSURE.WK1 , a simple 1-2-3 worksheet that compares the value of whole life vs. term insurance.
If you don't get The HP Palmtop Paper ON DISK and want to follow along on your 95LX, start 1-2-3 with a blank worksheet. Set column A to a width of 5 ((MENU) Worksheet Column Set-width 5 (ENTER)) and set the width of column D to 12.
Now key in the following worksheet labels and functions. (If you want to save a lot of keystroking, just key in the formulas in cells D11 and D13. The labels in column A are just reminders, anyway.)
Cell Contents
A1: ' Whole Life vs. Term Insurance
A2: 'Ann'l Prem for Term
A3: 'Ann'l Prem for Life
A4: 'Whole Life Cash Val
A5: 'Period (years)
A6: 'Whole Life Cash Val
A7: 'at end of Per.
A8: 'Avg Ann'l Int Rate
A9: 'over Period
A10: 'Future Value of Diff
A11: 'in Premiums
A12: 'Savings (+) Loss (-)
A13: 'with Term Ins
D11: @FV((D3-D2)/12,(D9/12)/100,D5*12)
D13: +D11-D7
When finished, your worksheet should look like this Graphic
Key in the numbers as shown below. If necessary, set the number of decimal places to 2 in cells D2, D3, D7, D11, and D13 (press (MENU) Range Format Currency 2 while in the cell. (This makes the numbers in those cells appear with currency formats.)
At Cell: Key in
D2: 310
D3: 453
D5: 25
D7: 5000
D9: 6
The last two lines will not appear on the HP 95LX. Press (<DownArrow>) to scroll the screen to see the "bottom line."
D11: $8,258.18
D13: $3,258.18
In other words, if you invested the money you saved with term insurance at a 6% annual rate, you would earn $5,000 more over 25 years.
Backsolving Using HP CALC with Lotus 1-2-3
Now, suppose you wanted to run the program in reverse to find what annual premium for term insurance would give a saving/loss figure of $0.00 exactly. 1-2-3 can't solve such a "backsolving" and uses the HP CALC program.
Keep 1-2-3 running and press (HPCALC). Then press (MENU) Solve to activate the SOLVER. Press (F2) to start editing and type in the following formula. (You can press (<Tab>) for new line within HP Solve editor.)
CALCCELL(
[TermPrem,d2],
[LifePrem,d3],
[LCVPeriod,d5],
[LCValue,d7],
[AnIntRate,d9],
d13)=
SavLoss
Press (F9) (Calc) when you've entered the above and key in the same set of numbers as you did before.
TermPrem = 310
LifePrem = 453
LCVPeriod = 25
LCValue = 5000
AnIntRate = 6
Press (F7) SavLoss should = 3,258.18 (assuming that you've used the command: (MENU) Options Format Fix 2 to set decimal places to two).
Now to find what annual term insurance premium would give a saving/loss figure of $0.00. With the highlight bar on SavLoss, type in 0 and press (F7). Then press (F2) to determine the Term Premium. The answer is: TermPrem = 366.42.
Switch back to 1-2-3 and notice that $366.42 is now in cell D2. You have "backsolved" for that value. You can delete everything in the D column except the formulas in D11 and D13 and the CALCCELL C formula will still work. Just go to CALC and press (F7) and all the numbers in the CALCCELL formula will be reestablished in the worksheet. (For more about this trick, see the HP 95LX User's Guide, pages 29-24 to 29-27.)
This technique will work with any spreadsheet that requires a number of input cells and produces a "bottom line" result. One stipulation mentioned in the HP 95LX Users Guide, is that the spreadsheet must be active, and must have "READY" showing in the upper right corner. Also, the spreadsheet's input cells must not be protected.
How CALCCELL Works
CALCCELL() provides one of the missing parts of Lotus 1-2-3 found in other spreadsheets, the ability to do "goal-seeking". It does this in a way that is far better than any other goal-seeking routine that I've seen. Not only can you solve for one variable in terms of another, you can solve for ANY variable in terms of all the others at the push of a function key.
CALCCELL is made up of a series of input "arguments". Each argument is enclosed in square brackets. Inside each pair of brackets are two parts; the menu prompt for SOLVE and the cell reference for 1-2-3. Each of these two parts is separated by a comma.
If there is more than one input argument, the pairs of square brackets are separated by commas as well.
The final argument, inside CALCCELL's parentheses, is the 1-2-3 output cell. It is the value in this cell that is made equal to the label on the right side of the equal sign in SOLVE.
Backsolving is one of those rare instances in which the whole is greater than the sum of its parts. All that is left to do is to add CALCCELL's power to your old spreadsheets.
Until next time, Happy Palming!