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 Type Average 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: $\_\_\_\_\_\_\_\_\_\_$Initial Equity Investment Including Points: $\_\_\_\_\_\_\_\_\_\_$Cost Recovery Value of Improvements $($Depreciation$)\_\_\_\_\_\_\_\_\_\_$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 InformationLoan 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.Input Assumptions for various valuation methods Required forMortgage Equity Methods or Ellwood required assumptions Initial Net Operating Income$$50,000$first year Growth Rate in NOI Per Year$3\mathrm{.}00\%$ Implied Effective Constant NOI $$55,250$ over holding period to use as stabilized NOI in Ellwood Loan to Value Ratio$75\mathrm{.}00\%$ Mortgage contract rate$7\mathrm{.}00\%$ Amortization term in years $($monthly payments$)25$Hint Required Going in Cash on Cash Yield$12\mathrm{.}00\%$Should be approximately $=12\mathrm{.}0\%$ Required Equity Yield $($like a before tax IRR$)15\mathrm{.}00\%$before tax over holding period Debt Coverage Ratio Required$1\mathrm{.}25$ Expected Holding Period$10$in yearsExpected Growth Rate in Value Over Holding Period $34\mathrm{.}39\%$Should be approximately $=34\mathrm{.}39\%$ Implied Growth in NOI Over Holding Period$34\mathrm{.}39\%$ Optional Input: Overall Market Capitalization Rate$8\mathrm{.}50\%$ Optional Input: transactions cost for use with Ellwood$7\mathrm{.}00\%$used to adjust net appreciation When inputs are complete go to the next worksheets below for various methods Calculation of effective NOI NOI year $1$$$50,0002$$$51,5003$$$53,0454$$$54,6365$$$56,2756$$$57,9647$$$59,7038$$$61,4949$$$63,33910$$$65,239$ PV $=$ $$485,436\mathrm{.}89$ Annuity $=$ $$55,250\mathrm{.}50$Simple Two Band Mortgage Equity Technique to Derive a Cap Rate Monthly rate or termMonthly Mortgage Constant Mortgage Contract Rate$7\mathrm{.}00\%0\mathrm{.}58333333\%$ Amortization Term Years$253000\mathrm{.}007067792$ Percent Annualized Mortgage Constan Loan to Value Ratio$75\mathrm{.}00\%$times$0\mathrm{.}08481350370\mathrm{.}0636101278$Percent Cash on Cash Initial Equity Yield $($Before Tax$)$ Equity to Value Ratio$25\mathrm{.}00\%$times$12\mathrm{.}00\%0\mathrm{.}03$ R $=0\mathrm{.}0936101278$ or $=9\mathrm{.}3610\%$ NOI $=$$$50,000$ Value $=$$$534,130$THE ELLWOOD FORMULATION FOR INCOME PROPERTY APPRAISAL Initial NOI $=$ $$50,000$R $=$ Y $-$ M $*$ C $(+$ or $-$ adjustment for depreciation or appreciation$)$ Growth Rate $=3\mathrm{.}00\%$ M is the loan to value ratio Effective Constant NOI $=$$$55,250$ Annual Loan Pymts $=$$$43,435\mathrm{.}43$ USE THIS AS BASE CASE VARIABLE Resulting DCR $=1\mathrm{.}27--------$Holding Period in Years $=10$Y $=$ Equity Yield $=15\mathrm{.}0\%$ Value $=$$$682,838$M $=$ Loan $/$ Value Ratio $=75\mathrm{.}0\%$Term of Mortgage $=25$Interest Rate $=7\mathrm{.}00\%$ Expected Net Appreciation $$After Transactions Costs $24\mathrm{.}98\%$C $=$ C Coefficient $=0\mathrm{.}07571$ C$=$Y$+$P$(1/$Sn$)-$F $$f $=0\mathrm{.}04925$ SINKING FUND FACTOR F $=$ Annualized Mort Constant Y is the equity yield before tax P is the proportion of loan paid $$r $=0\mathrm{.}09321751$ "BASIC RATe $$R $=0\mathrm{.}08091301$ R $=8\mathrm{.}091\%$CAP RATE WITH $$ APPREC. EFFECT"Backing Into Equity Yield Assumptions Based on Market Cap RatesCompare this to the going in assumptions ResultUsing a simple two band method to back into an initial year equity yield $($cash on cash$)$ rate $=8\mathrm{.}56\%$ Monthly rate or termMonthly Mortgage Constant Mortgage Contract Rate$7\mathrm{.}00\%0\mathrm{.}58333\%$ Amortization Term Years$253000\mathrm{.}007067792$ Percent Annualized Mortgage Constant Loan to Value Ratio$75\mathrm{.}00\%$times$0\mathrm{.}08481350370\mathrm{.}0636101278$ Percent Equity Yield $($before tax initial$)$ Equity to Value Ratio$25\mathrm{.}00\%$times$0\mathrm{.}08560\mathrm{.}0213898722$ Overall Cap Rate $=8\mathrm{.}50\%$ NOI $=$$$50,000$ Value $=$$$588,235$Using Ellwood to back into a total annualized before tax holding period Equity Yield Basic Cap Rate$0\mathrm{.}09707475$ Use solver and set target cell to C$16$ equal to value of C$17($you must type in the value$)$ by changing C$19.$You may need to do an add in under tools if you don't see solver. Ellwood Cap Rate Target $=0\mathrm{.}085000$ Needs to match the number from market $=0\mathrm{.}085000$ Change guess to equal cap rate By Changing the Equity Yield Required here$15\mathrm{.}3857\%$If this result is significantly different than the assumed total before tax holding period yield then the assumtpions are out of sync with the market cap rate.