The asking price is $3.5M, with land valued at $500,000. b. The 160 apartment units rent for $450 per month with rent expected to increase by 4% per year starting at year 2. c. Vacancy and collection loss allowance is 6% of the potential gross income. d. Operating expenses are expected to be 32% of effective gross income. e. The real estate agent estimates that the value of the property, net of selling expenses, will be $4.4M at the end of a five-year investment horizon. f. A 12%, 20-year mortgage for $2M is available with monthly payments. g. The cost recovery allowance recapture rate is 25%. h. The investors ordinary income tax rate is 28%. i. The investors capital gain tax rate is 15%. j. The appropriate discount rate for this investment (required return) is 18%. Calculate the relevant cash flows for this investment and apply the NPV and IRR rules to decide whether to pursue this project. (Please check my work I do not know what is wrong, the rest is automatic I just do not know which of my inputs is wrong or missing (whats in blue))
File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share X General Calibri = 14 AA # Insert bu C ab 47 o Fm $ % ) y Paste DX Delete AY A = = Conditional Formatting Format as Table Cell Styles Styles Sort & Find & Filter Select Analyze Sensitivity Data 60 .00 Format Undo Clipboard Font Alignment Number Cells Editing Analysis Sensitivity L24 Vi X fx 112 A B C D E F G G H 1 J L M M N 3 (starting at year 2) DON'T NEED YR 6 FOR YOUR PROJECT DON'T NEED YR 6 FOR YOUR PROJECT ZERO FOR YOUR PROJECT 4 Assumptions 5 Acquisition Price $ $ 3,000,000 6 Unit details 7 7 160 $ 450 per month 8 8 Market rent increases 4% per year 9 Vacancy Loss 6% per year 10 Operating Expenses 32% of EGI 11 Capital Expenditures 0% of EGI 12 Assume: : Above-the line CAPX 13 Holding period 5 years 14 Expected selling price in Yr5 Yr 6 NOI @ 6 15 Selling expenses in Yr5 0% of sale price 16 LTV 75% $ 2,250,000 17 Mortgage rate 12.0% 18 Amortization period 20 years 19 Up-front financing costs 0% of loan amount 20 Assumed Ordinary Income tax rate 28% 21 Assumed Capital Gain tax rate 15% 22 Assumed Depreciation Recapture tax rate 25% 23 Depreciable rate for real property 80% $ 2,400,000 24 Depreciable years 27.5 years 25 26 Analysis Year 0 1 L PP (2) pp Monthly Amort Table_75%LTV + Ready Accessibility: Investigate * $ ZERO FOR YOUR PROJECT DON'T NEED YR 6 FOR YOUR PROJECT 6 2 2 3 4 5 GCAAA 5 + 70% File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share X Calibri AA General == 14 Insert ale WE 47 O $ % ) DX Delete @ IN Analyze Data Paste U A Conditional Formatting Format as Table Cell Styles Styles == = += = v Sensitivity 40 .00 Sort & Find & Filter Select Format Undo Clipboard Font Alignment Number Cells Editing Analysis Sensitivity L24 1 X fx 112 A B C D E F G H 1 J K L M M N N 24 Depreciable years 27.5 years 25 26 DON'T NEED YR 6 FOR YOUR PROJECT 6 6 1 2 2 3 4 0 (750,000) 5 $ Analysis Year 27 Initial Equity Investment 28 Potential Gross Income 29 - Vacancy and collection losses 30 = Effective Gross Income 31 - Operating Expenses 32 - Capital Expenditures = Net Operating Income $ $ $ $ $ 864,000 $ (51,840) S 812.160 $ (259,891) $ $ 552,269 $ 898,560 $ (53,914) $ 844,646 $ (270,287) $ $ 574,360 $ 934,502 $ (56,070) $ 878,432 $ (281,098) $ $ 597,334 $ 971,882 S (58,313) $ 913,570 $ (292.342) $ $ $ 621,227 $ 1,010,758 $ (60,645) $ 950,112 $ (304,036) $ $ 646,076 $ 1,051,188 (63,071) 988,117 ( (316,197) $ 33 $ 671,919 39 40 41 $ $ 42 Monthly payment PV S FV $ PMT S N N 1/Y Net Loan Proceeds 2,250,000 Loan Financing fees (24,774.44) 240 6.5/12 Required Equity 2,250,000 Acquisition Price $ Net Loan Proceeds $ 2,250,000 $ 3,000,000 (2,250,000) 750,000 43 S 44 45 46 47 Sale Price 48 - Selling expenses 49 = Net sale proceeds 50 - Remaining mortgage balance 51 = Before-tax equity reversion (BTER) #DIV/0! #DIV/0! #DIV/0! (741,399) at EOY5 (month 60 on amortization table) #DIV/0! $ 52 PP (2) PP Monthly Amort Table_75%LTV || + Ready Accessibility: Investigate + + 70% File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share X General Calibri = 14 AA # Insert bu C ab 47 o Fm $ % ) y Paste DX Delete AY A = = Conditional Formatting Format as Table Cell Styles Styles Sort & Find & Filter Select Analyze Sensitivity Data 60 .00 Format Undo Clipboard Font Alignment Number Cells Editing Analysis Sensitivity L24 Vi X fx 112 A B C D E F G G H 1 J L M M N 3 (starting at year 2) DON'T NEED YR 6 FOR YOUR PROJECT DON'T NEED YR 6 FOR YOUR PROJECT ZERO FOR YOUR PROJECT 4 Assumptions 5 Acquisition Price $ $ 3,000,000 6 Unit details 7 7 160 $ 450 per month 8 8 Market rent increases 4% per year 9 Vacancy Loss 6% per year 10 Operating Expenses 32% of EGI 11 Capital Expenditures 0% of EGI 12 Assume: : Above-the line CAPX 13 Holding period 5 years 14 Expected selling price in Yr5 Yr 6 NOI @ 6 15 Selling expenses in Yr5 0% of sale price 16 LTV 75% $ 2,250,000 17 Mortgage rate 12.0% 18 Amortization period 20 years 19 Up-front financing costs 0% of loan amount 20 Assumed Ordinary Income tax rate 28% 21 Assumed Capital Gain tax rate 15% 22 Assumed Depreciation Recapture tax rate 25% 23 Depreciable rate for real property 80% $ 2,400,000 24 Depreciable years 27.5 years 25 26 Analysis Year 0 1 L PP (2) pp Monthly Amort Table_75%LTV + Ready Accessibility: Investigate * $ ZERO FOR YOUR PROJECT DON'T NEED YR 6 FOR YOUR PROJECT 6 2 2 3 4 5 GCAAA 5 + 70% File Home Insert Draw Page Layout Formulas Data Review View Help Comments Share X Calibri AA General == 14 Insert ale WE 47 O $ % ) DX Delete @ IN Analyze Data Paste U A Conditional Formatting Format as Table Cell Styles Styles == = += = v Sensitivity 40 .00 Sort & Find & Filter Select Format Undo Clipboard Font Alignment Number Cells Editing Analysis Sensitivity L24 1 X fx 112 A B C D E F G H 1 J K L M M N N 24 Depreciable years 27.5 years 25 26 DON'T NEED YR 6 FOR YOUR PROJECT 6 6 1 2 2 3 4 0 (750,000) 5 $ Analysis Year 27 Initial Equity Investment 28 Potential Gross Income 29 - Vacancy and collection losses 30 = Effective Gross Income 31 - Operating Expenses 32 - Capital Expenditures = Net Operating Income $ $ $ $ $ 864,000 $ (51,840) S 812.160 $ (259,891) $ $ 552,269 $ 898,560 $ (53,914) $ 844,646 $ (270,287) $ $ 574,360 $ 934,502 $ (56,070) $ 878,432 $ (281,098) $ $ 597,334 $ 971,882 S (58,313) $ 913,570 $ (292.342) $ $ $ 621,227 $ 1,010,758 $ (60,645) $ 950,112 $ (304,036) $ $ 646,076 $ 1,051,188 (63,071) 988,117 ( (316,197) $ 33 $ 671,919 39 40 41 $ $ 42 Monthly payment PV S FV $ PMT S N N 1/Y Net Loan Proceeds 2,250,000 Loan Financing fees (24,774.44) 240 6.5/12 Required Equity 2,250,000 Acquisition Price $ Net Loan Proceeds $ 2,250,000 $ 3,000,000 (2,250,000) 750,000 43 S 44 45 46 47 Sale Price 48 - Selling expenses 49 = Net sale proceeds 50 - Remaining mortgage balance 51 = Before-tax equity reversion (BTER) #DIV/0! #DIV/0! #DIV/0! (741,399) at EOY5 (month 60 on amortization table) #DIV/0! $ 52 PP (2) PP Monthly Amort Table_75%LTV || + Ready Accessibility: Investigate + + 70%
