Assingments % - Real estate Excel sheet

Real estate assignment need and excel spread sheet.

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

This is a detailed assignment that requires creating a financial model to simulate cash flows for Commercial Mortgage-Backed Securities (CMBS). Let’s break this into manageable steps for clarity and outline what the Excel model should include.

1. Choose a CMBS Type

I suggest selecting Sequential Pay with three tranches with a Z-bond for this model. A Z-bond (or accrual bond) is a type of tranche that does not receive interest payments until other tranches are fully paid, which makes the model slightly more complex and interesting.

2. Excel Model Structure

The model will include the following key components:

Inputs Section

Tranche Information:
- Initial principal balance for each tranche.
- Interest rate for each tranche.
Loan Information:
- Total loan principal.
- Loan maturity in months.
- Loan interest rate.
- Servicing fee (fixed across tranches).
- Prepayment assumption (PSA or CPR).
- Current market interest rate (used for discounting cash flows).

Outputs Section

Monthly breakdown of:
- Cash flows (interest + principal) for each tranche.
- Remaining balances for each tranche.
- Prepayment amounts.
- Total interest paid by the loan pool.

3. Calculation Logic

The model will calculate the following:

a) Monthly Cash Flow on Underlying Loans

Total loan pool interest = $Rate12\text{Loan Principal} \times \frac{\text{Loan Interest Rate}}{12}$ .
Scheduled principal = Loan Principal ÷ Loan Term (in months).
Prepayments = Adjusted according to PSA/CPR.

b) Waterfall Allocation

Servicing fee is deducted first.
Remaining cash flow is allocated sequentially:
- Tranche A: Pays interest first, then principal.
- Tranche B: Pays interest first, then principal.
- Tranche Z: Accrues unpaid interest until A and B are fully paid.

c) Prepayment Impact

Prepayments reduce principal on Tranche A first, followed by B, then Z.

d) Z-Bond Accrual

Interest for the Z-bond accrues and is added to its principal until the senior tranches are retired.

e) Market Rate Adjustment

Optional step to calculate discounted cash flows using the current market interest rate.

4. Excel Layout

Here’s how the Excel file will be organized:

Sheet 1: Inputs

Clearly labeled input cells for:
- Loan pool details.
- Tranche details.
- Servicing fee, prepayment assumption, and market rate.

Sheet 2: Monthly Cash Flow Table

Columns:
- Month.
- Starting principal, interest, and principal repayment for each tranche.
- Prepayment allocation.
- Remaining principal balance for each tranche.

Sheet 3: Summary

Key metrics:
- Total interest and principal paid for each tranche.
- Payoff time for each tranche.
- Total prepayment amounts.