- Experts Exchange Approved
Using Microsoft Excel to create a fixed rate loan amortization schedule is a very common task: probably just about every skilled Excel user who has ever taken out a mortgage has either created such a schedule him/herself or downloaded one of the hundreds (thousands?) of such schedules available online.
Several years ago I made my own attempt at a loan amortization schedule, available in my VBAExpress article Functions to return a loan amortization schedule in an array. This example had three of the weaknesses described above:
It allowed extra payments, but the extra amount was constant and applied to every scheduled payment.
It did not allow for a future value, or “balloon” amount, at the end.
It treated the return values as true floating-point values, and did not round the results as appropriate for financial transactions. For example, it would have returned a payment amount as $877.5715701, typically formatted to $877.57.
After reading mwvisa1's excellent article A Guide to the PMT, FV, IPMT and PPMT Functions. mwvisa1, brettdj. and I had a spirited discussion about how extra payments affect a loan amortization schedule and the components of each payment. As a result of that discussion, I started tinkering with my old approach to overcome its weaknesses.
For those who simply cannot wait to get their hands dirty, here is a link to the sample file:
For others who wish to know how that file came to be, and how to use it (and the VBA function that powers it), please read on.
The Difficulty: Handling Extra Payments
Absent extra principal payments, building an amortization table is very easy to
do. For one thing, Excel provides three very useful functions for the task:
IPMT calculates the interest portion of any given numbered payment for a fixed rate loan;
PPMT calculates the principal portion of any given numbered payment for a fixed rate loan.
Each of those functions relies on one of the most common formulas in use in finance: the present value of a fixed annuity.
Rearranging that formula yields the level payment amount paid each period:
Finding the principal and interest amounts for any given payment within that stream is possible using a geometric progression, as described on Wikipedia. Modifying that approach to take account of a constant extra payment is possible for an advanced formula jockey.
However, modifying it to take account of potentially multiple streams of extra payments, which may begin and end at arbitrary points in the overall schedule is rather too much to ask, and so I pass that task off to Visual Basic for Applications (VBA) code. Doing this makes it much, much easier to accommodate a variety of extra-payment schemes within the amortization schedule, and also allows for proper handling of rounding, which just is not possible with a purely mathematical approach.
AmortSchedTraditional User Defined Function
To allow maximum flexibility for amortization schedules with additional payments made to principal — whether flat over the course of the loan, or periodic — I developed the AmortSchedTraditional function. AmortSchedTraditional returns a two-dimensional array with a detailed payment schedule.
The first dimension, “vertical”, indexed by 1 to N, enumerates the actual number of payments made to retire the loan. Note that if extra payments are made, this number may be less than the initial loan term specified.