Use excel function only, step by step for this case :

Led by Chief Executive Officer, Kyle Bach, The Annex Group, LLC (Annex) is a fast-growing Indianapolis based company focused on both market rate student housing properties that are leased primarily to students and affordable housing apartments located in college towns that are leased primarily to non-students. In 2018 Annex was listed as the 7^th fastest growing private company in central Indiana by the Indianapolis Business Journal.

Like many fast-growing companies in capital intensive industries like real estate, The Annex Group sometimes considers joint ventures to help fund its projects. One such project is the Annex on 10^th development near the IUPUI campus in Indianapolis, Indiana. Hageman Group was the joint venture equity partner for the initial development of the Annex on 10^th project.

Annex on 10th development is an apartment complex. The following details are collected for feasibility analysis:

The purchase price is $1,000,000.

Potential gross income (PGI) for the first year of operations is projected to be $171,000.

PGI is expected to increase at 4 percent per year.

No vacancies are expected.

Operating expenses are estimated at 35 percent of effective gross income. Ignore capital expenditures.

The market value of the investment is expected to increase 4 percent per year.

Selling expenses will be 4 percent.

The holding period is 4 years.

The appropriate unlevered rate of return to discount projected NOIs and the projected NSP is 12 percent.

The required levered rate of return is 14 percent.

70 percent of the acquisition price can be borrowed with a 30-year, monthly payment mortgage.

The annual interest rate on the mortgage will be 8.0 percent.

Financing costs will equal 2 percent of the loan amount.

There are no prepayment penalties.

Can you help Annex Group to provide a detailed pro-forma DCF analysis for this project?

Flag question: Question 1Question 11 pts

Recall that the measure of income generally sought by property value is annual net operating income (NOI).

Let's first analyze the NOI for this project.

Calculate net operating income (NOI) for year 1?

Group of answer choices

$91,150

$100,150

$111,150

$120,150

Flag question: Question 2Question 21 pts

Calculate net operating income (NOI) for year 2?

Group of answer choices

$125,596

$135,596

$105,596

$115,596

Flag question: Question 3Question 31 pts

Case 4: Calculate net operating income (NOI) for year 3?

Group of answer choices

$120,220

$150,220

$130,220

$140,220

Flag question: Question 4Question 41 pts

Case 4: Calculate net operating income (NOI) for year 4?

Group of answer choices

$125,029

$135,029

$145,029

$155,029

Flag question: Question 5Question 51 pts

So far, we have calculated NOI for each year within our holding period. At the end of year 4, Annex sells the property.

Calculate the net sale proceeds from the sale of the property?

Hint: Make sure to consider house price appreciation.

Group of answer choices

$2,223,065

$1,923,065

$1,523,065

$1,123,065

Flag question: Question 6Question 61 pts

Now, you helped Annex build up its pro-forma, with (1) future annual cash flows from property operations, (2) the net cash flow from disposition of the property at the end of the assumed investment holding period,

Implementation of DCF valuation requires that future cash flow estimates be converted into present values. Why must future cash flows be discounted (reduced) into a present value? Because a dollar to be received in, say, one year is not as valuable as having the dollar in hand today.

Calculate the net present value of this investment, assuming no mortgage debt? Should you purchase?

Hint: Don't forget about your up-front purchase price.

Group of answer choices

$70,150, No

$70,150, Yes

-$70,150, No

-$70,150, Yes

Flag question: Question 7Question 71 pts

As you know, Annex would like to know the IRR for this project.

Calculate the internal rate of return of this investment, assuming no debt. Should you purchase?

Hint: Compare your IRR to unlevered rate of return which is 12%.

Group of answer choices

14.22, No

14.22, Yes

24.22, Yes

24.22, Yes

Flag question: Question 8Question 81 pts

As we discussed in class, the use of mortgage debt to help finance the acquisition of real estate is pervasive, and therefore its effect on risk and return should be clearly understood.

Many market participants recommend extensive use of debt! (We all know why.)

Although increased financial leverage increases, in some cases substantially, the estimated IRR, financial leverage is a double-edged sword. Its use enhances equity returns when the property is performing well. However, if the property performs poorly, the use of debt can make a bad situation worse.

Now, let's consider the case of using commercial mortgage debt.

First step, calculate the monthly mortgage payment. What is the total per year?

Hint: Monthly payment *12

Group of answer choices

$61,636

$41,636

$5,136.35

$51,636

Flag question: Question 9Question 91 pts

Calculate the loan balance at the end of years 1, 2, 3, and 4. (Note: the unpaid mortgage balance at any time is equal to the present value of the remaining payments, discounted at the contract rate of interest.)

What is unpaid mortgage balance in year 1?

Group of answer choices

$894,152

$794,152

$614,152

$694,152

Flag question: Question 10Question 101 pts

