*Make a Pro Forma for the following question*

Do all your calculations in Excel, and submit the final spreadsheet with all your work. Each cell must contain the correct formula in order for you to get credit. Financial calculators are not acceptable.

Based on the picture provided please answer the following

Now, they want you to consider financing options. They put you in touch with a mortgage

Your boss will need you to prepare a full pro forma to calculate the PBTCFs for each year of the investment.

broker, who finds two lenders who are willing to underwrite this investment: Lender A is offering a 75% LTV loan with a 6.5% interest rate and monthly payments of $27,679. At the end of year 5, it will have a remaining balance of $3,177,496.

Lender B is offering a 60% LTV loan with a 6.0% interest rate and monthly payments of $21,278. At the end of year 5, it will have a remaining balance of $2,521,518. You have been instructed to expand your pro forma. 1. Using the loan information above, please calculate the EBTCFs for both scenarios. The only constructive criticism you receive about your pro forma is that there was no justification for the discount rate. Your firm wants you to construct a more appropriate property discount rate and recalculate property value. They inform you that their investors (i.e. the equity investors) expect to receive at least a 12% return on their investments.

2. Based on this information... a. ...what is the property value in each scenario? b. ...what is the property NPV in each scenario? c. ...what is the equity NPV in each scenario? d. ...what is the unlevered IRR in each scenario? e. ...what is the levered IRR in each scenario? 3. Based on your calculations, which loan do you think is a better choice? Please explain why. Please remember: You are not allowed to use the PV, FV, or NPV functions in Excel for this homework assignment.