Loan Modeling: Dynamic Interest Rates

[@Garyket]

After inputting the dynamic Interest rates and adjusting the formulas, why doesn't the Extra Payment feature work anymore?

[@docbrownexcels]

Hi Gary. With dynamic interest rates, the model calculates the payment each period based on the outstanding balance, number of periods remaining, and current interest rate. Without an Extra Payment and with a future rate change, that results in a constant payment until the rate changes, and then a different constant payment after the rate changes. When you add an Extra Payment, you are reducing the principal balance by more than expected, thereby reducing the required payment a little bit each period. That is why the payments go down a little each month, and then go down by a lot when the rate decreases. So the model is working, you just aren't getting the level payments that you get without the Extra Payment feature. That all said, you could adjust the model to only recalculate the payment when the interest rate changes. That would give the behavior I think you are expecting. The cost would be that when the interest rate changes could no longer be dynamic (cell C29). Those are trade-offs we often have to make when designing models.

[@Garyket]

@@docbrownexcels Thank you very much for your response. I seem to be stumbling a little on the formulas needed to make the adjustment. My goal, after adding dynamic interest rates, is to see how the extra payment decreases the Effective Loan Term just like it did in the prior video models. Even if I set the rate reduction to 0.00% (so that all rates remain at 4% just like the static interest rate we started with), the table still ends at period 240 instead of 214. If I add a $200 extra payment to each period, it only decreases the effective loan term by one month. I think I'm just not conceptually understanding. Would you be able to detail the steps I need to take to adjust the model for what I'm trying to do? Or is it not set up to do that? Thanks again! This series has been very helpful!

[@Garyket]

@@docbrownexcels Thank you very much for the response. I'm having a tough time adjusting the formula as you suggested, and I was hoping you can help explain exactly how to do that. What I'm trying to see is the amount of time the effective loan term would be reduced by if adding an extra payment AND after accounting for changing interest rates. I'm probably not conceptually understanding the calculations. In the model with a static interest rate, a $100 extra payment produces a 17.83 effective loan. However, after making the changes to incorporate the dynamic rates, the $100 extra payment still produces a 20 year effective loan term (19.92 if it's $200). This is if I change the rate reduction to 0%, effectively making it 4% the entire time just like it would have been if the rates weren't dynamic. Many thanks for making this series. It has been very helpful!

[@docbrownexcels]

@@Garyket Hi Gary, sorry for my slow reply. The issue you are running into is that the payment is recalculating every period. So, when you add an extra payment of $100 or $200, the model is using that to reduce every future payment by just a little bit, rather than paying the loan off sooner. The concept is that by taking in the current balance, current rate, and remaining loan term, the payment always adjusts to pay off at the end of the original loan term. What I think you want is a model that calculates the payments at the beginning, and again when the loan rate adjusts, and holds those payment amounts constant and then reduces the loan term as you make overpayments. To make that work, start with the initial loan schedule in the model with the changing interest rate. Then I would link the payments in that tab to the original tab that had the overpayment incorporated. A combination of those two tabs should work for you, but let me know if you run into any big stumbling blocks.