cash flow goes to 180. please include all excel formulas!

14. A real estate investor is comparing to debt structures for a property acquisition at a price of $5,000,000. Debt structure A is a single, senior mortgage loan with an LTV of 85% at 6% interest with monthly payments for 15 years and 1 discount point. Debt structure B is a combination of two loans: a senior FRM loan with an LTV of 60% at 4% interest with monthly payments over 15 years and 1.5 points and a second, junior FRM loan with an LTV of 25% at 19.5% interest with monthly payments over 15 years. What is the borrower's effective borrowing cost under each of the 2 debt structures assuming all loans are held to their respective maturities. Use the RATE function for debt structure A 3 and the IRR function for debt structure B. Use the IF function to indicate which debt structure the borrower would 4 prefer. 3 6 Yield Debt Structure A 7 Purchase Price 8 LTV 9 Loan Amount B0 Lender's Cash Flow 0 31 Interest Rate 32 Maturity in Years 33 Periods per Year 54 Discount Points 85 Senior Loan Payment Preferred Debt Structure (A or B) Yield Debt Structure B Purchase Price LTV Loan Amount Lender's Cash Flow 0 2 Interest Rate 3 Maturity in Years 4 Periods per Year 5 Discount Points 6 Senior Loan Payment -7 8 Purchase Price 09 LTV 0 Loan Amount 1 Lender's Cash Flow 0 2 Interest Rate 3 Maturity in Years 34 Periods per Year 05 Junior Loan Payment st Rate ty in Years Is per Year Loan Payment Cash Flow Month 0 1 2 3 4 5 6 7 8 9 10 11 12 12