Return to Index
Operations Research Models and Methods
Computation Section
 - Mortgage Model

The mortgage model builds a multiline structure that computes the payments associated with a mortgage. The model dialog accepts the loan amount, term in years and annual interest rate. The model assumes payments are monthly and the interest is paid each month on the principal balance.

  Pressing OK for this dialog, presents a second dialog. Press OK on this dialog to build the worksheet describing the mortgage payments. The worksheet contains one more line than the number of monthly payments. The model is interesting because there are no random aspects. The simulation formulas implement the standard annuity computations. The formulas in all the lines are identical except for the relative references. Only the first two years of payments are shown. The table has 61 rows.
  One interesting feature of this simulation is that it incorporates a column for extra payments. On the left we see parameters for the amount borrowed, the term in years and the interest per year. These can be changed and the dynamic simulation will change accordingly. The corresponding monthly term and interest are computed by formulas, as indicated by the yellow color of these cells. The payment amount is computed with an Excel Financial function. The extra payment value is a fixed amount that is entered in cell C28. The figure below shows the simulation with an extra payment of $50 per month. As indicated, the extra payments reduce the time of the last payment to 46 months. The total interest paid is also reduced.
  The means and standard deviations of the columns are not interesting in this case, and we have chosen not to show them. We do include other statistics in the third row of the statistics area. The formula in cell F12 computes the time of the last payment. The cells in row 12 of G, H, I and K compute the sums of the corresponding columns.
  The parameters of the model in column C that are not colored yellow can be changed and the spreadsheet values will adjust. The loan amount, term in years, the annual interest rate and the extra payment each month are not colored yellow, so these parameters can be changed. The yellow cells hold formulas. Although they can be changed, the formulas are lost. For example the monthly payment is computed by an Excel financial function.
Return to Top

tree roots

Operations Research Models and Methods
by Paul A. Jensen
Copyright 2004 - All rights reserved

Next Page