ct Ill - Adobe Acrobat Reader DC Help Neuroscience LSI 1... Real Estate Financia.. x 1 /3 (+ 106% Justification tial sening expenses are 470 and closing costs are 2.570 Of un purchase price. You may also assume financing with a 20-year fully amortized fixed rate loan and DCR of no less than 1.3). For this project you may assume that the NOI reported by the seller (or the implied NOI reported, given the asking price and the reported CAP) is correct and no due diligence is required. O 4 . Before-tax expected return - Using an Excel spreadsheet, determine the expected levered-before-tax-annual rate of return on your capital. Also, you need to determine the portion of the return that is expected from the annual cashflows and the portion that is expected as a result of property price appreciation. 5. Max price - Based on the scenario above, calculate the absolute maximum price you are willing to pay for the property. Obviously, you will never tell this to the seller, but it is important to have this number in mind when if you are ready to negotiate the price you pay for the property. to search O a CoFile Edit View Insert Format Data Tools Add-ons Help Last edit was 2 hours ago 100% - % .0 00 123- Default (Ca.. . 11 4 CO LL I - 175 Fontainebleau Blud Inputs: Outputs : Acquisition Price $12,500,000 Down Payment $ 4,500,000 Terminal CAP 5.94% Expected 1st Year NOI "Going in" CAP $758,750 Mortgage Rate 3.85% Loan Amount $8,000,000 6.07% Maturity 20 Mortgage Payment (Annual) $580,862 CAP over (below) market 1.94% Levered Required Return 12.00% DCR 1.31 Required Rate of Return 8.50% Minimum Required DCR 1.3 Minimum DCR Satisfied? Yes Expected NOI Growth 3.50% Expected Treasury Yield 3.50% Mortgage Balance at Sale $5,499,187 Terminal NOI Growth 2.50% Expected LT Inflation 2.50% Selling Expenses 1.00% Expected Risk Premium (bp) 400 Closing Expenses 2.50% End of year NOI Acquisition/Disposition CF PV of CF DS Levered CF PV of Levered CF ($12,812,500) $ (4,500,000) ($4,500,000) HNM tin OO ($12,812,500) ($12,812,500) $758,750 $758,750 $699,309 ($580,862) $177,888 $158,829 $785,306 $785,306 $667,083 $580,862) $204,444 $162,982 $812,792 $812,792 $636,341 ($580,862 $231,930 $165,083 $841,240 $841,240 $607,017 ($580,862) $260,378 $165,475 $870,683 $870,683 $579,044 ($580,862) $289,821 $164,452 $901,157 $901,157 $552,360 ($580,862) $320,295 $162,271 $932,697 $932,697 $526,905 ($580,862) $351,836 $159,153 $965,342 $13,687,268 $14,652,610 $7,629,166 ($6,080,048) $8,572,562 $3,462,314 Total PV: $11,897,225 Total PV: $4,600,559 NPV: -$915,275 NPV: $100,559 IRR: 7.28% IRR: 12.35% Amortization schedule: Ending balance OHNm $8,000,000 $308,000 $272,862 $7,727,138 $297,495 $283,367 $7,443,771 $286,585 $294,277 $7,149,494 Levered with CAP Selection Inf_10yr_NOI_Vac CAP100% S % .0 .00 123- Defau A B C D E F Year inflation r: 10-year T- Vacancy NOI Growth 2015 0.7% 2.13% 7.5% 5.5% 2014 1.2% 2.48% 8.6% 5.2% 4 2013 1.5% 2.36% 9.6% 3.6% 2012 1.7% 1.74% 10.4% 4.3% 2011 3.0% 2.73% 11.6% 1.9% 7 2010 1.5% 3.13% 11.4% 1.4% 8 2009 2.7% 3.28% 10.8% 0.8% g 2008 0.1% 3.60% 8.8% 3.1% 10 2007 4.1% 4.58% 7.9% 5.7% 11 2006 2.5% 4.78% 8.2% 5.4% 12 2005 3.4% 4.27% 9.3% 1.7% 13 2004 3.3% 4.26% 10.5% 14 -1.7% 2003 1.9% 4.00% 10.9% -5.0% 15 2002 2.4% 4.53% 9.2% 16 -2.7% 2001 1.6% 4.97% 6.6% 17 6.6% 2000 3.4% 5.96% 5.8% 18 9.7% 1999 2.7% 5.69% 6.1% 3.9% 19 1998 1.6% 5.20% 6.0% 5.5% 20 1997 1.7% 6.30% 6.3% 6.1% 21 1996 3.3% 6.45% 7.1% 0.6% 22 1995 2.5% 6.48% 7.5% 4.9% 23 1994 2.7% 7.12% 8.9% 3.8% 24 1993 2.7% 5.84% 10.4% 25 -0.1% 1992 2.9% 7.03% 13.0% 26 -0.1% 1991 3.1% 7.79% 13.2% 27 0.8% 1990 6.1% 8.56% 28 12.9% 3.0% 1989 4.6% 29 8.45% 12.6% 1.1% 1988 4.4% 30 8.83% 10.9% -0.1% 1987 31 4.4% 8.41% 1986 -1.8% 32 1.1% 7.57% 1.7% 33 10.54% 4.9% 34 12.49% 3.7% 11 18% 4.4% Levered with CAP Selection Inf_10yr_NOL_Vac - Type here to search100% 0 .00 123- Default (Ca.. U Purchase OD LE - Purchase Sale Inputs: Inputs: Treasury yield 1.6% Treasury vield 4.0% Inflation expectation 2.0% Inflation expectation 2.5% Total Risk premium (br 450 Total Risk premium (be 400 NOI $ 758,750 NOI $ 933,167 Outputs: Outputs: CAP-Treasury "spread" 2.50% CAP-Treasury "spread" 1.50% CAP 4.13% CAF 5.50% Multiple 24.24 Multiple 18.18 Value $ 18,393,939 Value $ 16,966,673