Finance
Build to Suit for Convenient Mart Over the past 7 years you have developed a dozen of build to suit facilities for Convenient Mart. These properties have all been located along the main thoroughfares of suburban Des Moines. The senior managements of Convenient Mart have approached you, and asked you to play a major development role in their expansion effort. Their proposed new store site is along the Highway 35 in Ankeny. It is for a 10,000 sf of gross leasable area (GLA) store, with appropriate parking, ingress/egress, and signage requirements. Convenient Mart’s real estate committee has approved the following non-negotiable deal terms: 5 year lease since certification of occupancy (i.e., upon completion of the construction) Certificate of Occupancy must be in place at the end of 12 months Convenient Mart agrees to purchase the property at $1,200,000 when the current lease expires. There is no broker needed. We assume the transaction will be closed on the last day of the lease. Annual rent for the first years of operation is $8/sf, and it grows at 3%/year If there is any delay in construction and Certificate of Occupancy is not in place at the end of 12 months, initial rent will be $6/sf, and it grows at 2.5%/year. Furthermore, in this case, Convenient Mart will purchase the property at $1,000,000 when the current lease expires. The lease is triple net, which means all operating expenses will be paid by Convenient Mart. Hence we can ignore all operating expenses from this analysis. Also, because Convenient Mart is the only tenant, there will be no vacancy. As Convenient Mart signs a triple-net lease, your total rental revenue can automatically be thought of as the net operating income (NOI). In addition, you also know: Your company’s marginal income tax rate is 35%, capital gain tax upon property liquidation is 25%. Other key parameters: o Land cost: $120,000; Soft cost (loan fees and construction loan interest have not been included ): $50,000; Hard cost: $400,000 o Total project cost= Land cost+ Soft cost+ Hard cost+ loan fees+ construction loan interest Depreciation rule: Assume that the total depreciable cost is the sum of hard cost, soft cost and accumulated construction loan interest. Out of the total depreciable cost, 85% is for capital improvement and is subject to 30 year straight line depreciation rule; and 15% is for tenant improvement and is subject to 10 year straight line depreciation rule. You have secured a 1-year construction loan which allows you to draw $440,000. Out of it, 60% will be drawn evenly from month 1 to 6, and remaining 40% will be drawn evenly from month 7 to 12. The construction loan rate is 5% compounded monthly. There is NO fees for getting the construction loan. You have also secured a permanent takeout loan, which becomes effective when the Certificate of Occupancy is issued. It is 20 year, interest only loan. The interest rate is 6% compounded monthly. It has 2 points loan fee (recall: 1 point means 1%), which is due today. (Note: we discussed the interest only loan in chapter 4. Also see https://www.mtgprofessor.com/Tutorials2/interest_only.htm for further explanation.) The loan fee depreciation, when applicable, shall be straight line over the term of the loan; and depreciation starts when the relevant loan becomes effective. If you encounter a negative taxable income, simply assume income tax for that period is ZERO and move on, and DO NOT carry the loss over to the future. (Unrealistic assumption, only to simplify the test and also to test whether you pay attention to the case instruction; and there is NO implication that, if you have done everything correctly, you should see a negative taxable income somewhere in your pro-forma). Your tasks: 1. Fill in Box A, which is the input box (10 points) 2. Fill in Box B. (10 points) Here you need to project the loan repayment schedule for your construction loan (i.e., something similar to Exhibit 16-5 on Page 539 of your textbook). Here is the snapshot of Exhibit 16-5 from the book: The full exhibit on your book has additional columns, but it is enough for you to develop up to column c for this midterm. When you finish Box B, for the next two rows, answer what is the total interest, and final balance of your construction loan? (Remember that the final balance of your construction loan will also be the loan amount for your permanent take out loan) 3. Fill in Box C, which tracks the cash flows up to the completion of construction. (10 points) 4. Fill in Box D, which gives you both before and after tax cash flow due to operation. (15 points) 5. Fill in Box E, which is about before and after tax cash flow when you sell the property when the lease expires. (10 points) 6. Fill in Box F, and calculate both before tax IRR and after tax IRR. (15 points) 7. Assuming your company has a required rate of return at 35%, after tax. What is the after tax Net Present Value (NPV) of this project? Please use the NPV function in excel to answer this question. (10 points) Note: use NPV in excel with caution! NPV in excel is defined in a nontraditional way (something to do with time zero cash flow treatment) and often causes confusion. We discussed it in class, and this link also discusses how to use it properly. https://www.propertymetrics.com/blog/2014/09/30/how-not-to-use-npv-in- excel/ 8. Using the data table feature in excel, conduct a sensitivity analysis of your before tax IRR on rental growth rate. Consider growth rates at 1%, 2%, 3%, 4% and 5%. You can go to http://www.excel-easy.com/examples/data- tables.html for a demonstration on how to use the data table feature in excel. (10 points) 9. Suppose the Certificate of Occupancy is delayed by ONE hour (recall that the original contract requires Certificate of Occupancy must be in place at the end of 12 months). Although practically it doesn’t affect any operation, it triggers the penalty clause of the contract legally, which says “If the Certificate of Occupancy is not in place within 12 months, initial rent will be $6/sf, and it grows at 2.5%/year. Furthermore, in this case, Convenient Mart will purchase the property at $1,000,000 when the current lease expires.” Due to the penalty clause triggered by delay, what is going to be the before and after tax IRR? (10 points) For question 9, you MUST prepare a separate worksheet to answer this question. Please open a new tab (in the same excel file) to complete this question, do not create another excel file. The purpose of question 9 is to let you see how scenario analysis can be done in practice. (Hint: basically, what you need to do is to copy and paste the developed pro forma from the main case to a new worksheet, and change some parameters as needed). For question 9, you don’t need to perform the data table analysis as previously required by question 8.