|
|
 |
Simulation |
 |
-
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. |
| |
|
|
