How to use your spreadsheet to create a loan schedule. (Compute's Getting Started with Personal Money Management)
by Richard O. Mann
Although personal-finance and tax preparation programs are extremely useful, the greatest financial software ever created is the simple electronic spreadsheet program. To show the incredible power of even a routine spreadsheet program, let's build a worksheet to analyze the principal and interest on a loan. You can still pay your bank $15 to create one of these for you, but with this simple spreadsheet, you can analyze any loan in no time.
We'll use Lotus 1-2-3 for this example, because it's the world's most frequently used spreadsheet program. This example works even with the oldest version of 1-2-3 as well as virtually any other standard spreadsheet, with only minor syntax adjustments.
We're setting up this sheet so that as soon as you type in the loan amount, annual interest rate, and loan term, the rest of the worksheet magically fills itself out.
Figure 1 shows the top of the finished worksheet. Start by filling in the top of the sheet. Fill in cells A1 through A7 by simply typing the labels. Adjust the widths of columns A through E to 13 spaces to make everything fit nicely. Type / WCCSA1..E1, press the Enterkey, type 13, and press the Enter key again.
Fun With Formatting
Now let's format some cells. Because most of the cells in the worksheet are dollars and cents, set the global format to comma format with two decimal places. Type / WGF, 2 and press the Enter key. To set the two interest rate cells to be percentages, type/RFP2, press the Enter key, type C4..C5, and press the Enter key again. Set C6 to General format by typing /RFGC6 and pressing the Enter key. For the last format, set the date fields in column A. Type / RFD1A12..A200 and press the Enter key. You can carry that format as far down as necessary for the longest loans you'll be analyzing.
Fill in rows 9 and 10 with column headers as shown in Figure 1. Precede the typed labels and strings of dashes with a caret ([caret]) to center them over the column.
Now it's time for some data. So we can see things happen as we go, enter a loan amount in cell C3 and an interest rate in C4. Note that you need to enter the annual rate in decimal form. Thus, typing ".12" in the cell displays 12% because we formatted the cell for percentages. Skip down to C6 and enter a loan term in months. Type the word months in cell D4 to remind us that we're working in months.
We'll calculate the monthly interest rate in cell
C5 by typing in +C4/12, which tells the computer to divide the contents of cell C4 by 12. Now whenever we enter an annual rate in C4, the monthly rate appears in C5.
Next we calculate the monthly payment amount using Lotus's neat formula provided for this very purpose. The syntax is @PMT(principal, interest, term). For us, that means we should type in @PMT(C3,C5,C6). Note that we're using the monthly interest rate (in C5), that matches the term, which is also in months. Now cell C7 automatically displays the correct monthly payment amount that goes with the principal, interest rate, and loan term specified in those top few cells.
Now go to cell E12 and type+C3. That pulls the beginning principal amount down to become our starting point. Go to A12 and enter the beginning date of the loan with the formula @DATE(YY,MM,DD). In our example, that would be @DATE(93,1,1).
The next line is the guts of the whole sheet. In A13, type +A12+31; that means to move the date 31 days ahead. In B13, type+$C$7. The dollar signs keep the target cell address from changing when we later copy this cell.
In C13, we calculate the interest amount for the month with a simple formula. Type in +E12*$C$5. This multiplies the beginning-of-the-month principal balance by the monthly interest rate. As we copy the formula down column C, the relative address of the beginning-of-the-month value changes to always be the row above it, but the dollar signs force it to always use the interest rate in C4.
The principal part of the payment is merely the total payment amount less the interest portion, so in cell D13, type+B13-C13, Finally, in E13, we'll subtract this month's principal reduction from the prior month's balance by typing +E12-D13.
>From there, merely copy row 13 into the rows below it as many times as needed to cover the number of payments. Type /CA13..E13, press the Enterkey, and type A14..A200 (or however far you need to go). You'll have one problem--the dates will not always be the first of the month (or 15th of the month, or whatever your due date is) because not all months have the 31 days we added as the interval for the first month. You can browse through column A, editing the cells and changing the interval. Once you have the first year right, copy those 12 date cells into the next year's date cells and the pattern holds. Do this as many times as necessary.
There it is. You may want to spiff it up by inserting space after each December payment and putting in annual subtotals of principal and interest.
Be sure to save the worksheet! Now, whenever you change those first three important cells, the entire worksheet changes, giving you an automatic amortization schedule.