Mortgage Amortization Schedule Calculator

Mortgage amortization schedule calculator program, which is compatible with either Lotus 1-2-3, release 4 or higher, or Microsoft Excel, employs a simple macro to prompt the user for the five key facts about a bond. It then automatically calculates the required amounts and produces a table that compares the income statement effects of three common amortization methods. The program prepares amortization schedules for a bond of virtually any duration and payment frequency. It also presents the information in the form of a graph.

Example To illustrate the use of the spreadsheet program, assume an accountant has to account for a bond issued at a discount with the following characteristics: Face value $ 1,000,000 Fair value $ 945,000 Term-years 15 Number of payments per year 1 Stated interest rate 8.5%

Entering Bond Information To operate the macro, simultaneously depress the Ctrl+Q keys. A dialog box will ask for the face value of the bond (the amount due at maturity), the market value of the bond (issue price), its term in years, and the number of payments made to bondholders per year (i.e., 1 if annual, 2 if semiannual, etc.) and the stated (contractual) rate of interest. Enter the interest rate in decimal format (i.e., the 8.5% interest rate would be keyed in as .085).

Using the Table

The first boxed range in the Table shows all the basic facts for the bond, allowing the accountant to verify the accuracy. The next boxed range shows the periodic amount of amortization using straight-line methods and the periodic interest expense (the sum of the cash payment and the amortization expense). For the example shown, the difference between straight-line amortization ($ 3,666.67) and the amount of first-year amortization using the effective interest method ($ 1,845.45) is $ 1,821.22.

The accountant can decide whether this difference would be material. For bonds issued at smaller (and, generally speaking, more realistic) discounts, the difference between straight-line and effective interest methods may be immaterial. If the bond described above were issued at a 1.5% discount (instead of the 5.5% shown) the difference between the two methods drops to less than $ 500, or about six-tenths of one percent of the straight-line amount.

Except for the five user-supplied facts about the bond, all other cells in the worksheet contain formulas or labels. The documentation worksheet describes the computations for each of these formulas.

Using the Graph To view the information amortization calculations in a graph, use the keystroke sequence / g v (Graph View). The Esc button will return you to the worksheet. Correcting Input

If the wrong facts are provided for the bond, simply complete the entry cycle and start over again. Occasionally the user may supply incorrect information that causes the macro to attempt impossible calculations and to lock up. This might happen if the user supplied percentages in the wrong format (e.g., 8.5 instead of .085 for 8.5%) or switched the interest and term values. The Ctrl+Break combination will terminate the macro execution and you can start over again.