Please show excel functions -

Consider the following loan information. - Total acquisition price: $3,000,000. - Property consists of twelve office suites, five on the first floor and seven on the second. - Contract rents: three suites at $2,400 per month, two at $3,000 per month, and seven at - $1,330 per month. - Annual market rent increases: 3% per year. - Vacancy and collection losses: 5% per year. - Operating expenses: 35% of effective gross income each year. - Capital expenditures: 6% of effective gross income each year. - Expected holding period: 5 years. - Expected selling price in year 5: Year 6 NOI capitalized at 5\%. - Selling expenses in year 5:3% of the sale price. - First mortgage loan: 75% LTV. - Annual mortgage interest rate: 4.5%. - Loan term and amortization period: 25 years (monthly payments). - Total up-front financing costs: 2% of loan amount. - Required return: 9% 1. Create a one-year operating pro forma and calculate investment criteria (cap rate, equity dividend rate, net income multiplier, effective gross income multiplier, debt coverage ratio, debt yield ratio, and loan-tovalue ratio). 2. Create a five-year operating pro forma up to BTCF and BTER. 3. Calculate the NPV and IRR of the project based on the 5-year levered cash flows. 4. (Sensitivity Test) Calculate the project NPV of levered cash flows based on the following required return assumptions: 10%,12%,14%,16%,18%, and 20%. 5. (Extra Credit 5pts) Calculate the project NPV of levered cash flows when LTV is 90% (instead of 75% ). How does it change the NPV and IRR? Sale price (SP) - Selling Expenses (SE) = Net Sale Proceeds (NSP) - Remaining mortgage Balance (RMB) = Before-tax equity reversion (BTER) Cash Flows NPV @ 9\% Consider the following loan information. - Total acquisition price: $3,000,000. - Property consists of twelve office suites, five on the first floor and seven on the second. - Contract rents: three suites at $2,400 per month, two at $3,000 per month, and seven at - $1,330 per month. - Annual market rent increases: 3% per year. - Vacancy and collection losses: 5% per year. - Operating expenses: 35% of effective gross income each year. - Capital expenditures: 6% of effective gross income each year. - Expected holding period: 5 years. - Expected selling price in year 5: Year 6 NOI capitalized at 5\%. - Selling expenses in year 5:3% of the sale price. - First mortgage loan: 75% LTV. - Annual mortgage interest rate: 4.5%. - Loan term and amortization period: 25 years (monthly payments). - Total up-front financing costs: 2% of loan amount. - Required return: 9% 1. Create a one-year operating pro forma and calculate investment criteria (cap rate, equity dividend rate, net income multiplier, effective gross income multiplier, debt coverage ratio, debt yield ratio, and loan-tovalue ratio). 2. Create a five-year operating pro forma up to BTCF and BTER. 3. Calculate the NPV and IRR of the project based on the 5-year levered cash flows. 4. (Sensitivity Test) Calculate the project NPV of levered cash flows based on the following required return assumptions: 10%,12%,14%,16%,18%, and 20%. 5. (Extra Credit 5pts) Calculate the project NPV of levered cash flows when LTV is 90% (instead of 75% ). How does it change the NPV and IRR? Sale price (SP) - Selling Expenses (SE) = Net Sale Proceeds (NSP) - Remaining mortgage Balance (RMB) = Before-tax equity reversion (BTER) Cash Flows NPV @ 9\%