MBS_Tabular() function |

**MBS_Tabular() function**

**MBS_Tabular(argument
list…)**

This function values a mortgage security using Monte Carlo simulation on a Hull-White interest rate tree, with a user-defined prepayment model (via a 2-dimensional lookup table). Customized versions of this function can be created on demand, if one were to provide a suitable prepayment function.

The function returns a 1 row by 12 columns ARRAY (see section on Array Formulas in this manual). The return values consist of estimated security price, standard error of the estimate, and option-adjusted duration (in years) for the total security, and for the PO, IO, and PIP components; the values are in the following order:

1. total MBS price

2. total MBS standard error of price

3. total MBS duration

4. Principal-Only price

5. Principal-Only standard error of
price

6. Principal-Only duration

7. Interest-Only price

8. Interest-Only standard error of price

9. Interest-Only duration

10. Penalty-Interest-Payments-only price

11. Penalty-Interest-Payments-only
standard error of price

12. Penalty-Interest-Payments-only
duration

Suppose that the price estimate was $99.05, and the standard deviation of the estimate was $0.05. This means that 19 times out of 20, the "true price" (according to all the model's assumptions and inputs) will lie between two standard deviations of the estimate, i.e. between $99.85 and $99.15. The number of simulations can be increased in order to improve the precision of the estimate (albeit with a commensurate increase in computational time).

Note that the returned values are in all cases DIRTY prices (i.e. inclusive of any accrued interest, as of the valuation date). Clean prices can be calculated by making an adjustment for accrued interest, however the adjustment can sometimes be security specific, and time-of-month specific, so we've excluded that from this analysis).

The antithetic variate technique is used by default to maximize accuracy.

The function uses the following arguments (29 in total, which is the maximum allowed by Excel; MBS are indeed complex!):

Argument |
Description |
Restrictions |

VD |
the valuation date for the security (e.g. today); NB: the first element of the zero curve date arrays must be equal to this value | valid Excel date number |

MD |
maturity date of mortgage | valid Excel date
number >= VD |

FOD |
first "open" date of the mortgage (before this date, full economic prepayment penalties apply, which are based on the entire interest rate differential); if the first open date is past the maturity date, the mortgage is "closed" | none |

Age |
the age of the mortgage, in MONTHS, as of the valuation date | >= 0 |

Princ |
the remaining principal of the mortgage in dollars (we suggest that this value always be set at $100, in order to allow on to value the mortgage as a percentage of remaining principal more easily) | > 0 |

CleanUp |
if the remaining principal falls below this dollar level, the mortgage is assumed to pay off in full immediately | < Principal |

RAM |
the remaining amortization of the mortgage, measured in MONTHS from the valuation date (e.g. 20 years remaining amortization would be entered as 240) | > 0 |

Freq |
the payment frequency of the mortgage (number of payments per annum) | 1, 2, 4, or 12 |

Coupon |
the coupon rate on
the mortgage security in decimal form, e.g. 8% is
expressed as 0.08 (for MBS, this is often lower than the
rate on the underlying mortgages) NB: this rate must be specified as an annual rate, with a compounding frequency of Freq (e.g. 10% would
represent a 2.5% quarterly rate, if the frequency is 4)If the quoted coupon is semi-annual, and the mortgage pays monthly, the semi-annual rate must be converted to the equivalent rate which is compounded monthly. |
>= 0 |

Mort_Rate |
the weighted-average mortgage rate for the pool, expressed as an annualized rate, but with a compounding frequency of Freq (see note directly above, on "Coupon") | >= Coupon |

Age_VecX |
a row vector of mortgage ages (in months) for indexing into the prepayment matrix | ascending order |

C_R_VecY |
a column vector of refinancing incentives (defined as mortgage rate less the refinancing rate, both expressed on a semi-annual compounding frequency), in decimal form (e.g. 5% is expressed as 0.05) | ascending order |

PrepMatrix |
an array of annualized prepayment rates in decimal form (e.g. 20% is expressed as 0.20) | number of rows must equal dimension of C_R_VecY, and number of columns must equal dimension of Age_VecX |

PrepFact |
a multiplicative factor for the tabular prepayment (e.g. 1.0 would give the standard prepayment model, 0.0 would give no prepayments at all, 2.0 would give 200% of normal prepayments, etc.) | >= 0 (typically 1.0) |

PrepShift |
an additive shift
to the prepayments (in annualized form), which are added
AFTER the multiplicative factor above is used For example, 0.05 would add 5% to the annual prepayment rate for all times and refinancing incentives. |
none (typical 0.0) |

LiqPerc |
the percentage of
prepayments which are liquidations IN THE OPEN period (in
the closed period, we assume 100% partal prepayments) Typically, this value is 0.90 or so (90% liquidations). |
>= 0.0 <= 1.0 |

NumPIP |
the number of months worth of penalty interest for liquidations in the open period (e.g. 3 months is typical for Canada) | >= 0.0 |

ShortRateVol |
annual standard deviation of the short rate of interest, in decimal form (Hull-White model) | > 0 |

Rev_Rate |
mean reversion rate
of the short rate of interest, in decimal form
(Hull-White model) When set to zero, the Hull-White model reduces to the Ho-Lee [1986] model. |
>= 0 |

GovDates |
array of zero coupon curve dates for the government risk-free curve (i.e. zero credit risk) | strictly
ascending orderThe first date of this array must be Valuation_Date (VD) |

GovRates |
array of continuously compounded zero coupon rates in decimal form (e.g. six percent entered as 0.06) for the government risk-free curve | > 0 correspond to GovDates array |

GovShock |
a 4 by 1 vector, in the order OAS, Bucket_Start, Bucket_End, and Bucket_Shift which allow one to shock the risk-free curve (see any Hull-White function for the definitions of the OAS, Bucket_Start, Bucket_End, and Bucket_Shift requirements) | must have 4 elements |

CredDates |
array of zero coupon curve dates for the risk cost-of-credit curve; this curve should be ABOVE the Government curve, as it should reflect the credit risk of the underlying mortgage cash flows, as well as the liquidity of the security, transactions costs, and return on capital (profits) of the mortgage lender/investor | strictly
ascending orderThe first date of this array must be Valuation_Date (VD) |

CredRates |
array of continuously compounded zero coupon rates in decimal form (e.g. six percent entered as 0.06) for the risky curve | > 0 correspond to CredDates array |

CredShock |
a 4 by 1 vector, in the order OAS, Bucket_Start, Bucket_End, and Bucket_Shift which allow one to shock the risk-free curve (see any Hull-White function for the definitions of the OAS, Bucket_Start, Bucket_End, and Bucket_Shift requirements) | must have 4 elements |

MortTerms |
a vector of mortgage terms in months (e.g. 6, 12, 24, 36, 60) for which we will specify mortgage spreads over government par bond yields | a vector |

MortSpreads |
a vector of spreads
over the semi-annual government par rate for
mortgages of the corresponding tenor. These are used to
determine the relevant refinancing rates in future
scenarios. |
none (but should
normally be positive) The array must be of the same length as MortTerms. |

Steps |
the number of tree
steps per mortgage payment (typically 1 or 2,
although perhaps higher for very short term MBS, or for
ones with a low payment prequency) |
> 0 |

Sims |
the number of Monte Carlo simulations (increase this number to improve accuracy) | > 10 typically 1000 or more |

© 1995-98 Leap of Faith Research Inc.