I would appreciate working in Excel using formulas (not written steps) so that I can learn how to do this problem.

Thanks,

For this assignment, I want you to calculate the return on a potential investment in a condominium project. You expect that it will generate 1.6 million in rental income each year, and that revenue will increase by 4% each year. The property will have operating expenses of 40% in the first year, and that expense rate will increase by 2%(42%,44%, etc.) each year. You anticipate holding on to the property for 5 years, as you think the market will become saturated after that point. It is typical for this type of property to have 5% vacancy at any given time. Right now, similar properties are selling for a capitalization rate of 10%. At the end of five years, you expect that the terminal capitalization rate will be lower at 9%. You are hoping to finance the project by using 3,000,000 in equity from your limited partnership, and the rest will come from a bank loan that will have an interest rate of 6%, with annual payments over 30 years. The property will be depreciated over 40 years, although the land is worth $1,200,000. You have an average tax rate of 30%. With this information, create an Excel spreadsheet to answer the following questions so I can see how you arrived at your answers. 1) What is the unlevered project's IRR? 2) Using the formula from lecture 4, what is the projected equity IRR if you take out the loan? 3) What is the debt coverage ratio for this project? 4) After calculating the before tax equity IRR, what is the actual value? 5) Using the formula to predict the after-tax equity IRR, what do you expect it to be? 6) What is the actual value of the after-tax equity IRR? 7) What is a good estimate for when the project will being to have negative financial leverage