real estate

1. Individual Project:

The final project will be due on November 22th, before the Thanksgiving break on Blackboard.

You have to choose one among the following types of CMBS securities:

• Sequential Pay with three tranches

• Sequential Pay with three tranches with a Z-bond

• Floater / Inverse Floater

• PAC + Support Tranche

• TAC + Support Tranche

After that, build an excel model that will compute cash flows towards each tranche. The model will feature the following:

a) Customizable inputs for the following:

• Initial principal in each tranche (summing to total principal on the underlying mortgages)

• Maturity of the underlying loans in months

• Interest rate of the underlying loans

• Interest rate to investors in each tranche

• Servicing fee paid by the investors (same for each tranche)

• Prepayment assumption (PSA or constant CPR)

• Current market rate

b) User will input all the criteria in a) and be able to see the following update automatically:

• Amortization table for the underlying pool (initial principle, scheduled payments, prepayments, scheduled principal, interest)

• Servicing fee of each tranche

• Cash flow of each tranche

• Investor valuation for the security

c) An additional tab should include the following, fully annotated graphs (title + labeled axis)

• Initial principal of each tranche over time

• Total payments to each tranche

The grades will be awarded based on the following criteria:

· Accuracy: the spreadsheet should compute accurate cash flows for any inputs of the parameters above. The spreadsheets must necessarily be dynamic.

· User-Friendliness: it should be obvious where one should enter the parameters above and where to look for cash flows towards each tranche. Clearly state what type of security you chose.

Note: This is an individual project and intended for you to learn how to build a spreadsheet yourself from scratch. The use of a pre-made template or collaboration of any kind is strictly prohibited. It would be treated as plagiarism and duly measures would be taken against that.

Excel Functions That Might be Useful:

I. ADDITION, SUBTRACTION, MULTIPLICATION, AND DIVISION LOGIC

II. SUM() AND AVERAGE() FUNCTIONS

III. TRUE AND FALSE LOGIC

IV. IF() FUNCTION

V. AND() & OR() FUNCTIONS

VI. MAX() AND MIN() FUNCTIONS

VII. COUNTIF() AND COUNTA() FUNCTIONS

VIII. IRR() AND NPV() FUNCTIONS

Additional Excel Features:

1. DATA VALIDATION: Data Validation restricts the type of data or the values that users enter into a cell. So for instance, with Data Validation you can limit inputs in a given cell to a specific type of value (e.g. whole numbers only). You can also restrict inputs to only predetermined values (e.g. a drop-down menu with a specific list).

2. CONDITIONAL FORMATTING: Conditional Formatting allows you to create a custom look for certain cells (or the entire model) based on user inputs.

3. DATA TABLE: The Data table feature is a feature in Excel that calculates multiple results (i.e. outputs) instantly. This feature is especially useful for scenario analysis in real estate, as well as displaying outputs in a dynamic way.

4. CHARTING TOOL: Charting tools allows you to visually present your outputs in a compelling (and dynamic) way.