File Name: Simulation – Debt Repayment and Amortization
Location: Modeling Toolkit | Risk Simulator | Debt Repayment and Amortization
Brief Description: Illustrates how to use Risk Simulator for simulating interest rates in a mortgage and amortization schedule to determine the potential savings on interest payments if additional payments are made each period and when the interest rates can become variable and unknown over time
Requirements: Modeling Toolkit, Risk Simulator
This is an amortization model examining a debt repayment schedule. In this example, we look at a 30-year mortgage with a portion of that period on a fixed interest rate and a subsequent period of variable rates with minimum and maximum caps. This model illustrates how the mortgage or debt is amortized and paid off over time, resulting in a final value of zero at the end of the debt’s maturity. Further, this model allows for some additional periodic (monthly, quarterly, semiannually, annually) payment, which will reduce the total amount of payments, reduce the total interest paid, and reduce the length of time it takes to pay off the loan. Notice that initially the principal paid off is low, but it increases over time. Because of this, the initial interest portion of the loan is high but decreases over time as the principal is paid off.
The required input parameters of the model are highlighted in boxes, and an assumption on the uncertain interest rate is set in cell D8, with a corresponding forecast cell at I12. By entering additional payments per period, you can significantly reduce the term of the mortgage (i.e., pay it off faster), and at a much lower total payment (you end up saving a lot on interest payments). A second forecast cell is set on J12 to find out the number of years it takes to pay off the loan if additional periodic payments are made.
You can either change the assumptions or keep the existing assumptions and run the simulation:
The resulting forecast charts (Figure 138.1) on the example inputs indicate that the least amount of money that can be saved by paying an additional $500 per month can potentially save the mortgage holder $136,985 (minimum), and at most $200,406 (maximum) given the assumed uncertainty fluctuations of interest rates during the variable rate period. The 90% confidence level can also be obtained, meaning that 90% of the time, the total interest saved is between $145,749 and $191,192.
In addition, the Payoff Year with Extra Payment forecast chart (Figure 138.1) shows that given the expected fluctuations of interest rates and the additional payments made per period, there is a 90% chance that the mortgage will be paid off between 20.9 and 21.8 years, with an average of 21.37 years (mean value). The quickest payoff is 20.67 years (minimum). If interest rates are on the high end, the payoff may take up to 22 years (maximum).
Figure 138.1: Interest paid and payoff year on long-term debt
