Microsoft Office Tutorials and References
In Depth Information
Creating Amortization Schedules
Summary information
The user can now change the term; the interest rates; and the payments, which can and usually
will change the maturity date. For the summary information, you want to sum only the relevant
rows. The formula in C13 is
=SUMIF(\$G15:\$G374,”>=0”,C15:C374)
After the Balance in column G is zero, the amortization is complete. This SUMIF function sums
only those payments up until that point. This formula is copied across to the interest and
principal columns, and the absolute column reference ensures the new formulas still point to column G.
The schedule
With so many user changeable fields in the schedule, many of the formulas have to change to
account for different conditions. An amortization schedule has two kinds of user input data:
h Data that changes for one payment only
h Data that changes for all subsequent payments
When the interest rate changes for one payment, it changes for all subsequent payments — at
least, until it changes again. It doesn’t go back to the old rate. For that reason, the APR column
relies on the data directly above it. The formula in B15 pulls the starting interest rate from the
user input section. This formula, in B16 and copied down, simply repeats the previous month’s
rate:
=B15
This allows the user to enter a new rate when it changes and have that rate continue down until
it’s manually changed again. In this example, the bank informed you that the rate was reduced to
4.8% for the fifth payment (row 19). That rate was entered in B19, and all rates after that reflect
the change.
The payment date is an example of data that changes for one payment. If a payment is made
late, it doesn’t mean that all subsequent payments will be late. In this example, the third payment
(row 17) was made ten days late. This had no effect on the next month’s payment, which was
made on time. For this type of data, the increments need to be made against a base that doesn’t
change. The formula in A15 is
=DATE(YEAR(Loan_Date),MONTH(Loan_Date)+ROW()–14,DAY(Loan_Date))
This formula is copied down to all the rows. Unlike the previous example, it doesn’t rely on the
date above it. Rather, it uses the Loan_Date range as its base. Because the payments start in row
15, the current row less 14 is used to increment the month.

Search JabSto ::

Custom Search