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 order The 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 order The 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