|Everything HP200LX: Knowledge, Products, Service|
The 1-2-3 Column: Using Formulas in a Lotus Spreadsheet
Carl shows how to use formulas and @functions in a Lotus spreadsheet to calculate loan payments and set up a loan payment analysis table.
By Carl MerkleOne of the most powerful aspects of any spreadsheet program is its ability to use formulas to turn raw data into useful information. Understanding a spreadsheet often means understanding the formulas used in it.
This article demonstrates the use of formulas and @functions (At Functions) using an example that calculates loan payments for purchasing a home. It also demonstrates some aspects of spreadsheet navigation, the use of 1-2-3's copy and formatting commands, and the obscure but powerful data table command.
Well start with an example that integrates HP Calc and 1-2-3, teaching some commonly used spreadsheet concepts in the process.
Calculating loan payments for a new home
Let's say you're shopping for a new home, and find a home that you and your spouse really like costing $185,000. You have the cash for the 20% down payment and closing costs. However, you check with lenders and discover that a variety of financing options are available. You settled on a fixed-rate program, but now have a choice between a higher interest rate and lower loan origination costs or a lower rate and higher loan origination costs. You also have a choice between different mortgage amortization lives. You want the best economic deal over the life of the loan but are concerned about the amount of the monthly and annual payments. What loan option do you choose?
You can solve this question using the Time Value of Money module in the HP Calc program. But the number of loan choices is fairly extensive and 1-2-3 works better with these multiple scenario evaluations.
Quick answer using HP Calc
As a first step, let's use HP Calc to get a quick answer to our loan payment question. Go to HP Calc and press (CTRL)-(T) to bring up the Time Value of Money (TVM) screen. Clear any old data by pressing (MENU) Clear Data. Calculate the loan balance by multiplying $185,000 by 80% (0.8). Enter the result of 148,000 in TVM by pressing (F8) (PV). Enter an annual interest rate of 8.0% by pressing 8 (F7). Enter the number of months for a 15-year loan by pressing 180 (F6). Calculate the monthly principal and interest payment for this loan by pressing (F9).
Your screen should look like this:
N = 180.00
I%YR = 8.00
PV = 148,000.00
PMT = -1,414.37
FV = 0.00
This says your monthly payment will be $1,414.37 to fully amortize a $148,000 loan over 15 years at an 8.00% fixed interest rate. In order to determine the total interest to be paid over the life of the loan, you'll have to run an amortization table.
Creating an HP Calc amortization table in 1-2-3
Press (1-2-3) and make sure the spreadsheet is empty by pressing (MENU) Worksheet Erase. Now switch back to HP Calc and build an amortization schedule in 1-2-3 from HP Calc by following these steps:
Loan amortization table created by HP Calc in Lotus 1-2-3
Column C now contains principal payments by year, and column D contains interest payments by year. Let's set up 1-2-3 to automatically total up these columns.
First, go to the beginning of the spreadsheet and take a look at the whole table. Move your cursor to cell A1. (A quick way to do this is press (Fn)-(LeftArrow), the HOME key combination.) If you cant see the whole amortization table, press (Fn)-(Spacebar) to Zoom between the default 64x18 screen and an 80x25 screen, which displays more of the spreadsheet.
Draw a separator line with label prefixes
When you add up a column of numbers on a piece of paper, you draw a line at the bottom to separate the column of numbers from the total. Let's do that in our spreadsheet. First, move your cursor to cell C18. (A quick way to do this is to press (F5), key in C18 and press (ENTER).)
Unfortunately, you cant just enter a bunch of dashes for this separator line. 1-2-3 thinks a dash is a minus sign and that you are entering a number. Try it and you'll notice that the Mode indicator (the little box in the upper right hand corner) changes from READY to VALUE. Since this approach doesn't work, let's try something different let's use a label prefix.
While in cell C18 press (\) (backslash), type in one dash and press (ENTER). Notice that cell C18 now has nine dashes displayed in it. The backslash is a label prefix that tells 1-2-3 to repeat a subsequent character as often as possible to fit the cell's column width. There are four label prefixes which tell 1-2-3 how to display text in a cell. They are summarized as follows:
Label prefix What it does
` Left aligns text
^ Center aligns text
" Right aligns text
\ Repeats text
Text (labels) can be re-aligned one cell at a time, or over a range of cells that you specify using the (MENU) Range Labels command. However, the Range Labels command does not create a repeating Label prefix.
Now that we've made a subtotals line and learned how to use label prefixes, well move on to the primary subject of this tutorial, @functions and formulas.
Adding, multiplying, and more using 1-2-3 @functions
Storing a bunch of data in a 1-2-3 spreadsheet is only the first step. For financial data to be useful, you have to make some calculations. This is where the @functions come in to play.
Using the arrow keys, move the cell pointer to cell C19. Well enter a formula here to add up all the numbers in cells C3 through C17. (The formula will actually add up all numbers from cells C1 through C18. It ignores the text labels it finds in C1 and C18.)
We've added up one column. Let's add up the other columns. Fortunately, we don't have to re-do the previous steps. We can simply copy the formula from cell C19 to other cells. Lotus will automatically adjust the formula for us. Here's how its done.
Eliminate extra work and mistakes by copying formulas
Well copy both the separator line and the formula below it.
One tremendous value associated with copying formulas is accuracy. 1-2-3 adjusts formulas so that their cell references are in the same location relative to the original formulas. This is called relative addressing and it is one of the most important spreadsheeting concepts. I deal with this concept more in this and subsequent columns.
Deleting unwanted rows
Let's say you don't like the empty row 2 at the top of your spreadsheet. Move you cell pointer (i.e., cursor) to cell D2 (use either the arrow keys or F5 (GOTO) method). Press (MENU) Worksheet Delete Row. 1-2-3 now prompts Enter range of rows to delete: D2..D2. Press (ENTER).
Now move the cell pointer to cell D18 and look at the first line which should now read: D18: @SUM(D17..D1). Because of relative addressing, 1-2-3 automatically adjusted the formula you had previously created.
Center column titles to improve the look of your spreadsheet
Press HOME ((Fn)- (LeftArrow)) to go to cell A1 and notice that all the labels are left aligned. They would look better if they were center aligned. Press (MENU) Range Label Center. 1-2-3 now prompts Enter range of labels: A1..A1. Notice 1-2-3's POINT mode. Use your right arrow key to highlight cells A1 through H1 and then press (ENTER). The labels are now all center aligned.
Remember when we started that we wanted to use 1-2-3 for some what-if analysis? Let's say we want that loan analysis work to appear at the top of the spreadsheet and the amortization table further down.
With your cell pointer on cell A1 press (MENU) Worksheet Insert Row. At this time, 1-2-3 switches from MENU to POINT mode and prompts you to Enter the row insert range: A1..A1. How far down do you want to bump the amortization table? For now, let's just page down a couple of screens and get it out of the way. (We can adjust this later if its too far down.) Press PG DN ((Fn)-(DownArrow)) two times and then press (ENTER). The 1-2-3 screen should now appear blank.
Using the END key to navigate in worksheets
Where did our amortization table go? Find it quickly by pressing END ((Fn)-(RightArrow)), a little END sign should appear at the lower right hand corner of the screen. Then press the DownArrow once. Your cursor should now be at cell A42.
Press (Fn)-(RightArrow) and then (DownArrow) again. You should now be at cell A57.
Creating and entering some loan analysis formulas
A spreadsheet formula tells Lotus what you want done (i.e., @sum), and the range of data you want to do it to (C18..C1). You can specify the range of data using the anchoring method described earlier in the article. You can also just type in the range, in the format (Cell#..Cell#) (two cell references inside parentheses, separated by two periods).
In order for a formula to be recognized as a formula and not a label, the formula must begin with one of the following 17 characters:
0 1 2 3 4 5 6 7 8 9 . + - ( @ # $
The @sum formula used earlier began with an @. Go to cell A1. (Remember the HOME shortcut?) Then, we'll begin to fill in some of the data and formulas needed to analyze this loan.
Enter into these cells, the following formulas or constants:
Cell B1 Enter 185000, the sales price.
Cell B2 Down payment. In B2 type (+), (UpArrow), key in *.2 and press (ENTER). Cell B2 should display 37,000 and the first line of the control panel should say B2: +B1*0.2.
Cell B3 Actual amount of the loan. In B3, type (+), then press (UpArrow) (UpArrow) to go to cell B1. Type (---) press (UpArrow) to go to B2. Finally, hit (ENTER). Cell B3 should display 148,000 and the first line should say B3: +B1-B2.
Cell B4 Type in the number of years of the loan. Key in 15 and hit (ENTER).
Cell B5 Type in the interest rate of the loan (8%). Type in .08 and hit (ENTER). Notice that B5 is not formatted to display this properly. Press (MENU) Range Format Percent 3 (ENTER) (ENTER). Cell B5 should now display 8.000%.
Cell B6 Monthly loan payment. Type in: @PMT(B3,B5/12,B4*12). The syntax is @PMT(principal, interest, term). The interest rate (B5) needs to be adjusted to a monthly amount, and B4, the loan life in years, needs to be multiplied by 12 to give the loan life in months. Cell B6 should now be displaying 1,414.
Cell B7 Total annual payments. Type in +B6*12. Cell B7 should now read 16,972.
Cell B8 Payment constant (expressed as a percentage of the original loan amount). Type in +B7/B3.
Cell B8 is not formatted to display this properly. Press (MENU) Range Format Percent 3 (ENTER) (ENTER). Cell B8 should now read 11.468%.
Cell B9 Total payments of the loan. Type in +B7*B4. Cell B9 should read 254,586.
Cell B10 Total interest paid over the life of the loan. Type in +B9-B3. Cell B10 should read 106,586.
By now you should be getting an idea of how creating formulas work. One of the problems working with formulas is that it's hard to remember what the variables are referring to. Take a look at the formula in cell B6 above: @PMT (B3,B5/12,B4*12). Its hard to look at this formula and figure out what's going on. Fortunately, Lotus provides a way to assign Range Names, which make a formula a little easier to understand.
Introduction to Range Names in formulas
In the example we've been working on, go to cell A1 and press (MENU) Range Names Labels Right. 1-2-3 will switch to POINT mode and prompt you to Enter label range: A1..A1. Using the down arrow key, highlight A1 through A10, and then press (ENTER).
Now move your cursor to B2 and look at the first line of the control panel. Notice that the first line says B2: +SALES_PR*0.2 instead of B2:+B1*0.2. The B1 in the formula is now displayed as the Range Name SALES_PR.
Here's what happened. When we used the Range Names Labels Right command above, the names or Labels in column A were associated with the Cells to the right in column B. So B1 now had the name Sales_Pr, B2 now had the name Down_Pmt, and so on. Now, whenever we enter a cell reference in a formula, it is displayed as the name we have associated with it.
Using this trick can obviously be very useful for constructing formulas with built-in documentation. A cell or range given a name can be used in a formula either by the name you have given it, or by the cell address. Range names can be created as we did above from a label in an adjoining cell on the right, left, up or down.
The above spreadsheet lets us vary the sales price, term of the loan in years, and interest rate and conduct simple what if analyses. For example: What if the interest rate changed to 8.25%? What if we decided to get a 30-year mortgage? We'd simply plug in the changes and see how it affects the other variables.
Setting up a monthly loan payment analysis table
Let's set up a loan analysis table to display monthly payments as we vary the interest rates and length of a loan. Well create a data table with the different interest rates displayed across a top row and different loan lives down the left column.
First, let's set up the interest rate row:
Earlier we used the Range Name feature to associate an understandable name with a formula in a single cell. We can also assign a name to a range of cells. This saves keystrokes later on by allowing us to refer to an entire range of cells by a simple name.
Let's name our table with a short name like T for Table or Temporary.
Move your cursor to cell A12 and press (MENU) Range Name Create. At the Enter name: prompt press (T) and then (ENTER). Then at the Enter range: prompt use your arrow keys to highlight the entire table from A12 to H16 and then press (ENTER).
Now we'll create a data table that will calculate the total interest payments over the life of the loan. Instead of typing in everything again, well make a copy of the previous table and then modify the original to show the total interest payments over the life of the loan.
With your cell pointer still in cell A12, press (Fn) (DownArrow). Then use the up arrow key, go to cell A21 and type in: Table of monthly payments. Complete the entry by pressing (DownArrow). Press (DownArrow) once more to place the cell pointer in A23.
To make a copy of the table, press (MENU) Copy and we are asked Copy what? Enter T , the name of Range and press (ENTER) (ENTER). By now, you should be getting a feel for the usefulness of range names, how copying data and 1-2-3s prompts work.
Updating a previously defined data table
Let's modify the original table to calculate the total interest payments over the life of the loan.
You now have the core structure of a useful model for loan payment evaluations.
You might want to compare the difference between the total payments and
interest savings over the life of the loan. For example, I found out that
on an 8% loan, with a monthly payment of $328, a 15-year loan would save
me $136,324 over a 30-year loan. Think of how many toys (I mean tools)
you could buy with that.
Copyright © 2010 Thaddeus Computing Inc