Now that we know how to allow for more robustness when it comes to early repayments and withdrawals, what about the interest rate. It is very rare that the reality of a debt schedule can allow for a single interest rate. Even if you have a fixed rate, at some point it may become variable or you may want to allow for a scenario where it changes.
To follow along go to 17.3
In this case we have enhanced our headers to include a monthly input of the interest rate to use that month (see row 49 below). For the interest charge calculation we no longer look at a static 12% but rather at that months interest charge. So in month 7 below you see that the interest rate changes from 12% to 11%.
Now when we calculate the PMT (monthly repayment), we point the rate to the new interest rate header. Note that the repayment amount changes once the interest rate changes in J53 and K53 but it correctly gets to the expected residual value. This is as a result of the new interest rate and the way we set up the loan to allow for the ad hoc withdrawals and prepayments.
For the PPMT and IPMT functions the same logic applies. Point to the interest rate header and Excel will recalculate the loan as if it is always the first month and with the different interest rate.
By combining these methods you should be able to allow for robustness and flexibility of your loan amortisation schedules.