Due Friday by 11:59pm Points 175 Submitting a file upload Available Apr 2 at 3pm - Apr 22 at 11:59pm 20 days Please FULLY read these instructions and ask questions on anything you are unclear on. Each person's spreadsheet will be reviewed. Each person must develop their own spreadsheet. Duplicate/shared spreadsheets will result in a zero grade for both (all) people who have used the same spreadsheet. Scenario Doctors William and Joan Carpenter are considering the acquisition of a commercial property. The Carpenters would like to invest in the growing southwestern community in which they have settled. After acquiring an apartment complex in this neighborhood, they are convinced that there is a demand for a retail plaza. The area has been demonstrating strong economic growth in all sectors. It has a mixed population in terms of age and excellent public services, parks and schools. The median income is over $70,000. Because of corporate growth, many executives and managers move to the town and rent for a while before buying a house. After viewing many retail centers, the Carpenters have asked you to provide a complete cash flow analysis for a 60,000 square foot property, in the upscale neighborhood of Indian Trials. The three-year-old property is in excellent condition and it is unlikely that it will require any major repairs in the near future, however the Carpenters would like to create a capital account of 3.3% of EGI (above the line) as a reserve against future repairs. The Carpenters expect a 6.5% unlevered first year return from NOI on the purchase price. The Carpenters would like to know what price they should offer if their investment expectation is a levered 18% per year. They will purchase on the first day of year one and sell on the last day of year five. The appraisal indicates a ratio of 75% of the purchase price allocated to structures/improvements and 25% to land. Working with your mortgage broker sources, the Carpenters received a commitment for a loan for the lesser of 60% or a DSCR of 1.70. The loan will be a partially amortizing 25-year loan with a 7-year term, a 4.55 percent annual interest rate, and monthly payments as well as a 1.4% loan fee (to obtain a better interest rate) and $6,000 loan origination fee payable to the bank. Assume there are 2.4% of depreciable purchase price closing costs for the acquisition. The Carpenters have asked you to be conservative in your analysis. The Rent roll is as follows