Calculate the loan balance at the end of years 1, 2, 3, and 4. (Note: the unpaid mortgage balance at any time is equal to the present value of the remaining payments, discounted at the contract rate of interest.)

What is unpaid mortgage balance in year 2?

Group of answer choices

$887,820

$987,820

$787,820

$687,820

Flag question: Question 11Question 111 pts

What is unpaid mortgage balance in year 3?

Group of answer choices

$490,961

$580,961

$600,961

$680,961

Flag question: Question 12Question 121 pts

What is unpaid mortgage balance in year 4?

Group of answer choices

$573,533

$673,533

$603,533

$473,533

Flag question: Question 13Question 131 pts

Now, let's consider mortgage principal pay down. Recall that our mortgage payment has two parts: (1) interest payment and (2) principal reduction.

Calculate the amount of principal reduction achieved during each of the four years.

What is the Principal reduction in year 4?

Group of answer choices

$2,428

$1,428

$6,128

$7,428

Flag question: Question 14Question 141 pts

Calculate the total interest paid during each of the four years.

Hint: Remember that debt service equals principal plus interest.

What is the Interest paid in year 4?

Group of answer choices

$34,208

$84,208

$54,208

$44,208

Flag question: Question 15Question 151 pts

Most commercial properties require debt financing tailored to meet the unique circumstances of the situation.

When a mortgage loan is obtained, the cash down payment (i.e., equity) required at property acquisition

In this case, calculate the levered required initial equity investment for Annex.

Group of answer choices

$354,000

$314,000

$214,000

$394,000

Flag question: Question 16Question 161 pts

Recall that before-tax cash flow from annual rental operations (BTCF) is defined as net operating income (NOI) minus the sum of the mortgage payments (debt service). BTCF is the expected cash flow left over from rental operations each year after paying all operating expenses, capital expenditures, and servicing the mortgage debt.

In your excel, can you calculate the before-tax cash flow (BTCF) for each of the four years.

What is BTCF for year 4?

Group of answer choices

$13,393

$63,393

$43,393

$53,393

Question 171 pts

Recall that the before-tax equity reversion (BTER) is defined as the net selling price minus the remaining mortgage balance (RMB) at the time of sale.

Calculate the before-tax equity reversion (BTER) from the sale of the property.

Group of answer choices

$449,532

$229,532

$119,532

$339,532

Flag question: Question 18Question 181 pts

Discounted cash flow analysis has become the main financial analysis tool used to evaluate the investment desirability of commercial real estate. Although much of the effort in DCF analysis goes toward the estimation of future cash flows, net present value and the internal rate of return on equity are the bottom-line decision tools of investors.

And yes, I know that you already did a NPV calculation for this project. This time, how about consider the debt usage?

Calculate the levered net present value of this investment with leverage.

Group of answer choices

$194,189

$114,189

$174,189

$224,189

Flag question: Question 19Question 191 pts

It often hinders comparisons to other investment alternatives when investment perfor- mance is expressed in dollar terms. Although certain technical problems are associated with its use, the internal rate of return (IRR) continues to be a widely used measure of investment returns in real estate and other business fields, such as corporate finance.

Calculate the levered internal rate of return of this investment.

Group of answer choices

25.02%

15.05%

35.09%

29.09%

Flag question: Question 20Question 201 pts

We now turn to a discussion of single-year return measures and ratios widely used by real estate investors and lenders to evaluate potential investments. These ratios work best when used to compare a stabilized property to other stabilized properties.

Calculate, for the first year of operations, the: overall (cap) rate of return

Group of answer choices

11.12%

41.12%

21.12%

31.12%

Flag question: Question 21Question 211 pts

Because many commercial property investments involve the use of mortgage funds and because the cost of mortgage debt may differ across investment opportunities, the use of the capitalization rate as a measure of profitability has potential limitations.

The equity dividend rate shows investors what percentage of their initial equity investment is expected to be returned to them in cash during the next 12 months (before income taxes).

Calculate, for the first year of operations, the: Equity dividend rate

Group of answer choices

30.8%

15.8%

10.8%

20.8%

Flag question: Question 22Question 221 pts

Income multipliers can be used to provide a quick assessment of whether a property is priced reasonably in relation to its gross or net income.

Calculate, for the first year of operations, the: Gross income multiplier

Group of answer choices

3.85

4.85

6.85

5.85

Flag question: Question 23Question 231 pts

The debt coverage ratio (DCR) indicates the extent to which net operating income (NOI) can decline before it is insufficient to cover the debt service (DS).

The DCR provides an indication of safety from potential borrower default in the event rental revenues fall and the mortgage payment is in jeopardy.

Calculate, for the first year of operations, the: Debt coverage ratio.

Group of answer choices

1.1

2.0

1.8

1.0