Read your article on The True Cost of Borrowing We Don’t Care Much About. Saw your amortization table using straightline amortization. Can you also share your MS Excel calculator for diminishing balance amortization table?
Last month, we published an eye-opener-or-reminder-article for every Juan on the true cost of borrowing — how much interest we actually pay on our amortized loans (part reason why banks are really profitable, and why I like keeping bank stocks in my portfolio, but that’s another story.)
We also shared Excel formulas on how to create straight-line amortization table (equal amortization per month) in Excel for those who want to have their own easy-to-use calculators. The same app mentioned in the previous article also computed using straightline amortization.
A reader asked for the formula of diminishing balance amortization.
I checked since I wrote an article on this 2 years ago: On Loans: Straightline vs Diminishing Balance, to discuss the differences but I did not include the excel formulas.
So here it goes:
*For the month column (I), note that I just have a hidden +1 counter in column A which compares the counter to the tenor. Once the counter is greater than the tenor, month column will be blank.
Not all loan providers offer this option. But from time to time I encounter this option especially for mortgage / home loans. If Juan is presented such an option and s/he can afford the amortization using diminishing balance, s/he should go for it to save some more on interest payments.
Hope this helps!