Amortization Schedule Software

To find out the results through the amortization schedule software enter the values 1 through 120 in range A15..A134, which will allow the worksheet to accommodate monthly payments for as many as 10 years. Select /Data Fill (in Symphony, MENU Range Fill), specify range A15..A134 as the Fill range, enter 1 as the start value, and press Return twice to accept the default step and stop values. To save space, many of the worksheet's rows are not displayed in figure 1. Assign the General format to the ascending values in column A: Select /Range Forma General (in Symphony, MENU Format General) and specify range A15..A134. Assign the same format to cell B4 and to range D8..D9 Format cell D 10 for percent with one decimal place: Select /Range Format Percent (in Symphony, MENU Format %), enter 1, and specify cell D10. Format cell D7 and range B15..B134 for currency with no decimal places. Then enter the following formulas (all the formulas will initially return ERR): Cell Formula D11 @ROUND@PMT(D7,D10/D9,D8*D9)/ (1 + D10/D9*B4),2) B15 @MAX@PV($ D$ 11,$ D$ 10/$ D$ 9, $ D$ 8*$ D$ 9 - A15),0) C15 @IF(B4,D11,D7 - B15) C16 + B15 - B16 D15 (D$ 11 - C15)*(A15<=(D$ 8*D$ 9))

Copy cell B15 to range B16..B134, copy cell C16 to range C17..C134, and then copy cell D15 to range D16..D134. Now save the worksheet and use a file name such as AMORT.

Using the template You'll need five pieces of information to produce an amortization schedule: the amount of the loan, the term of the loan, the number of payments per year, the annual interest rate, and the payment basis--ordinary-annuity or annuity-due basis. The payment basis refers to when the payments are due. For an ordinary-annuity loan, payments are due at the end of each payment period. For an annuity-due loan, payments are due at the beginning of each payment period.

The @PMT function returns the payment amount for an annuity based on an ordinary-annuity loan. Our formula adjusts this @function so that it returns the payment amount for annuity-due loans by dividing its result by the sum of 1 plus the interest rate. The formula in cell D11 uses the @PMT function and the value you enter in cell B4 to return the correct payment amount for the loan type. To produce an amortization schedule for almost any loan, retrieve the worksheet, enter the five pieces of information in the appropriate cells, and press the CALC key. Starting at cell B15 are three columns of formulas that compute each payment-period's balance and the portions of each payment applied to the principal and interest.

Assume that you manage the books for Ubermilch Dairy. The owner of the dairy has decided to acquire a new delivery truck costing $ 19,000 and to finance 80% of the purchase price. He has obtained a three-year loan for $ 15,200 with a 10.9% annual interest rate and payments due at the end of each month.With the amortization worksheet on your display screen, enter Milk Delivery Truck in cell B3 and 0 in cell B4 (since payment at the end of each period constitutes an ordinary-annuity loan). Enter the values shown in range D7..D10 of figure 1, then press the CALC key.