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 = Loan Principal×Loan 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

  1. Servicing fee is deducted first.
  2. 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.
Posted in Uncategorized

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount