Give Excel step by step solutions to these questions Question 1 pts

Construction Loan Request

Now, let's consider a construction loan request.

Table 2 contains a breakdown of the loan request. Note that this particular loan request does not include land cost. Also note that it does not ask for financing for all soft costs.

However, Southfork is requesting funding for some off-site improvements. The total loan request is $9,001,416, which represents about _________ percent of the $11,982,287 estimate of total project cost (land plus all other outlays).

Group of answer choices

75%

65%

55%

85%

2. Can you take a look at the components of total amount? What are included in the loan request?

Group of answer choices

site improvement + hard construction costs+soft costs+ interest carry

site improvement + hard construction costs+soft costs

site improvement + hard construction costs

site improvement+equity requirements

Question 3

Based on the last question, I think you find an interesting thing in construction lending-----interest.

It is very common in construction lending because the project would not generate much rent or cash inflow during development.

Now, let's help the developer figure out a loan repayment schedule. Can you copy and past this table into your excel. Use table 1 and table 2 as your background information. I provide some numbers for month 12 as your reference.

Hint: table 1 shows the construction loan details.

What is the loan payment for the first 4 months?

Group of answer choices

$1,557,938

$1,457,938

$1,357,938

$1,257,938

Question 4

What is the loan payment from month 5 to month 12?

Group of answer choices

$259,656

$269,656

$249,656

$279,656

Question 5

Alright, now, you basically finish the first column (a) project costs. We can figure out the second column. What would be interest for each month?

Hint: Current month's interest would depend on the loan balance by the end of last month. Loan balance would increase each month, and it would be project costs + construction interest.

Based on your excel, what is the construction interest for month 4?

Group of answer choices

47,207

46,207

45,207

44,207

Question 6

What is the total loan balance for month 10?

Group of answer choices

8,312,427

8,412,427

8,512,427

8,612,427

Question 7

Now, can you add up all your interest payment? What is the total interest? Can you double check table 2. What is the total interest carry? Are they the same?

Group of answer choices

$ 692,416

$ 682,416

$ 672,416

$ 662,416

Question 8

In this case, what is the total draw for this developer? You may want to add one additional column to your excel, and calculate the monthly draw.

What is the total draw for month 4?

Group of answer choices

$1,605,145

$1,305,145

$1,405,145

$1,505,145

Question 9

Permanent Loan Request

Upon completion of the project, Citadel Life Insurance Company will replace the construction loan with permanent financing, assuming that all conditions in the construction loan and all contingencies outlined in the permanent financing commitment have been met. Remember, the permanent loan terms were predetermined before construction began.

Here is a summary table:

Any additional development costs over $9,001,416 are Southforks responsibility. For a 3 percent loan fee, Citadel Life Insurance Company will provide Southfork with a 10-year mortgage.

Can you use your excel and calculate the monthly payment for this commercial loan?

Hint: it is based on a 25-year amortization schedule at an interest rate of 12 percent.

Group of answer choices

$94,805

$95,805

$96,805

$97,805

Question 10

Equity Investment During Construction Period

Another ingredient in the submission of data to the permanent lender is a pro-forma of construction costs and equity investment. The table above shows a summary. Note that it contains annual estimates for expenditures during the construction period for land acquisition, site improvements, hard costs and soft costs.

Can you take a look at the table, make sure you fully understand all the numbers in the table.

What is the total equity needed for year 0?

Group of answer choices

$2,950,071

$2,850,071

$2,750,071

$2,650,071

Question 11

Use the table in the previous question, for year 0, what would the equity investment cover?

Group of answer choices

land acquisition and loan fee

land acquistion

loan fee and hard cost

land acquistion and hard cost

Question 12

How much equity needed for year 1?

Group of answer choices

$30,800

$31,800

$32,800

$33,800

Question 13

In general, how much total equity investment is needed during construction period?

Group of answer choices

$2,980,871

$2,880,871

$2,780,871

$2,680,871

Operating Period----Determine EGI

The lease-up or marketing effort should result in 70 percent occupancy during the second year and 95 percent thereafter.

Southfork is estimating a base rent of $15 per square foot of gross leasable area, with average increases based on leases indexed to the CPI of 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 tenants to also pay 5 percent of gross sales in excess of a base sales level each month.

In a retail operation, rent is usually divided into two components. The first is a minimum rent per square foot. The other component is called percentage rent. Developers frequently charge percentage rent, calculated as a percentage of the sales of a tenant in excess of a predetermined breakpoint or sales volume. As long as the tenants sales are below the breakpoint, the owner receives only the minimum rent. When a tenants sales increase above the breakpoint, the percentage rent rate is applied to the sales volume in excess of the breakpoint and is added to the minimum rent, thus increasing the total rent. In this way, should the shopping center become very successful, the owner shares in the increased revenue produced by the tenants.

Tenant reimbursements are based on negotiations between the owner and tenants and represent the amount of operating expenses over expense stops for which the tenant is responsible. Hence, base rents, percentage rents, and expenses for which tenants are responsible over some preagreed amount (stop) all represent gross income to the owner of Rolling Meadows.

The table below shows the details.

Operating Period ---Determine the total expenses

Operating expenses represent the actual expenses that must be paid to operate Rolling Meadows. They are deducted from rents, overage, and tenant reimbursements.

All leases are to be net to the tenant, 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 will be added to the utility charge to provide for a replacement reserve on HVAC equipment.

Tenants will pay these expenses to Southfork as reimbursement. Southfork management will, in turn, pay any expenses to third parties as they become due. Southfork will also incur expenses of its own for property management, leasing commissions, and general and administrative expenses that will not be recoverable from tenants. These amounts are deducted from rents, overage, and tenant reimbursements. The projections assume that a sufficient number of leases will be signed at the end of the second year to warrant closing the permanent loan.

Here is a table:

Question 14

Operating Period ---Determine NOI

Based on the table above, can you calculate net operating income each year for 2-6 years?

Determine the NOI for year 3.

Group of answer choices

$1,092,402

$1,192,402

$1,292,402

$1,392,402

Question 15

Can you determine the NOI for year 6?

Group of answer choices

$1,903,498

$1,803,498

$1,703,498

$1,603,498

Question 16

For your excel, can you add one additional row below NOI, called debt service?

What is annual debt service?

Group of answer choices

$1,137,661

$1,237,661

$1,337,661

$1,437,661

Question 17

Now, we can determine the before-tax cash flow.

What is your year 2 BTCF?

Group of answer choices

$-380,361

$-370,361

$-360,361

$-350,361

Question 18

What is your before tax cash flow in year 4?

Group of answer choices

$557,438

$547,438

$537,438

$527,438

Question 19

Now, let's analyze the financial risks.

The operating expense ratio, which is calculated by dividing total annual operating expenses by the effective gross income (EGI).

What is the operating expense ratio in year 2? In other words, ______ of EGI from Rolling Meadows will go into servicing operating expenses.

Group of answer choices

40.58%

43.58%

44.58%

46.58%

Question 20

What is Rolling Meadowss debt coverage ratio?

Hint: use year 2's NOI

This ratio demonstrates the propertys ability to meet its debt payments.

Group of answer choices

1.41

1.31

1.21

1.11