Preparing an Amortization Schedule

Although the amount of your mortgage payment remains the same during the life of the mortgage, the amount of interest and the amount of principal vary with each payment. An amortization schedule like the one that appears in Figure 14.1 shows you the breakdown of each mortgage payment, identifying the portion of the payment that represents interest and the portion that represents principal.

Figure 14.1. The amortization schedule Quattro Pro can produce.

Why would you need an amortization schedule? Well, you could use an amortization schedule to double-check the statement your lender sends you at year-end that identifies the amount of interest you paid. Because mortgage interest is one of the few remaining deductions available to you, you do need to know how much interest you pay each year on your mortgage.

You can also use an amortization schedule to make extra principal payments on your mortgage, assuming that your loan has no prepayment penalty. Suppose, for example, that you pay the current month's loan paymentprincipal and interestand the next month's principal only throughout the life of your mortgage. Making payments in this fashion will cut the term on your loan in half and you'll end up paying half the total interest due; effectively, a 30-year mortgage will become a 15-year mortgage. Suppose that you decide to pay $100 each month in extra principal on a $100,000, 6%, 30-year loan with a regular payment amount of $599.55. You'll end up making only 251 (of 360) payments of $699.55 with a final payment of $350.99. The total interest you pay over the life of the loan is $75,938.04, whereas you would have paid $115,838.45 if you made no extra principal payments.

Quattro Pro's amortization schedule enables you to keep track of the effect extra principal payments have on your mortgage balance. You can type the amount of each extra principal payment you make in column G, and Quattro Pro will recalculate your loan term and payments.

To create an amortization schedule, start in a blank spreadsheet. Open the Tools menu, point to Numeric Tools, and click Analysis Tools. The Analysis Experts Step 1 of 3 screen appears (see Figure 14.2).

Figure 14.2. Use this screen to select the analysis tool you want to use.

Click Amortization Schedule in the list on the right. The title of the screen changes to indicate that there are only two steps when you use the Amortization Schedule tool. Click Next. The Analysis Experts Step 2 of 2 screen appears (see Figure 14.3).

Figure 14.3. Use this screen to set the parameters of your loan.

You can leave the Output Cells location set to cell A1; Quattro Pro will fill in the amortization schedule information starting at the location and using as much space as needed. If any data exists in the range Quattro Pro needs to use, Quattro Pro will ask if it can overwrite the data in those cells. To avoid the question and problem of losing data, I suggested earlier that you start in a blank spreadsheet.

1. Type the interest rate for your loan in the Interest Rate (%) box, and be sure to include the percent sign when you type the rate.

2. Type the length of the loan in years in the Term (years) box.

3. Type the amount of the loan in the Original box.

4. Leave zero (0) in the Ending Balance box.

5. In the Last Year box, type the same number you typed in the Term (years) box.

6. When you click Finish, Quattro Pro creates an amortization schedule like the one you saw in Figure 14.1.