Northfork Development Co. plans to develop, own and operate, Rolling Meadows, a high-quality shopping center development. Northfork plans to use both interim and permanent financing and has approached the Citadel Life Insurance Company to provide permanent financing. You are provided an Excel Template-ASSIGNMENT 6 Retail Construction and Cash Flow Template to assist.

Tab I-Project Description. Contains all relevant information for the project and will serve as the source of information to populate the tabs below and complete the case study.

Tab II. Summary of Costs breaks down development costs into land acquisition costs, off-site costs, hard costs, and soft costs. These costs are also broken down as a percentage of total cost and cost per square foot of gross building area (GBA). The top of Tab II (lines 4 to 35) are a restatement of the information on Tab I (lines 37 to 69). Note the bolded Construction interest (line 62).

For the Construction Loan Request section (line 37), note that you have total costs and the amount of the construction loan. The difference is equity. You also have amounts for Total on/off site improvements, hard construction costs and the construction loan interest. The developer is only seeking a portion of total soft costs to be financed.

In summary, the total loan request is $9,001,416, which represents approximately 75 percent of the $11,982,287 estimate of total project cost (land plus all other outlays). Note that this particular loan request does not include land cost and also that the developer does not ask for financing for all soft costs. Also, note in Tab II. Summary of Costs. that the request includes construction period interest as part of the loan. The developer will be allowed to borrow the interest as a cost of construction. An estimate of construction period interest is provided on Tab I, line 62.

