You are an employee of University Consultants, Limited, and have been given the following assignment. You are to present an investment analysis of a small retail income-producing property for sale to a potential investor. The asking price for the property is $1,320,000; rents are estimated at $168,960 during the first year and are expected to grow at 2.5 percent per year thereafter. Vacancies and collection losses are expected to be 10 percent of rents. Operating expenses will be 35 percent of effective gross income. A fully amortizing 70 percent loan can be obtained at 7 percent interest for 30 years (total annual payments will be monthly payments 12). The property is expected to appreciate in value at 2 percent per year and is expected to be owned for five years and then sold.
Below I've attached my excel sheet for formatting. Thank you
Required:
a. What is the first-year debt coverage ratio? 1.34 confirmed right
b. What is the terminal capitalization rate? 7.67% possible
c. What is the investors expected before-tax internal rate of return on equity invested (BTIRR)?
d. What is the NPV using a 12 percent discount rate?
e. What is the profitability index using a 12 percent discount rate?
AutoSave UG-FIN-460-Chapters-9-11-Homework-Template.xlsx - Saved - Search (Alt+) Christian Daniel File Home Insert Page Layout Formulas Data Review View Help Power Pivot Share Comments AutoSum Calibri 11 - A A == - 2 Wrap Text General IL LEX () A X Out LCopy Paste Format Painter Ayo Sort & Find & Filter Select I U A B El Clear Insert Delete Format $ % Merye & Center v 09 Sensitivity Analyze Data Conditional Format as Cell Formatting Table Styles Styles Cipboard Font Alignment Number Cells Editing Analysis Sensitivity K40 X f H 1 L M NE B 152,064 155,866 53,222' 54,553 98,842 101,313 73.709" 73,769" 25.073 27,544 D E F 159,762 163,756 167,850 172,046 55,917' 57,315 58,748" 60,216 103,845 105,442 109,103 111,830 73,769" 73.769 73,769' 79,769 30,077 32,673 35,334 38,061 % of gross income from problem F F Based on annual appreciation during ownership period Assumer 1.34 a) See Debt Coverage Ratios In Introduction to Debt Financing section In text A 18 Effective Gross income 19 Operating Expenses 20 Net Operating Income 21 Debt Service 22 Before-Tax Cash Flow 23 24 Cash Flow from Sale - Year 3 25 Sales Price 26 Sales Expenses 27 Mortgage Balance 28 Before-Tax Cash Flow 29 30 a) Year 1 Debt Coverage Ratio 31 32 b) Terminal Capitalization Rate 33 34 ) Before-Tax IRR (BTIRR) on equity 25 Year 36 3/ 28 d) NPV-equity discount rate 39 40 le) Profitability Index 41 Present Value o discount rate 12 Initial Equity Investment 43 44 15 46 47 7.67%. b) c) 4 5 Be sure to include cash to purchase and not sales proceeds d) Fxcel discounts the first cash flow, sa add the year o cash flow to the end of the NPV function e) Use NPV function, do not include the initial investment at time 48 49 50 51 12 1,9-1 2 2.9-6 Logan 1 3.9-8 4, 10-9 5. 10-12 6, 11-2 wecke AutoSave UG-FIN-460 Chapters-9-11-Homework-Template.xlsx - Saved - Search (Alt+) Christian Daniel File Home Insert Page Layout Formulas Data Review View Help Power Pivot Share Comments AutoSum Calib -11 - A A == 2 Wrap Text Number CD XD () A X Out [Copy Paste Format Painter Cipboard 6 IL Conditional Format as Cell Formatting Table Styles Ay O Sort & Find & Filter Select BU U A + Insert Delete Format Merge & Center $ %. $9.9 El Clear Analyze Data Sensitivity Font Alignment Number Styles Cells Editing Analysis Sensitivity 836 X fi H 1 K L M NE Notes: Calculate the PV with only 25 years remaining on the loan. Grow by 3% annually % of rents from problem of gross income from problem F F F F F A B D E F 6 4 5 Key Assumptions Loan Information 6 Asking Price 1.320.000 Equity 7 Rent 168,960 Information 8 Annual Rent Growth 2.5 Loan Rate 70W 9 Vacancies and collection losses (% of rents 10% Term 30 10 Operating Expenses % of Gross Incomel 35% Payment 11 Property annual appreciation 709 % Annual Payment 12 Discount Rate 7% 13 Years old 30 Morte End 14 15 Year 1 2 3 4 5 6 16 Rental Income 168.960 173, 184 177,514 181,951 186,500 191,163 17 Vacancy & Collection Loss 16.896 17,318 17,751 18,195 18,650 19,116 18 Fffective Gross Income 157,064 155,856 159,752 163,756 167,850 177,046 19 Operating Expenses 53.222' 54,550" 55,917' 57,915' 58,748 G0,216 20 Net Operating Income 98.842 101,313 103,845 106,442 109,103 111,830 21 Debt Service 73,769' 73,759 73,759 73,769 73,769' 78,769 22 Before Tax Cash Flow 25,073 27,544 30,077 32.673 35,334 39,061 23 24 Cash Flow from Sale - Year 5 25 Salas Price 26 Sales Expenses 27 Mortgage Balance 28 Before Tax Cash Flow 29 20 ) Year 1 Debt Coverage Ratio 1.34 ) 31 32 b) Terminal Capitalization Rate 7.67% b) 23 31.) Before-Tax IRR (BTIRR) on cquity c! 35 Year 1 2 3 5 36 37 38 NPW Aut dicunt este 1,9-1 2.9-6 Logan 1 3,9-84, 10-9 5. 10-12 6, 11-2 6 Based on annual appreciation during ownership period Assume zero Sep Dehr Coverage Ratios in Introduction to Debt Financing Section in test Be sure to include cash to purchase and net sales proceeds HI. Evelies the first and to the Head of the Nortir Breda AutoSave UG-FIN-460-Chapters-9-11-Homework-Template.xlsx - Saved - Search (Alt+) Christian Daniel File Home Insert Page Layout Formulas Data Review View Help Power Pivot Share Comments AutoSum Calibri 11 - A A == - 2 Wrap Text General IL LEX () A X Out LCopy Paste Format Painter Ayo Sort & Find & Filter Select I U A B El Clear Insert Delete Format $ % Merye & Center v 09 Sensitivity Analyze Data Conditional Format as Cell Formatting Table Styles Styles Cipboard Font Alignment Number Cells Editing Analysis Sensitivity K40 X f H 1 L M NE B 152,064 155,866 53,222' 54,553 98,842 101,313 73.709" 73,769" 25.073 27,544 D E F 159,762 163,756 167,850 172,046 55,917' 57,315 58,748" 60,216 103,845 105,442 109,103 111,830 73,769" 73.769 73,769' 79,769 30,077 32,673 35,334 38,061 % of gross income from problem F F Based on annual appreciation during ownership period Assumer 1.34 a) See Debt Coverage Ratios In Introduction to Debt Financing section In text A 18 Effective Gross income 19 Operating Expenses 20 Net Operating Income 21 Debt Service 22 Before-Tax Cash Flow 23 24 Cash Flow from Sale - Year 3 25 Sales Price 26 Sales Expenses 27 Mortgage Balance 28 Before-Tax Cash Flow 29 30 a) Year 1 Debt Coverage Ratio 31 32 b) Terminal Capitalization Rate 33 34 ) Before-Tax IRR (BTIRR) on equity 25 Year 36 3/ 28 d) NPV-equity discount rate 39 40 le) Profitability Index 41 Present Value o discount rate 12 Initial Equity Investment 43 44 15 46 47 7.67%. b) c) 4 5 Be sure to include cash to purchase and not sales proceeds d) Fxcel discounts the first cash flow, sa add the year o cash flow to the end of the NPV function e) Use NPV function, do not include the initial investment at time 48 49 50 51 12 1,9-1 2 2.9-6 Logan 1 3.9-8 4, 10-9 5. 10-12 6, 11-2 wecke AutoSave UG-FIN-460 Chapters-9-11-Homework-Template.xlsx - Saved - Search (Alt+) Christian Daniel File Home Insert Page Layout Formulas Data Review View Help Power Pivot Share Comments AutoSum Calib -11 - A A == 2 Wrap Text Number CD XD () A X Out [Copy Paste Format Painter Cipboard 6 IL Conditional Format as Cell Formatting Table Styles Ay O Sort & Find & Filter Select BU U A + Insert Delete Format Merge & Center $ %. $9.9 El Clear Analyze Data Sensitivity Font Alignment Number Styles Cells Editing Analysis Sensitivity 836 X fi H 1 K L M NE Notes: Calculate the PV with only 25 years remaining on the loan. Grow by 3% annually % of rents from problem of gross income from problem F F F F F A B D E F 6 4 5 Key Assumptions Loan Information 6 Asking Price 1.320.000 Equity 7 Rent 168,960 Information 8 Annual Rent Growth 2.5 Loan Rate 70W 9 Vacancies and collection losses (% of rents 10% Term 30 10 Operating Expenses % of Gross Incomel 35% Payment 11 Property annual appreciation 709 % Annual Payment 12 Discount Rate 7% 13 Years old 30 Morte End 14 15 Year 1 2 3 4 5 6 16 Rental Income 168.960 173, 184 177,514 181,951 186,500 191,163 17 Vacancy & Collection Loss 16.896 17,318 17,751 18,195 18,650 19,116 18 Fffective Gross Income 157,064 155,856 159,752 163,756 167,850 177,046 19 Operating Expenses 53.222' 54,550" 55,917' 57,915' 58,748 G0,216 20 Net Operating Income 98.842 101,313 103,845 106,442 109,103 111,830 21 Debt Service 73,769' 73,759 73,759 73,769 73,769' 78,769 22 Before Tax Cash Flow 25,073 27,544 30,077 32.673 35,334 39,061 23 24 Cash Flow from Sale - Year 5 25 Salas Price 26 Sales Expenses 27 Mortgage Balance 28 Before Tax Cash Flow 29 20 ) Year 1 Debt Coverage Ratio 1.34 ) 31 32 b) Terminal Capitalization Rate 7.67% b) 23 31.) Before-Tax IRR (BTIRR) on cquity c! 35 Year 1 2 3 5 36 37 38 NPW Aut dicunt este 1,9-1 2.9-6 Logan 1 3,9-84, 10-9 5. 10-12 6, 11-2 6 Based on annual appreciation during ownership period Assume zero Sep Dehr Coverage Ratios in Introduction to Debt Financing Section in test Be sure to include cash to purchase and net sales proceeds HI. Evelies the first and to the Head of the Nortir Breda
