You are considering purchasing a 12,000 square feet (4,000 sf. per floor), three story office building for $1,250,000 with closing to be on May 12. The top and bottom floors are preleased for $24 per sq, ft. annually and the middle floor for $20 annually. Land costs are estimated at 10% of the value of the property. You expect rents to increase by 7% per year. You project that your vacancy and collection losses will be about 6%. You expect that operating expenses will be 35% of the adjusted gross income. The Friendly Federal Savings Bank has agreed to lend to you at a 9.5% annual interest rate with an amortization period of 30 years paid monthly. The loan will have only a term of 10 years. The amount of the loan is going to be based upon a 1.5 Debt Service Coverage Ratio for the first year's NOI. The bank is going to charge three points. At the end of year 3 you expect to refinance the property under the same terms as the original loan except for a reduction in the interest rate to 8% and a points charge of four points. (The new loan is based upon the year 4 NOI!) Your accountant has advised you that for this investment analysis, you should use expect a 31% marginal tax rate. You expect to sell the property at the end of the fifth year. The sales price is expected to be based upon an 11.5% capitalization rate of the year six NOI. Sales expenses are projected at 8%. You have concluded that your cost of equity capital is 10%. ****Finally, be sure that you DO NOT enter a space before, after, or in the middle of your answers. Entering a space will cause the answer to be scored incorrectly.****
What is the indicated loan amount? (Round your answers to the nearest $1)
Prepare a 5-year amortization schedule (Be careful to change the loan for year 4 and 5!):
Year 1 Year 2 Year 3 Year 4 Year 5
ADS
Interest
Principle
EOR Balance
How much equity cash is required?
Prepare a five-year projection of the before-tax cash flow for the property and a six-year projection of the net operating income.
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6
Gross Income
V&C
Adj. Gross
Operating Exp.
NOI
ADS
Refinancing $
BTCF
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6
NOI
Interest
Depreciation
Points
Unexpensed Pts
Taxable Income
Taxes
BTCF
Taxes
ATCF
Compute the cash flow from reversion:
Sales Price
Selling Expense
Amount Realized
Mortgage Payoff
BTCF (reversion)
Purchase Price
Accumulated Depreciation
Adjusted Basis
Amount Realized
Adjusted Basis
Taxable Gain
Unexpended Points
Net Gain
Less Accumulated Depreciation
Preferential Taxable Gain
Tax on Preferential Gain
Accumulated Depreciation
Tax on gain from Depreciation
Tax on Preferential Gain
Total taxes on Gain
BTCF (reversion)
Taxes (reversion)
ATCF (reversion)
What is the indicated Net Present Value of this investment?
What is the Internal Rate of Return of this investment?
still need help
To calculate the indicated loan amount, we need to determine the Debt Service Coverage Ratio (DSCR) for the first year's Net Operating Income (NOI). The DSCR is calculated by dividing the NOI by the annual debt service.
1. Calculate the NOI for the first year:
- Determine the annual rental income for each floor:
- Top and bottom floors: $24 per sq. ft. x 4,000 sq. ft. = $96,000
- Middle floor: $20 per sq. ft. x 4,000 sq. ft. = $80,000
- Calculate the total annual rental income:
- Total rental income = ($96,000 x 2) + $80,000 = $272,000
- Subtract the vacancy and collection losses (6%):
- Adjusted rental income = $272,000 - (6% x $272,000) = $255,680
- Calculate the Operating Expenses (35% of the adjusted rental income):
- Operating Expenses = 35% x $255,680 = $89,648
- Calculate the Net Operating Income (NOI):
- NOI = Adjusted rental income - Operating Expenses
= $255,680 - $89,648 = $166,032
2. Calculate the annual debt service:
- Convert the loan's annual interest rate to a monthly interest rate:
- Monthly Interest Rate = 9.5% / 12 = 0.7917%
- Calculate the monthly loan payment using the amortization period and loan amount:
- Monthly loan payment = Loan amount x Monthly Interest Rate / (1 - (1 + Monthly Interest Rate)^(-12 x Loan Term))
- Divide the monthly loan payment by 12 to get the annual debt service.
3. Calculate the indicated loan amount:
- Indicated Loan amount = NOI / DSCR
To prepare a 5-year amortization schedule, start with the loan amount and calculate the interest, principal, and ending balance for each year. Use the loan term, interest rate, and monthly payments to calculate these values.
To calculate how much equity cash is required, you need to determine the total purchase cost and the loan amount. Subtract the loan amount from the total purchase cost to get the equity cash required.
Next, to prepare a five-year projection of the before-tax cash flow (BTCF) and a six-year projection of the net operating income (NOI), you need to calculate various financial indicators such as gross income, vacancy and collection losses, adjusted gross income, operating expenses, NOI, interest, depreciation, points, unexpensed points, taxable income, taxes, BTCF, and ATCF.
To compute the cash flow from reversion, you need to calculate the sales price, selling expenses, amount realized, mortgage payoff, BTCF (reversion), purchase price, accumulated depreciation, adjusted basis, taxable gain, unexpended points, net gain, less accumulated depreciation, preferential taxable gain, tax on preferential gain, accumulated depreciation, tax on gain from depreciation, tax on preferential gain, and total taxes on gain.
Finally, to calculate the indicated Net Present Value (NPV) and Internal Rate of Return (IRR) of the investment, you need to consider the cash flows and discount rate. Net Present Value is calculated by discounting the cash flows to the present value and subtracting the initial investment. The Internal Rate of Return is the discount rate that makes the NPV equal to zero when all cash flows are considered.
Perform the necessary calculations for each step to obtain the desired answers.