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 Merkle

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:

1. 1. Press (F2) (Amort) and then (F10) (1-2-3). HP Calc starts 1-2-3 and line two of 1-2-3's control panel displays Select table location: A1.
2. 2. Press (ENTER). A dialog box appears saying Amort/1-2-3 link opened.
3. 3. Press (F2) (Go) 15 times to amortize the loan over its 15-year life. (You'll know you've done this enough, when the screen displays Group 15, Payments 169-180.)
4. 4. Press (123) and you'll see a loan amortization table created by Calc in 1-2-3. The table just created is unformatted, making it difficult to read. Format the entire worksheet to display numbers in a comma format rounded to the nearest dollar by pressing (MENU) Worksheet Global Format , 0 (ENTER).

 

 

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

1. 1. Type in @sum( (don't hit (ENTER) yet). This is the first part of the formula. It tells Lotus what you want to do (sum or add up something). It doesn't tell Lotus the range of data you want to do it to. That's what you do next.
2. 2. Press your up-arrow key once. Notice that line two of the control panel has automatically added C18 to what you typed. It now says @sum(C18.
3. 3. With the cell pointer still resting on cell C18 press (.). This is called anchoring. The formula displayed in the second line of the control panel now contains the following: @sum(C18..C18.
4. In essence, the formula now says, add up all the numbers beginning with the number in cell C18 and ending with the number in cell C18. Of course, this doesn't make much sense. You want to add up the entire column of numbers. You want the final cell reference in this formula to read C1.
5. 4. Again use your up arrow key to move the cursor to C1. Notice that as you do, the entire column is highlighted and the second line now reads @sum(C18..C1.
6. 5. Complete the formula by pressing ) (close parentheses) and (ENTER). Cell C19 now displays 148,002 and the first line of the control panel reads C19: @SUM(C18..C1).

 

 

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.

1. 1. Using your arrow keys, go to cell C18. Make sure 1-2-3 is in READY mode.
2. 2. Press (MENU) Copy. The second line says Copy what? C18..C18, and 1-2-3 switches from MENU mode to POINT mode.
3. 3. Press the down arrow key once. The second line now says Copy what? C18..C19, and C18 and C19 are highlighted.
4. 4. Press (ENTER). The second line now has a new prompt saying: To where? C18. 1-2-3 is still in POINT mode.
5. 5. Using the right arrow key move the cell pointer to cell D18 and press (ENTER). The cell pointer should now be back where we started in cell C18, but cell D18 has dashed lines and D19 has a column total displaying 106,585.

 

 

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.

Inserting rows

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.

1. 1. First type in the following labels in column A at the cell reference indicated:

 

 

A1: Sales_Pr

A2: Down_Pmt

A3: Loan

 A4: Term_yrs

 A5: Rate

 A6: Mo_pmt

 A7: Annl_pymt

A8: Pmt_Const

A9: Total_$pd

A10: Ttl_IntPd

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:

1. 1. Move the cell pointer to cell B12 and enter 7.25% as the first interest rate. To do this, type in .0725 and press (ENTER).
2. 2. Adjust the formatting so this and other interest rates you enter display as percentages. Press (MENU) Range Format Percent 3 (ENTER). At the prompt saying: Enter range to format, highlight cells B12 through H12 and then press (ENTER).
3. So far, we've entered the first interest rate and formatted the cells to display interest properly. We could manually enter interest rates in cells C12, D12, and so on. But there's a quicker way using the copy command. We want the interest rate to increase 1/4 a percentage point each time. Follow these steps:
4. 3. In cell C12 type .0025+ and press (LeftArrow) (ENTER). (The first line should now read C12: (P3) 0.0025+B12). This tells 1-2-3 to add .0025 to the number in B12.
5. 4. Now copy this formula to cells D12 through H12. With the cursor on C12 press (MENU) Copy (ENTER). Move the cursor to D12 and press (ENTER). Repeat this procedure, changing D12 to E12, F12, G12, and H12. Interest rates of 7.250%, 7.500%, 7.750%, 8.000%, 8.250%, 8.500% and 8.750% should appear in cells B12 through H12.
6. Next, enter the different loan lives down column A beginning at cell A13.
7. 5. Move the cell pointer to A13 and enter 15 in that cell.
8. 6. Move to cell A14 and type in (+). Then move to cell A13, type in +5 and hit (ENTER).
9. 7. Copy cell A14 to A15 and A16 (see point 4 above). The years of 15, 20, 25, and 30 should now be displayed in cells A13 through A16.
10. Now let's calculate 1-2-3 data table of the monthly loan payments. The data table feature lets you specify variables and quickly create tables. We'll talk more about the feature in later articles. For now, just follow these steps carefully:
11. 8. Move the cell pointer to cell A12, type in +B6 and press (ENTER).
12. 9. Define the dimensions of the data table. Press (MENU) Data Table 2. When 1-2-3 prompts you to Enter table range: A12, press (-.-), highlight the range A12 through H16, and press (ENTER).
13. 10. 1-2-3 will prompt you to Enter input cell 1: A12. Using the arrow keys move the cell pointer to cell B4 (Term_yrs) and press (ENTER). Then 1-2-3 will prompt you to Enter input cell 2: A12. Using the arrow keys move the cell pointer to cell B5 (Rate) and press (ENTER). 1-2-3 then seems to magically create the following table of monthly loan payments: Data tables are normally taught as an advanced feature of 1-2-3, but in reality they are quite easy to set up. They are very useful when you want a bottom line analysis of a complex calculation dependent on one or more variables. We will modify this data table a little later in the article, after we save the results of this initial analysis.

 

 

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.

1. 1. Move your cell pointer to cell A12, key in +B10 and press (ENTER). The first line now says A12: +TTL_INTPD.
2. 2. Press (F8) (Table). 1-2-3 then updates the table, but this time with the various scenarios reflecting total interest paid 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.