A spreadsheet template for installment loan amortization tables
Accountants frequently encounter questions regarding installment Moans, 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.
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 173-177), all of the other cells in the worksheet contaro formulas (the shaded cells) or labels. The values in cells F3-F7 are for illustration purposes.
The first user input is the date of the loan (cell F3). The fist 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 fi-equency, is entered in cell F7. This value must equal one (annual payments), two (semiannual payments), dime (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.
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 delet ing a row or column means that cell ref erences 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 FIO. =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 FIL =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 C 15 is entered).
Cell BI . =F3. This copies the loan date input by the user.
Cell FI . =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 pay ments. 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(Al4
Cell BIS. =IF(A15=”” “,EDATE (B14,12/F$7). This formula will result in the current payment date unless no pay ment 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.
CellDlS. =IF(A15=” ,”‘,F14*(F$5/F$’n). 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 FIS. =IF(A15=”” “‘,F14-E15). This calculates the new loan balance by subtracting the principal reduction from the previous loan balance.
The spreadsheet discussed in this article can be downloaded from www.capj.com 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 per manent 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 Al, 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.
Bruce Wampler, CPA, is an assistant professor of accounting at the University of Louisiana at Monroe. He can be reached at email@example.com.
Copyright New York State Society of Certified Public Accountants May 2000
Provided by ProQuest Information and Learning Company. All rights Reserved