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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |