INTRODUCTION You are working for a company which is considering purchasing a number of properties. You have been asked to model each of the available investments to assist in choosing a portfolio (up to a maximum purchase price of $1,700,000) that maximizes the value to the company, as measured by an increase in net present value. The company's cost of capital is 8%. AVAILABLE INVESTMENTS . . Full details of the investments may be found on the table below The model should be monthly. For NPV purposes assume that all payments occur at the end of the month and use the XNPV function. The purchase price for each property should be paid on 31 December 2017. The company holds the property for a number of years (the investment length). During the investment length, the company receives rental revenue and pays operating costs. Where amounts are indexed the base date is 1 January 2018 and the index should step annually (i.e. a full year of indexation should first be applied on 1 January 2019). DO NOT round inflated prices to whole cents in interim calculations. At the end of the investment length, the company will sell the property for the terminal value. The terminal value is not indexed. For property 4, the company has the option of overhauling the property. O Details of the property without overhaul are listed under property 4a, O Details of the property with overhaul are listed under property 4b. The overhaul cost should not be considered in the purchase price constraint. o It is NOT possible to invest in both property 4a and property 4b. o The overhaul cost (which is not indexed). INVESTMENT DETAILS Property 1 $450,000 Property 2 $550,000 Property 3 $500,000 Property 4a $470,000 Property 4b As 4a Purchase Price 5 years 5.5 years 6 years 4 years As 4a Investment length Overhaul cost N/A N/A N/A N/A $125,000 paid 31 Dec 2019 Terminal value $500,000 $575,000 $550,000 $570,000 $675,000 Rental revenue $45,000 per year Paid monthly $60,000 per year Paid quarterly (starting March) $55,000 per year Paid quarterly (starting January) $55,000 per year Paid monthly Up to overhaul as 4a Afterwards: $75,000 per year Paid monthly Indexed at 2.5% Not indexed Indexed at 3% Indexed at 2% Not indexed Up to overhaul as Operating costs 5% of revenues $4,500 per year Paid monthly $1,000 in April $3,000 in October $3,000 per year Paid monthly Indexed at 3% Indexed at 2% Afterwards: 8% of revenue Indexed at 1% Question 7 What is the NPV value of Property 4a less the NPV value of Property 4b? If 4b is greater in value enter a negative number. Please enter your answer to the nearest dollar (e.g. $1,000) Question 8 Which property is highest in NPV value? A. Property 1 B. Property 2 C. Property 3 D. Property 4a E. Property 4b Question 9 Which properties should the company invest in subject to the constraint on purchase price in order to maximize their increase in net present value? State your answer in numerical order separated by commas and with NO spaces (e.g. 1,4b) Question 10 If the company's cost of capital was 4% what is the maximum portfolio NPV value that can be achieved subject to the constraint on total purchase price? Please enter your answer to the nearest dollar (e.g. $1,000). Question 11 If the revenues for all properties were paid monthly, what would be the total NPV (at 8%) of Properties 1, 2 and 3? A. $268,662 B. $268,663 C. $268,664 D. $268,665 E. $268,666 F. $268,667 G. $268,668 H. $268,669 1. $268,670 INTRODUCTION You are working for a company which is considering purchasing a number of properties. You have been asked to model each of the available investments to assist in choosing a portfolio (up to a maximum purchase price of $1,700,000) that maximizes the value to the company, as measured by an increase in net present value. The company's cost of capital is 8%. AVAILABLE INVESTMENTS . . Full details of the investments may be found on the table below The model should be monthly. For NPV purposes assume that all payments occur at the end of the month and use the XNPV function. The purchase price for each property should be paid on 31 December 2017. The company holds the property for a number of years (the investment length). During the investment length, the company receives rental revenue and pays operating costs. Where amounts are indexed the base date is 1 January 2018 and the index should step annually (i.e. a full year of indexation should first be applied on 1 January 2019). DO NOT round inflated prices to whole cents in interim calculations. At the end of the investment length, the company will sell the property for the terminal value. The terminal value is not indexed. For property 4, the company has the option of overhauling the property. O Details of the property without overhaul are listed under property 4a, O Details of the property with overhaul are listed under property 4b. The overhaul cost should not be considered in the purchase price constraint. o It is NOT possible to invest in both property 4a and property 4b. o The overhaul cost (which is not indexed). INVESTMENT DETAILS Property 1 $450,000 Property 2 $550,000 Property 3 $500,000 Property 4a $470,000 Property 4b As 4a Purchase Price 5 years 5.5 years 6 years 4 years As 4a Investment length Overhaul cost N/A N/A N/A N/A $125,000 paid 31 Dec 2019 Terminal value $500,000 $575,000 $550,000 $570,000 $675,000 Rental revenue $45,000 per year Paid monthly $60,000 per year Paid quarterly (starting March) $55,000 per year Paid quarterly (starting January) $55,000 per year Paid monthly Up to overhaul as 4a Afterwards: $75,000 per year Paid monthly Indexed at 2.5% Not indexed Indexed at 3% Indexed at 2% Not indexed Up to overhaul as Operating costs 5% of revenues $4,500 per year Paid monthly $1,000 in April $3,000 in October $3,000 per year Paid monthly Indexed at 3% Indexed at 2% Afterwards: 8% of revenue Indexed at 1% Question 7 What is the NPV value of Property 4a less the NPV value of Property 4b? If 4b is greater in value enter a negative number. Please enter your answer to the nearest dollar (e.g. $1,000) Question 8 Which property is highest in NPV value? A. Property 1 B. Property 2 C. Property 3 D. Property 4a E. Property 4b Question 9 Which properties should the company invest in subject to the constraint on purchase price in order to maximize their increase in net present value? State your answer in numerical order separated by commas and with NO spaces (e.g. 1,4b) Question 10 If the company's cost of capital was 4% what is the maximum portfolio NPV value that can be achieved subject to the constraint on total purchase price? Please enter your answer to the nearest dollar (e.g. $1,000). Question 11 If the revenues for all properties were paid monthly, what would be the total NPV (at 8%) of Properties 1, 2 and 3? A. $268,662 B. $268,663 C. $268,664 D. $268,665 E. $268,666 F. $268,667 G. $268,668 H. $268,669 1. $268,670
