THE CPA AND THE COMPUTER

May 2000

A SPREADSHEET TEMPLATE FOR INSTALLMENT LOAN AMORTIZATION TABLES

By Bruce Wampler

Accountants frequently encounter questions regarding installment loans, such as how changing the interest rate, the amount borrowed, or the term of the loan will affect the payment amount. Any handheld financial calculator can easily perform this type of "what-if" analysis. Sometimes, however, accountants prefer to view a complete amortization table for the loan. The amortization table enables the user to easily ascertain, for example, the projected balance of the loan on a particular future date, or the amount of interest that will be paid in a given year. Other professionals, such as attorneys, bankers, or realtors, often need to generate amortization tables as well.

A flexible spreadsheet template can be used to analyze installment loans with equally spaced payments of equal amount beginning at the end of period one, such as an ordinary annuity. The key feature of the spreadsheet is its use of "IF" formulas that automatically adjust the visible output of the program to the proper length based on the total number of payments and eliminate the need to copy formulas to additional cells or delete unnecessary rows. The result is a clean output, regardless of the term of the loan.

Example

The Exhibit is a partial amortization table that shows how the spreadsheet output would appear. Row and column headings are included for discussion but would probably be omitted from the actual output. With the exception of the five variables provided by the user (cells F3­F7), all of the other cells in the worksheet contain formulas (the shaded cells) or labels. The values in cells F3­F7 are for illustration purposes.

User Inputs

The first user input is the date of the loan (cell F3). The first loan payment is assumed to occur one period following the loan date, and subsequent payments are made on the same day of the month (although a payment does not have to occur every month). This cell should be formatted using the desired date format.

The user would also enter the loan amount (cell F4) and the annual interest rate (cell F5). If the percentage format is used for cell F5, Excel 97 will interpret the interest rate properly whether or not it is in decimal form. The term of the loan in years should be entered in cell F6.

The number of payments per year, which determines the compounding frequency, is entered in cell F7. This value must equal one (annual payments), two (semiannual payments), three (payments every four months), four (quarterly payments), six (bimonthly payments), or 12 (monthly payments). The worksheet will not work properly for other types of payments, such as biweekly.

Cell Formulas

The formulas in this worksheet are designed for Microsoft Excel 97. (Understanding the formulas will allow the user to modify the program to work with other versions of Excel and other spreadsheet programs.) Cell references are integral to the formulas; adding or deleting a row or column means that cell references have to be modified.

Cell F9. =F6*F7. This calculates the total number of payments that will be made, and is referenced in other cell formulas.

Cell F10. =EDATE(F3,F6*12). The EDATE function calculates the final payment date by adding the term of the loan (in months) to the date the loan was made. This cell must have a date format to display appropriately. If the EDATE function does not work properly, it may be necessary to run the Excel 97 setup program and install the Analysis ToolPak. After installation, enable the Analysis ToolPak using the Add-Ins command on the Tools menu. Alternatively, row 10 may be omitted without any negative impact on the remainder of the worksheet.

Cell F11. =C15*F9-F4. This calculates the total interest for the life of the loan and is for informational purposes only (the output of this formula will be correct once the formula in cell C15 is entered).

Cell B14. =F3. This copies the loan date input by the user.

Cell F14. =F4. This copies the amount of the loan input by the user.

The most complicated formulas are in row 15, but once these are entered, the spreadsheet is substantially complete. All that remains is to copy these formulas to other cells. The best way to do this is to copy downward through row 374, which will accommodate loans of up to 360 payments. Using the IF function instructs the spreadsheet to display the results of the calculation only if it is appropriate for a particular loan; otherwise, a blank cell will appear. For example, the inputs in the Exhibit would result in an amortization table with 36 payments; changing the number of years to four would automatically increase the visible output of the worksheet to 48 payments. Specific formulas are as follows:

Cell A15. =IF(A14<F$9,A14+1,""). If the amortization table is not yet complete, this formula will add another row; otherwise, the cell will appear blank.

Cell B15. =IF(A15="","",EDATE(B14,12/F$7)). This formula will result in the current payment date unless no payment number is in column A, in which case the visible output of this formula (and those in columns C­F as well) will be a blank cell. Again, enable the EDATE function and give the cells a date format for the output to appear as desired. If problems with the EDATE function occur, omitting all of the formulas in column B will leave the remainder of the worksheet unaffected.

Cell C15. =IF(A15="","",ROUND(PMT(F$5/F$7,F$9,-F$4),2)). This calculates the amount of the periodic payment. Because actual loan payments are not made in fractional cents, Excel automatically rounds the amount to two decimal places. Due to rounding, the loan balance following the last payment will probably not be exactly zero.

Cell D15. =IF(A15="","",F14*(F$5/F$7)). This calculates the interest associated with each payment based on the previous loan balance and the periodic interest rate.

Cell E15. =IF(A15="","",C15-D15). This calculates the portion of each payment to be applied to principal by subtracting the interest in column D from the payment amount.

Cell F15. =IF(A15="","",F14-E15). This calculates the new loan balance by subtracting the principal reduction from the previous loan balance.

Other Tips

The spreadsheet discussed in this article can be downloaded from www.cpaj.com/down.htm in Excel 97 format. To use the spreadsheet, open the file, enter the desired inputs, and view or print the resulting table. To avoid inadvertently altering any formulas in the permanent worksheet, close the file without saving after each use or save it under a new name.

Because the amortization table may require several pages to print, column headings should be repeated at the top of each page by selecting Page Setup from the File menu, clicking on the Sheet tab, and entering the following in the Print Titles section: "Rows to repeat at top: $13:$13"; "Columns to repeat at left: $A:$F."

Finally, set the print area by placing the cursor over cell A1, clicking and holding the left mouse button, and dragging the mouse until all visible output is highlighted. Then, choose Print Area from the File menu and click on Set Print Area before printing in the usual manner. If the print area is not defined, Excel will print out all cells containing labels or formulas, even if the cells have no visible output. For relatively short amortization tables, the result will be several pages of unnecessary output containing only column headings. *