Ten year Project$120$ unit Apartment ComplexPurchase Price:$$3,056,600$Holding Period:$10$ YearsDepreciation in years:$30$Land Value $\%$ of Price:$25\%$Loan to Value:$70\%$Term of Loan:$20$ yearsPoints to close:$2\%$Interest Rate:$6\mathrm{.}5\%$Prepayment Penalty $1\%$ of the loan before $15$ yearsInvestor Tax Rate $34\%$Growth rate in rents:$4\mathrm{.}5\%$Vacancy Rate:$13\%$Cap rate for resale:$12\%$ Going out Cap rateSelling Costs:$7\%$ of selling priceCapital gain s Tax Rate:$15\%$Target Equity IRR after Tax:$12\%$AVERAGE RENT Per TypeAverage Rent# of UnitsStudio Apartments: $$40040$One bedroom Apartments: $$60050$Two bedroom Apartments: $$80030$Annual Operating Expenses:Management $\%$ of effective gross income:$8\%$ including ManagerOther Annual Income:$0$Property Taxes:$$136,329$Property Tax Growth rate:$2\%$ yearProperty Insurance:$$35,000$Property insurance growth rate:$2\%$Maintenance and repair:$$200,000$ includes replacement reservesOther Expenses$$12,000$Other Expenses Growth rate:$4\%$Summary Information:Purchase Price:$$3,056,600$Acquisition Costs:$$0$Acquisition Going in Cap rate:$\_\_\_\_\_12\%\_\_\_\_\_$Initial Equity Investment Including Points:$\_\_\_\_$$$2,139,660\_\_\_\_\_\_$Cost Recovery Value of Improvements $($Depreciation$)\_\_\_\_$$$1,926,990\_\_\_\_\_\_$Useful Life in Years$11\mathrm{.}5$ Month year Cost Recovery Deduction $($IRS Rule$)\_\_\_\_\_\_\_\_\_\_$Full yr depreciation based on $27\mathrm{.}5$ yrs $($IRS rule$)\_\_\_\_\_\_\_\_\_\_$Disposition Cap Rate based on $11$th yr NOI$10\%$Projected Sale price $($end of $10$yrs$)\_\_\_\_\_\_\_\_\_\_$Projected Cost of Sale $($end of $10$ yrs$)\_\_\_\_\_\_\_\_\_\_($based on $7\%$ of price$)$Acquisition Financing Information Loan amount:$\_\_\_\_\_\_\_\_\_\_$Interest Rate:$6\mathrm{.}5\%$Payments per yr:$12$Periodic Payment:$\_\_\_\_\_\_\_\_\_\_$Annual Debt Service:$\_\_\_\_\_\_\_\_\_\_$Amortization Period:$20$ yearsTerm:$20$ yearsLoan cost at $2\%$ of Loan:$\_\_\_\_\_\_\_\_\_\_$Effective cost of Borrowed Funds:$\_\_\_\_\_\_\_\_\_\_($with prepay ment at end of $10$ yrs$)$Other InformationLeaseTerm information $($units$)$:Month to Month $1$ year $+2$ year$+$Studio Apartments: $30100$One bedroom Apartments: $30155$Two bedroom Apartments: $15105($Remember if on lease other than month to month cannot increase rent until lease is up$.$ Assume lease term ends at period $($ ie$.1$ Year leases end in $1$ year and $2$ year in $2$ years. Also assume similar rotation for each year$).$Property Valuation:Assume Property increases in value at rate if inflation. Using the Miller Geltner Spreadsheet from Chapter $14$Starting: When the spreadsheet opens, hit the icon that says enable macros.You will be on the Input page of the spreadsheet. Your inputs feed into light yellow colored cells. The input page looks something like this with new inputs as shown:Investor Assumptions$1.$Tax Rate on Ordinary Income$34\mathrm{.}00\%2.$Tax Rate on Capital Gains$15\mathrm{.}00\%3.$Tax Rate on Cost Recovery Recapture$25\mathrm{.}00\%4.$Projected Holding Period$105.$EOY $10$ Disposition Price $(11$th Year NOI Capped or $ Amount$)10\mathrm{.}00$ $$760,5666.$Disposition Cost of Sale$7\mathrm{.}00\%$Property Assumptions$7.$Purchase Price $($Acquisition Cap Rate or $ Amount$)650,000\mathrm{.}00$ $$650,0008.$Acquisition Costs$$09.$Improvement Allocation$80\mathrm{.}00\%10.$Useful Life$27\mathrm{.}511.$Year $1$ Potential Rental Income$/$Constant Annual Growth Rate$$90,0003\mathrm{.}00\%12.$Constant Vacancy Rate$5\mathrm{.}00\%13.$Property Management Expense $(\%$ of Effective Rental Income$)8\mathrm{.}00\%14.$Year $1$ Property Taxes$/$Constant Annual Growth Rate$$8,9262\mathrm{.}00\%15.$Year $1$ Property Insurance$/$Constant Annual Growth Rate$$2,4372\mathrm{.}00\%16.$Year $1$ Repairs and Maintenance$/$Constant Annual Growth Rate$$10,5603\mathrm{.}00\%17.$Year $1$ Accounting and Advertising$/$Constant Annual Growth Rate$$1,2003\mathrm{.}00\%$Acquisition Financing Assumptions$18.$Calculation of Loan Amount $(\%$ of Purchase Price, DCR$,$ or Loan Amount$)80\mathrm{.}00$ $$520,0009.$Interest Rate$8\mathrm{.}25\%$Amortization Period$2521.$Term$2522.$Payments Per Year$1223.$Loan Costs $(\%$ of Loan Amount or $ Amount$)1\mathrm{.}00$ Refinance Assumptions$24.$Projected Year of Refinance $($EOY$)025.$Calculation of Loan Amount $(\%$ of Value,DCR$,$ or Loan Amount$)0\mathrm{.}00$ $$026.$Interest Rate$0\mathrm{.}00\%27.$Amortization Period$2528.$Term$2529.$Payments Per Year$1230.$Loan Costs $(\%$ of Loan Amount or $ Amount$)0\mathrm{.}00$ The Acquisition Information page is a summary of inputs and should show these figures:Property InformationPurchase Price$$650,000$ Acquisition Costs$$0$ Acquisition Cap Rate$8\mathrm{.}54\%$Initial Investment$$135,200$ Cost Recovery Value of Improvements$$520,000$ Useful Life$27\mathrm{.}5111/2$ Month Year Cost Recovery Deduction$$18,122$ Full Year Cost Recovery Deduction$$18,907$ Disposition Cap Rate$10\mathrm{.}00\%$Projected End of Year $10$ Sale Price$$760,566$ Projected End of Year $10$ Cost of Sale$$53,240$ Acquisition Financing InformationLoan Amount$$520,000$ Interest Rate$8\mathrm{.}25\%$Payments Per Year$12$ Periodic Payment$$4,099\mathrm{.}94$ Annual Debt Service$$49,199$ Amortization Period$25$ Term$25$ Loan Costs$$5,200$ Effective Cost of Borrowed Funds$8\mathrm{.}41\%$The cash flow page is shown below. You need to look at the lines that show the NOI in line $10,$ the before tax cash flow on line $20$ and the financial ratios below on lines $2331.$ Note in particular the Debt Coverage Ratio or DCR on line $23.$ It is the first year DCR that counts the most. You will see $10$ years even though only $3$ years are shown below.