G H M A B C D E K 4 5 Potential gross income $442,000.00 $454,643.75 $467,667.34 $487,979.00 $501,971.23 $516,384.97 6 Less: Vacancy & Collection $22,100.00 $22,732.19 $23,383.37 $24,398.95 $25,098.56 $25,819.25 7 Effective gross income $419,900.00 1,911.56 $444,283.98 $463,580.05 $476,872.67 $490,565.72 8 Potential expense reimbursement 10 Common area maintenance $5,233.41 $5,233.41 $5,233.41 $5,233.41 $5,233.41 $5,233.41 Property tax expense $47,100.73 $47,100.73 $47,100.73 $47,100.73 $47,100.73 $47,100.73 11 12 Property insurance $6,541.77 $6,541.77 $6,541.77 $6,541.77 $6,541.77 $6,541.77 13 Total $58,875.91 $58,875.91 $58,875.91 $58,875.91 $58,875.91 $58,875.91 14 15 Less: Vacancy & Collection $2,943.80 $2,943.80 $2,943.80 $2,943.80 $2,943.80 $2,943.80 16 Net expense reimbursement $55,932.11 $55,932.11 $55,932.11 $55,932.11 $55,932.11 $55,932.11 17 $487,843.67 $500,216.09 $519,512.16 $546,497.84 18 Total operating receipts $475,832.11 $532,804.78 19 20 Less Expenses Paid By Landlord Common area maintenance $5,233.41 $5,233.41 $5,233.41 $5,233.41 $5,233.41 $5,233.41 21 22 $47,100.73 $47,100.73 $47,100.73 $47,100.73 $47,100.73 $47,100.73 Property tax expense 23 Property insurance $6,541.77 $6,541.77 $6,541.77 $6,541.77 $6,541.77 $6,541.77 24 25 Management expense $23, 791.61 $24,392.18 $25,010.80 $25,975.61 $26,640.24 $27,324.89 26 Replacement reserves $5,000.00 $5,000.00 $5,000.00 $5,000.00 $5,000.00 $5,000.00 27 28 Total operating expense $87,667.52 $88,268.09 $88,886.71 $89,851.52 $90,516.15 $91,200.80 29 30 Net Operating Income $388, 164.59 $399,575.58 $411,329.37 $429,660.64 $442,288.63 $455,297.03 31 32 Less: $0.00 $0.00 $12,926.94 33 Leasing commissions $0.0 $0.00 $9,701.95 34 $22,082.25 Tenant improvements $0.00 $0.00 $0.00 $19,403.90 $0.00 35 Total non-operating expense $0.00 $0.00 $0.00 $29,105.86 $0.0 $35,009.19 36 37 38 $420,287.85 39 NOI After Capitalized Items $388, 164.59 $399,575.58 $411,329.37 $400,554.79 $442,288.63 40 41 Less: Maximum mortgage loan pmt 42 43 Before-tax cash flow $120,041.35 44 45 46 47 48 49 50 52 53 54113 A B C D E F G H 1 Year 1 2 3 4 5 6 3 Beginning Mortgage Balance $3,241,774.48 $3,199,336.27 $3,153,375.72 $3,103,600.46 $3,049,693.88 $2,991,313.08 4 Total Mortgage Payment $300,246.49 $300,246.49 $300,246.49 $300,246.49 $300,246.49 $300,246.49 5 Interest $257,808.29 $254,285.94 $250,471.24 $246,339.92 $241,865.70 $237,020.12 6 Principle Amortization $42,438.20 $45,960.55 $49,775.26 $53,906.58 $58,380.80 $63,226.38 7 Ending Mortgage Balance $3,199,336.27 $3,153,375.72 $3,103,600.46 $3,049,693.88 $2,991,313.08 $2,928,086.71 8 g 10 11A B C D E F G H 7 Unlevered Project 00 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 9 (Purchase)/Sales Price $4,600,896.34 10 Net Operating Income $436, 136.16 11 Non-Operating Expenses $0.00 12 BTCF $5,037,032.50 13 14 15 NPV of "free & clear" return 16 Actual Un-levered IRR 10.36% 17 18 19 20 Levered Project 21 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 22 (Purchase)/ Sales Price $4,600,896.34 23 Net Operating Income $436, 136.16 24 Non-Operating Expenses $0.00 25 Debt Service $300,246.49 26 Mortgage $2,991,313.08 27 BTCF $1,745,472.92 28 29 30 NPV Levered Return 31 Levered IRR 16.42% 32 33 34 35A B C D E F 1 Maximum Loan Calculations W N Economic Value Approach Economic Value 291123.4459 6 Maximum Loan to Value Loan 4322365.972 3241774.479 8 Construction Cost Approach 10 Cost of Construction 11 Maximum Loan to Cost Loan 4328102.1 12 3246076.575 13 14 Debt Service Coverage Approach 15 Debt Service 16 Maximum Debt Service Coverage Ratio Loan 311210.35 17 3322100.834 18 19 Loan Used 20 Maximum Acceptable Loan Value 21 Annual Mortgage Loan Payment 3241774.479 22 12 pmts per year 300246.4947 23 300246.4947 25020.54123 24 25 26 27 28 29 30 31 32 33 34 35 36C9 1 X B C D E F G H K L N O P Q S T 1 BUILDING COMPONENT COST/SQ FT COST ESTIMATE 2 Land (1.0 acre) 6.3769 1300000 Rock Cinco Shopping Center Analysis, Parts 2 & 3 3 Building Shell (22300 sq ft) 75 1672500 4 Parking Area and Landscaping 2.5 453902 5 Tenant Finish 12 267600 6 Soft Costs and Constuction Interest 2.099471796 428000 Part 2 - Determining the Cost of Construction and the Amount that May be 7 Entrpreneurial Profit 0.05 206100.1 Borrowed 8 9 Total Cost First the seller of the land is firm at $1.3 million for the 4.68 acres he needs to construct 10 Total Cost of Land plus Improvements 4328102 1 Total Cost of Land 0000 the project. Rock has a buddy, Ernie Ernest who is a TAMU construction science major + 12 Total Cost of Improvements 3028102 who provided a "rough" estimate of what costs are to build a shopping center in Houston. 13 14 Building Component Cost Estimate 15 Land (1 acre or 43,560 square feet) $1,300,000 or $6.3769 PSF of land Building (shell) $75.00 PSF of building area Parking Area and Landscape $ 2.50 PSF of land less building area Tenant Finish (Tenant Improvements $12.00 PSF of building area (TIs)) Soft Costs and Construction Interest $428,000 Entrepreneurial Profit 5% of the above costs Having never developed a property before, Rock was a little confused by this list. Ernie explained that in construction lingo, the building shell term refers to the outside walls, floors and roof while Tenant Improvements refer to the construction items inside the tenant's space. He went on to explain that sometimes the landlord pays for all or part of the TIs and sometimes the tenant pays. Rock was also confused about the entrepreneurial profit until Ernie explained that the contractor needs to make money too. To make matters worse, sometimes the developer puts is a little profit as well, but Rock, being a simple guy, decided not to add a profit for himself. Soft costs refer to expenses such as architectural, legal, permits and interest - generally services that are not per se part of the building or land. Finally, in order not to embarrass himself further, Rock "googled" the term acre to discover that there are 43,560 in an acre. Now he had enough information to create a preliminary construction budget. Next Rock went to The Last Bank of Snook to see about borrowing money. His banker, Lucy Linder promised him a construction loan if he could secure a permanent financing commitment. He needed to get a quote from a long-term lender. She suggested he speak with Louis Lender her husband who worked for the Life Insurance Company of Hearne (LICH) in their loan department. According to Louis, LICH required a debt service coverage ratio of 1.25x based on an 8%, 25-year loan. In addition, LICH will not lend more than 75% of market value based off the first year Net Operating Income. Because Life Insurance companies consider small shopping centers to be quite risky, they use a 9% cap rate (i.e. lender's capitalization rate) to value the property. Even so, Louis advised Rock that the "higher-ups" were not likely to approve a loan in excess of 75% of the total cost of the property. Rock was astonished to learn that he would have to figure out three possible loan amountsAssignment 2 '3' Determine what the total cost to build the shopping center will be based on Ernie1 s information. '3' Determine the 3 possible loan amounts and the maximum loan amount that the bank is willing to lend. Is it based off of the economic value or construction cost or is it possibly restricted by the Debt Coverage Ratio? '3' What will be the annual debt service payment? '3' Complete the earlier cash ow forecast by adding in the debt service and determining the before tax cash ow for years 16. Part 3 Discounted Cash Flow Analysis and Performance Measures Rock has decided that he does not want to contribute all of the equity required {25% of cost] so he is going to try to nd other investors. To do so, he needs to prepare a nancial feasibility analysis. He will use a 9.0% goingin cap rate. Over a game of pool at the Chicken, some of his rich buddies advise him that they require an 1 1% \"free and clear\" return on their real estate deals and a 15.5% before-tax levered return. For his analysis, he is assuming a 5year holding period {sale at end of year 5]. To estimate the residual value (or terminal value AKA sales price) at the end ofyear 5, he will use the income approach and capitalize proforma N01 for the sixth year assuming a capitalization rate of9.5%. Based on conversations with Suzie, he estimates that selling expenses will be 4% of the residual value. Assignment 3 Using your original cash flow spreadsheet, please start at least one new sheet Part 3 to develop a nancial analysis for these investors. '3' Using Rock's value estimate, determine the shopping center's NPV at the investors required \"free and clear \" return and determine the shopping center's actual \"free and clear\" return or unlevered IRR including nonoperating expenses. '3' Determine the required equity using the maximum loan available from the lender and determine the loan balance at end onear 5. NOTE: You may use various functions but setting up an amortization schedule is probably the best practice '2' Determine the NPV of the BTCF using the investor's required beforetax return on equity and determine the actual required rate of return on equity invested {levered IRR] Which would be better for investors: A market rental rate growth of 7% over 5 years or a decline in both initial and terminal capitalization rates of 150 basis points? For credit answers must include the impact on before tax IRR. HINT: This is not so straightforward