Utilizes the same directions as Castles in the Air from ModelOff listed under Module 4 INTRODUCTION All the inputs mentioned below are provided in the workbook for this case study. 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 maximises 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. Note: NPV using the NPV function calculates all payments as occuring at the start of the month
• • 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.

 Details of the property without overhaul are listed under property 

1. 4a, Details of the property with overhaul are listed under property
2. 4b. The overhaul cost should not be considered in the purchase price constraint. It is NOT possible to invest in both property 4a and property 4b. 

The overhaul cost (which is not indexed). Investment Details 

Property 1 Property 2 Property 3 Property 4

A Property 4B Purchase Price $350,000 $600,000 $475,000 $535,000 

As 4A Investment Length 5 years 5.5 years 6 years 4years 4 years 

Overhaul Cost N/A N/A N/A N/A $200,000 

Terminal Value $425,000 $745,000 $550,000 $625,000 $850,000 

Rental Revenue $50,000 per year, paid monthly, indexed at 2.5% $75,000 per year, paid quarterly (starting in March), indexed at 3% $45,000 per year, paid quarterly (starting in January), indexed at 2% $60,000 per year, paid monthly, not indexed 

Before overhaul is same as 4A; After overhaul is $75,000 per year paid monthly, not indexed Operating Costs 5% of revenue $6000 per year, paid monthly, indexed at 3% $2,500 in April, $4,000 in October, indexed at 2% $5,000 per year, paid monthly, indexed at 1% Up to overhaul is same as 4A; after overhaul is 8% of revenues Questions: 

1. What are the total revenues for Property 1? 
2. What are the revenues for Property 2 in September 2019? 
3. What are the costs for property 3 in October 2020? 
4. What are total revenues less total costs for Property 4a? 
5. What is the absolute value of difference in operating costs between properties 4a and 4b?
6. Using the provided data, calculate the NPV for each project. 
7. What is the NPV of Property 4a less the NPV values of property 4b? 
8. Which properties should the company invest in subject to the constraint on purchase price in order