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. Some of these schedules are somewhat limited, in that they generally: * may not allow extra payments to principal (which would retire the loan faster) at all; * may allow extra payments, but the extra amount is assumed to be constant and is applied to every scheduled payment; * may not allow for a future value, such as for a loan with a "balloon" payment at the end; * may be too tightly tied to a paradigm of a 30-year loan with monthly payments, such as is the case with most mortgages originated in the United States; and/or * either do not truly round results to appropriate currency intervals (such as nearest cent for the USA), typically relying on number formatting to fix the display at the desired level of precision without affecting the underlying values. 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: h**p://filedb.experts-exchange.com/incoming/2010/07_w28/323285/Amortization-Schedule.xls reff : experts-exchange.com |
Posting Komentar