Create a VBA model to answer the above questions.

o Q A B D E F G H I K L M N P R 1 Build a model to determine the value of an apartment complex. 2 The fixed assumptions, listed on the "Your Model" tab, provide the necessary inputs to your valuation model. 3 Begin by modeling all of the property costs for January 2019 through December 2024 (the latest possible date you will sell the property). 4 Assume that all cash flows (costs and income) occur at the end of the month, so the first flows (other than the purchase) will occur on January 31, 2019 5 Note that insurance costs occur annually in January, taxes are paid annual in April, maintenance is paid quarterly (Mar, Jun, Sept, Dec) 6 and is subject to inflation (rate given as EAR), and management costs are paid monthly based on the occupancy rate 7 Next, model the property income from apartment and townhouse rents (assuming 100% occupancy, but allowing for other possibilities). 8 Note that the rents for apartments and townhouses increase at different rates, increase annually, and the rate increases occur in different months. 9 If the increase month is January, then increase the rent for January 31, 2019 10 Through question 12, base all calculations on January 31, 2019 through December 31, 2024. 11 Beginning with question 13, account for the planned sale date (which is given as an input). 12 The sales price will be based on the purchase price plus anticipated appreciation, which is given as an APR. 13 Given the purchase price, sales price and cash flows, use the discount rates given to calculate the NPV of the project (assuming 100% occupancy). 14 Finally, construct a data table to analyze how NPV changes as the occupancy rates range from 0% to 100% (in 10% intervals). 15 The data table will need to be two-way, to allow for different year 1 - 3 and year 4 - 6 occupancy rates. 16 Be sure to answer questions 17 - 20 by referencing your data table in order to perserve the answers to questions 1 - 16. 17 18 All of the model inputs are given on the "Your Model" tab. Note that the parameter values are randomly generated and are linked to the "Model Answer Key" tab (which is protected). 19 Do not change those links, as doing so will disable your check values. 20 To lock the parameter values, hit the "Lock Values" macro button below. If you would like to restore the random values, use the "Reset Values" button. 21 As you build your model, keep an eye on the questions tab, as you are required to use your model to answer twenty questions. 22 Each of the questions should be answered with a link to your model or a formula that references your model. 23 In each case, you can check your answers as you go based on the answer given on the Questions tab. 24 To get accurate values for questions 17 - 20, be sure to lock values and hit F9 to recalculate/update the data tables. 25 26 27 28 Lock Values Reset Values 29 30 31 32 33 $9,900,000 1.4% 1.4% 0.2% 1.9% $65 64 $1,460 8% 11 A B 1 Fixed Assumptions (Do Not Change) 2 Property Costs 3 Purchase Price 4 Annual insurance (January - % of Purchase Price) 5 Annual taxes (April - % of Purchase Price) 6 Quarterly Maintenance (Mar, Jun, Sept, Dec - % of Purchase Price) 7 Annual Maintenance Inflation Rate (EAR) 8 Monthly Management (per unit) 9 10 Property Income 11 Apartment Units 12 Apartment Monthly Rent 13 Apartment Rent Growth Rate 14 Apartment Rent Increase Month 15 Townhouse Units 16 Townhouse Monthly Rent 17 Townhouse Rent Growth Rate 18 Townhouse Rent Increase Month 19 20 Other Assumptions 21 Discount rate: Years 1 - 3 (EAR) 22 Discount rate: Years 4 - 6 (EAR) 23 Annual Appreciation (APR) 24 Sales Date (months since purchase) 25 Appreciation Lookup Date 26 27 Variable Assumptions (You Can Change) 28 Occupancy Rate: Years 1 - 3 29 Occupancy Rate: Years 4-6 30 31 22 $2,210 10% 8 14% 9% 2% 67 4/30/2024 100% 100% o Q A B D E F G H I K L M N P R 1 Build a model to determine the value of an apartment complex. 2 The fixed assumptions, listed on the "Your Model" tab, provide the necessary inputs to your valuation model. 3 Begin by modeling all of the property costs for January 2019 through December 2024 (the latest possible date you will sell the property). 4 Assume that all cash flows (costs and income) occur at the end of the month, so the first flows (other than the purchase) will occur on January 31, 2019 5 Note that insurance costs occur annually in January, taxes are paid annual in April, maintenance is paid quarterly (Mar, Jun, Sept, Dec) 6 and is subject to inflation (rate given as EAR), and management costs are paid monthly based on the occupancy rate 7 Next, model the property income from apartment and townhouse rents (assuming 100% occupancy, but allowing for other possibilities). 8 Note that the rents for apartments and townhouses increase at different rates, increase annually, and the rate increases occur in different months. 9 If the increase month is January, then increase the rent for January 31, 2019 10 Through question 12, base all calculations on January 31, 2019 through December 31, 2024. 11 Beginning with question 13, account for the planned sale date (which is given as an input). 12 The sales price will be based on the purchase price plus anticipated appreciation, which is given as an APR. 13 Given the purchase price, sales price and cash flows, use the discount rates given to calculate the NPV of the project (assuming 100% occupancy). 14 Finally, construct a data table to analyze how NPV changes as the occupancy rates range from 0% to 100% (in 10% intervals). 15 The data table will need to be two-way, to allow for different year 1 - 3 and year 4 - 6 occupancy rates. 16 Be sure to answer questions 17 - 20 by referencing your data table in order to perserve the answers to questions 1 - 16. 17 18 All of the model inputs are given on the "Your Model" tab. Note that the parameter values are randomly generated and are linked to the "Model Answer Key" tab (which is protected). 19 Do not change those links, as doing so will disable your check values. 20 To lock the parameter values, hit the "Lock Values" macro button below. If you would like to restore the random values, use the "Reset Values" button. 21 As you build your model, keep an eye on the questions tab, as you are required to use your model to answer twenty questions. 22 Each of the questions should be answered with a link to your model or a formula that references your model. 23 In each case, you can check your answers as you go based on the answer given on the Questions tab. 24 To get accurate values for questions 17 - 20, be sure to lock values and hit F9 to recalculate/update the data tables. 25 26 27 28 Lock Values Reset Values 29 30 31 32 33 $9,900,000 1.4% 1.4% 0.2% 1.9% $65 64 $1,460 8% 11 A B 1 Fixed Assumptions (Do Not Change) 2 Property Costs 3 Purchase Price 4 Annual insurance (January - % of Purchase Price) 5 Annual taxes (April - % of Purchase Price) 6 Quarterly Maintenance (Mar, Jun, Sept, Dec - % of Purchase Price) 7 Annual Maintenance Inflation Rate (EAR) 8 Monthly Management (per unit) 9 10 Property Income 11 Apartment Units 12 Apartment Monthly Rent 13 Apartment Rent Growth Rate 14 Apartment Rent Increase Month 15 Townhouse Units 16 Townhouse Monthly Rent 17 Townhouse Rent Growth Rate 18 Townhouse Rent Increase Month 19 20 Other Assumptions 21 Discount rate: Years 1 - 3 (EAR) 22 Discount rate: Years 4 - 6 (EAR) 23 Annual Appreciation (APR) 24 Sales Date (months since purchase) 25 Appreciation Lookup Date 26 27 Variable Assumptions (You Can Change) 28 Occupancy Rate: Years 1 - 3 29 Occupancy Rate: Years 4-6 30 31 22 $2,210 10% 8 14% 9% 2% 67 4/30/2024 100% 100%