In Tab III. Loan Draw Schedule, the draw rates to be calculated in column (a) are determined by expected monthly draws for direct costs ($8,309,000) calculated from line 41 in Tab II. The draw amounts by month are determined from Tab I (lines 19 & 20). (Also note that the estimated interest cost is provided. This consists of interest calculated at 12% --- 12 months, or 1 percent per month on the cumulative loan balance shown in column (c). Interest draws are computed on the outstanding monthly loan balance and are borrowed as a part of the construction cost draws at the end of each month. However, because all of the interest carry is borrowed, it becomes part of the loan balance. Thus the net effect is as if there were no payments to the lender until the entire loan balance is repaid at the end of the construction period. You should prepare the schedule in Tab III. with the loan draws to verify the amount of the interest needed.

Cell G19 should be a negative of the construction loan amount, thus bringing Tab III to a -0- balance in Cell H19.

In summary, Tab III. Loan Draw Schedule will show that the loan balance will increase each month by the amount of the project cost draws plus interest borrowed. NOTE: the total ending balance will be equal to the total construction loan amount per Tab II, line 43 at the end of the 12-month period. This amount will be then funded by the permanent lender, thereby taking out the construction lender at that time.

Permanent Loan Request

Upon completion of the project, Citadel Life Insurance Company will replace the construction loan with permanent financing. The permanent loan terms are outlined in Tab I-Project Description, lines 28-30. For a 3 percent loan fee, Citadel Life Insurance Company will provide Northfork with a 10-year mortgage. Monthly payments will be based on a 25-year amortization schedule at an interest rate of 12 percent. All loan fees are paid upfront or in Year 0.

Tab IV. Construction Cash Flows contains annual estimates for expenditures during the construction period for land acquisition, site improvements, hard costs, and soft costs. Year 0 expenditures include ONLY the land acquisition, permanent loan fee and construction loan fee.

Year 1 costs will be the difference between total costs (Tab II, Line 45) and the amount calculated for Year 0. Note that will require a small amount of equity in Year 1 to balance the schedule in Cell D13.

Total loan draws are based on the $9,001,416 loan request (including interest), for which financing is being sought, over the two-year development period. You will calculate interest during the construction period which will populate cell D10 which will again verify the construction loan interest estimate.

Operational Data

Tab V. Cash Flow Assumptions contains Rental and operating expense data including sales assumptions. Rental operations begin in Year 2. Assume 70 percent occupancy during Year 2 and 95 percent thereafter. Northfork is estimating a base rent of $15 per square foot of gross leasable area, with average increases based on leases indexed to the CPI at 6 percent per year after the first year of operation (leases are expected to have terms ranging from one to five years). An overage provision requires all tenants to also pay 5 percent of gross sales in excess of a base sales level each month.

All leases are to be net to the landlord, with a direct pass-through for insurance and property taxes. Tenants will also be billed for their share of common area maintenance (parking lot, circulation space in center, etc.) and utilities. An additional premium (22.4%) will be added to the utility charge to provide for a replacement reserve on HVAC equipment. Thus tenants will reimburse Northfork for 122.4% of the utility expenses. Northfork will also incur expenses of its own for property management, leasing commissions, and general and administrative expenses that will not be recoverable from tenants. Assume that the vacancy and collection loss of 5 percent is primarily due to vacancy.

Upon selling the property, be sure to include the full repayment of the permanent loan prior to cash distributions to Northfork.

REQUIREMENTS:

Formats have been provided in the Excel spreadsheet. Compute the following:

1. Verify the amount of interest incurred during the construction period.
2. Prepare a proforma cash flow.
3. Compute the following ratios using Column F of Tab VI Cash Flows :

• Annual debt service coverage (NOI divided by debt service)
• Annual investment yield (Cash flow divided by total project cost)
• Annual return on equity (Cash Flow divided by Total Equity Needed (Tab IV, E13)

1. Internal rate of return

A. Site and Proposed improvements \begin{tabular}{|l|c|} \hline Site area (in acres) & 9.5 acres \\ \hline Gross buildable area (GBA) sq. ft. & 120,000 sq. ft. \\ \hline Gross leasable area (GLA) sq. ft. & 110,000 sq. ft. \\ \hline Percent leasable area & 91.67% \\ \hline Floor-to-area ratio (site area) & 3.45 \\ \hline Parking index & 5 spaces/1,000 sq. ft. (GLA) \\ \hline Parking spaces & 550 \\ \hline \end{tabular} B. Development period 12 months C. Site plan \begin{tabular}{l|r} \hline Building coverage & 29% \\ \hline Street parking & 45% \\ \hline Open space/landscaping & 26% \\ \hline Total & 100% \end{tabular} D. Loan information Construction loan Permanent loan \begin{tabular}{|l|c|} \hline Debt amortization & 25 years \\ \hline Term of loan & 10 years \\ \hline Interest rate & 12% \\ \hline Permanent loan fee & 3% PAID UPFRONT \end{tabular} E. Anticipated hold after completion 5 years F. Project Costs I. Land and site improvements: Site acquisition and closing costs (PAID UPFRONT) $2,500,000 On-/off-site improvement costs: Off-site improvements $250,000 On-site improvements: \begin{tabular}{lr|} \hline Excavation and grading & 50,000 \\ \hline Sewer/water & 150,000 \\ \hline Paving & 200,000 \\ \hline Curbs/sidewalks & 100,000 \\ \hline Landscaping & 100,000 \end{tabular} Total on-/off-site costs $850,000 II. Construction costs: Hard costs: Shell structure 3,925,000 \begin{tabular}{|l|r|} \hline HVAC & 528,500 \\ \hline Electrical & 613,000 \\ \hline Plumbing & 221,580 \\ \hline Project management fees & 300,250 \\ \hline Finish-out & 1,400,600 \\ \hline Graphics/signage & 66,570 \end{tabular} Total hard costs 7,055,500 Soft costs: \begin{tabular}{l|r|} \hline Architect/engineering & $147,000 \\ \hline Fees and permits & 24,300 \\ \hline Legal fees & 26,900 \\ \hline Construction interest & 692,416 \\ \hline Construction loan fees & 180,029 \end{tabular} \begin{tabular}{|l|r|r|} \hline Permanent loan fees & 270,042 \\ \hline Leasing commissions & 45,300 & \\ \hline Direct overhead & 160,000 & \\ \hline Indirect overhead & 30,800 & \\ \hline Total soft costs & & $1,576,787 \\ \hline Total Project Costs & & $11,982,287 \\ \hline \end{tabular} Percent of Cost per Total Cost Sq. Ft. GBA A. Land and site improvements: Site acquisition and closing costs On-/off-site improvement costs: Off-site improvements On-site improvements: Excavation and grading Sewer/water Paving Curbs/sidewalks Landscaping Total on-/off-site costs B. Construction costs: Hard costs: Shell structure HVAC Electrical Plumbing Project management fees Finish-out Graphics/signage Total hard costs Soft costs: Architect engineering Fees and permits Legal fees Construction interest Construction loan fees Permanent loan fees Leasing commissions Direct overhead Indirect overhead Construction Loan Request: Total on-/off-site improvements Total hard construction costs Extent of Soft Costs Funded Total costs to be financed Construction loan interest (Estimated interest carry) Total loan amount Equity requirements Total project cost DRAWS per YEAR Year 0 Year 1 Total Site acquisition and closing costs Site improvements (on-/off-) Hard costs Soft costs Permanent loan fee Construction loan fee Construction interest Total construction cash outflow Less: Total draws Total equity needed CASH FLOW ASSUMPTIONS Period of constuction 12 months Tenant occupancy comences Year 2 \% occupied Year 2 % occupied after Year 2 Base Rent Year 2 Rental Growth Rate Percentage of sales Breakpoint $5,000,000 Year 2 sales $5,600,000 Year 3 sales $7,496,000 Overage rent growth( post year2) 4% 100% Reimbursable expenses (Direct pass thru) \begin{tabular}{|rr|} & 100% \\ \hline$ & 137,500 \\ $ & 385,000 \\ $ & 300,300 \\ \hline & 22.4% \\ \hline$ & 33,000 \\ & 4% \\ \hline \end{tabular} Non-reimbursable expenses Management \& leasing fees \begin{tabular}{l|rr} Year 2 & $ & 104,500 \\ \hline Thereafter & $ & 93,690 \\ \hline Management fees growth, commences Year 3 & & 5.87% \\ \hline G \& A & $ & 77,000 \\ \hline Other & $ & 27,500 \\ \hline Other expense growth rate & & 4% \\ \hline \end{tabular} Sales price based on year 6 cash flow before debt service Selling cap rate 11.875% Selling expenses 2% Cash Flows (End of Year) 0 1 2 3 Income: Minimum rent Overage (5\% of gross sales) Tenant reimbursements Real Estate Taxes Common area maintenance Utilities Insurance Gross Potential Income Less: Vacancy Allowance Expected Gross Income Expenses: Management \& Leasing fees G&A Real Estate Taxes Common area maintenance Utilities Insurance Other Total Expenses Net Operating Income Less: Debt Service Cash Flow (Deficit) REQUIREMENTS Annaul Debt Service Coverage Annaul Investment Yield (NOI/Costs) Annaul Return on Equity Internal Rate of Return A. Site and Proposed improvements \begin{tabular}{|l|c|} \hline Site area (in acres) & 9.5 acres \\ \hline Gross buildable area (GBA) sq. ft. & 120,000 sq. ft. \\ \hline Gross leasable area (GLA) sq. ft. & 110,000 sq. ft. \\ \hline Percent leasable area & 91.67% \\ \hline Floor-to-area ratio (site area) & 3.45 \\ \hline Parking index & 5 spaces/1,000 sq. ft. (GLA) \\ \hline Parking spaces & 550 \\ \hline \end{tabular} B. Development period 12 months C. Site plan \begin{tabular}{l|r} \hline Building coverage & 29% \\ \hline Street parking & 45% \\ \hline Open space/landscaping & 26% \\ \hline Total & 100% \end{tabular} D. Loan information Construction loan Permanent loan \begin{tabular}{|l|c|} \hline Debt amortization & 25 years \\ \hline Term of loan & 10 years \\ \hline Interest rate & 12% \\ \hline Permanent loan fee & 3% PAID UPFRONT \end{tabular} E. Anticipated hold after completion 5 years F. Project Costs I. Land and site improvements: Site acquisition and closing costs (PAID UPFRONT) $2,500,000 On-/off-site improvement costs: Off-site improvements $250,000 On-site improvements: \begin{tabular}{lr|} \hline Excavation and grading & 50,000 \\ \hline Sewer/water & 150,000 \\ \hline Paving & 200,000 \\ \hline Curbs/sidewalks & 100,000 \\ \hline Landscaping & 100,000 \end{tabular} Total on-/off-site costs $850,000 II. Construction costs: Hard costs: Shell structure 3,925,000 \begin{tabular}{|l|r|} \hline HVAC & 528,500 \\ \hline Electrical & 613,000 \\ \hline Plumbing & 221,580 \\ \hline Project management fees & 300,250 \\ \hline Finish-out & 1,400,600 \\ \hline Graphics/signage & 66,570 \end{tabular} Total hard costs 7,055,500 Soft costs: \begin{tabular}{l|r|} \hline Architect/engineering & $147,000 \\ \hline Fees and permits & 24,300 \\ \hline Legal fees & 26,900 \\ \hline Construction interest & 692,416 \\ \hline Construction loan fees & 180,029 \end{tabular} \begin{tabular}{|l|r|r|} \hline Permanent loan fees & 270,042 \\ \hline Leasing commissions & 45,300 & \\ \hline Direct overhead & 160,000 & \\ \hline Indirect overhead & 30,800 & \\ \hline Total soft costs & & $1,576,787 \\ \hline Total Project Costs & & $11,982,287 \\ \hline \end{tabular} Percent of Cost per Total Cost Sq. Ft. GBA A. Land and site improvements: Site acquisition and closing costs On-/off-site improvement costs: Off-site improvements On-site improvements: Excavation and grading Sewer/water Paving Curbs/sidewalks Landscaping Total on-/off-site costs B. Construction costs: Hard costs: Shell structure HVAC Electrical Plumbing Project management fees Finish-out Graphics/signage Total hard costs Soft costs: Architect engineering Fees and permits Legal fees Construction interest Construction loan fees Permanent loan fees Leasing commissions Direct overhead Indirect overhead Construction Loan Request: Total on-/off-site improvements Total hard construction costs Extent of Soft Costs Funded Total costs to be financed Construction loan interest (Estimated interest carry) Total loan amount Equity requirements Total project cost DRAWS per YEAR Year 0 Year 1 Total Site acquisition and closing costs Site improvements (on-/off-) Hard costs Soft costs Permanent loan fee Construction loan fee Construction interest Total construction cash outflow Less: Total draws Total equity needed CASH FLOW ASSUMPTIONS Period of constuction 12 months Tenant occupancy comences Year 2 \% occupied Year 2 % occupied after Year 2 Base Rent Year 2 Rental Growth Rate Percentage of sales Breakpoint $5,000,000 Year 2 sales $5,600,000 Year 3 sales $7,496,000 Overage rent growth( post year2) 4% 100% Reimbursable expenses (Direct pass thru) \begin{tabular}{|rr|} & 100% \\ \hline$ & 137,500 \\ $ & 385,000 \\ $ & 300,300 \\ \hline & 22.4% \\ \hline$ & 33,000 \\ & 4% \\ \hline \end{tabular} Non-reimbursable expenses Management \& leasing fees \begin{tabular}{l|rr} Year 2 & $ & 104,500 \\ \hline Thereafter & $ & 93,690 \\ \hline Management fees growth, commences Year 3 & & 5.87% \\ \hline G \& A & $ & 77,000 \\ \hline Other & $ & 27,500 \\ \hline Other expense growth rate & & 4% \\ \hline \end{tabular} Sales price based on year 6 cash flow before debt service Selling cap rate 11.875% Selling expenses 2% Cash Flows (End of Year) 0 1 2 3 Income: Minimum rent Overage (5\% of gross sales) Tenant reimbursements Real Estate Taxes Common area maintenance Utilities Insurance Gross Potential Income Less: Vacancy Allowance Expected Gross Income Expenses: Management \& Leasing fees G&A Real Estate Taxes Common area maintenance Utilities Insurance Other Total Expenses Net Operating Income Less: Debt Service Cash Flow (Deficit) REQUIREMENTS Annaul Debt Service Coverage Annaul Investment Yield (NOI/Costs) Annaul Return on Equity Internal Rate of Return