amortization schedule extra payments excel

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.

Modifying that approach to take account of a constant extra payment is possible for an advanced formula jockey. Rearranging that formula yields the level payment amount paid each period:įinding the principal and interest amounts for any given payment within that stream is possible using a geometric progression, as described on Wikipedia. PPMT calculates the principal portion of any given numbered payment for a fixed rate loan.Įach of those functions relies on one of the most common formulas in use in finance: the present value of a fixed annuity. For one thing, Excel provides three very useful functions for the task: As a result of that discussion, I started tinkering with my old approach to overcome its weaknesses.įor those who simply cannot wait to get their hands dirty, here is a link to the sample file:įor 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.Ībsent extra principal payments, building an amortization table is very easy to do. For example, it would have returned a payment amount as $877.5715701, typically formatted to $877.57.Īfter 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. It treated the return values as true floating-point values, and did not round the results as appropriate for financial transactions.

Amortization schedule extra payments excel