*Make a Pro Forma for the following question *

Based on the picture provided answer following

You will add three more tabs in the spreadsheet: one for each loan option. So, you will copy-and-paste the Scenario B pro forma to start, and then you will expand the three new pro formas to show your two loan proposal scenarios: Scenario C, Scenario D, and Scenario E.

Here's your idea for Scenario C: You get a 50%-LTV, fully amortizing, fixed-rate mortgage for 30 years. You anticipate an interest rate of 5%.

1. Using the information above, model EBTCFs, property value, property NPV, equity NPV, unlevered IRR, and levered IRR for Scenario C.

Heres your idea for Scenario D: You get a 90%-LTV, zero-amortizing, fixed-rate mortgage for 10 years. You anticipate an interest rate of 7.5%.

2. Using the information above, model EBTCFs, property value, property NPV, equity NPV, unlevered IRR, and levered IRR for Scenario D.

Heres your idea for Scenario E: You get a 70%-LTV, partially-amortizing, fixed-rate mortgage for 7 years with a $1 million balloon payment. You anticipate an interest rate of 6.75%.

3. Using the information above, model EBTCFs, property value, property NPV, equity NPV, unlevered IRR, and levered IRR for Scenario E.

4. Please rank the five scenarios based on everything youve learned in class. From your firms perspective, what is the best scenario, what is the second-best, etc? Please explain all the criteria you used to rank them. Demonstrate your understanding of all the concepts we discussed including underwriting standards, the business model of banking, risk-return tradeoffs, and primary vs. secondary markets.