Microsoft Office Tutorials and References
In Depth Information
Creating Amortization Schedules
Nothing in the schedule can be updated by the user. Changes to the amortization table must be
made in the input cells in column B or in one of the three tables to the right of that. The following
sections discuss the new formulas in the schedule. Formulas not listed have not changed from
the previous example.
Date
This formula looks a little daunting, but it’s not too bad. It starts with the same DATE function
used in the preceding example and adds the number of late days from tblLate. The VLOOKUP
function looks for an exact match in the first column of tblDate; the number in the second
column, either plus or minus, is added to the originally computed date. The IFERROR function is
used to return a zero if no match is found, meaning the originally computed date is used.
=DATE(YEAR(Loan_Date),MONTH(Loan_Date)+ROW()–14,
DAY(Loan_Date))+IFERROR(VLOOKUP(DATE(YEAR(Loan_Date),
MONTH(Loan_Date)+ROW()–14,DAY(Loan_Date)),tblLate,2,FALSE),0)
APR
The table tblRate contains a list of interest rate changes. The VLOOKUP function is used with an
omitted fourth argument so that the rate change persists until it is changed again. This means
that the dates in tblRate must be sorted.
The IFERROR statement returns the starting rate if no value is found in tblRate.
=IFERROR(VLOOKUP(A15,tblRate,2),Rate)
The table tblAdd is a listing of additional payments, the date they become effective, and the date
they expire. To add a one-time additional payment, the user can make the start and end dates
the same. To schedule a series of additional payments, however, this method allows the user to
where the current payment date is in between the start and end dates. That means that more