if you use excel, please show the functions or formulars
You are considering the purchase of a set of three flex industrial properties with a total square footage of 26,274 SF in Wynwood to add to an existing property portfolio. You expect to receive a rent-roll of $40/SF/Yr on average during the first year with a base rent increase of 1% built into the leases of all tenants. The first year, you have an in-place tenant in one of the properties and you expect the other two to be vacant resulting in an expected vacancy rate of 60% in the first year. You hope to lease-up the remaining units over the course of your holding period. You expect the vacancy rate to decrease by 10% each year. All the leases are triple net (NNN) and your holding period is five years (2021-2025). In the first year, CAM will cost $300,000, property insurance will cost $65,000, property taxes are expected to cost $600,000. You forecast that each of these expenses will increase by 2% each year. Additionally, you outsourced property management to a private firm that has agreed to manage your property at a cost of $8/SF/yr managed. Toshiba You will receive $20,000 a year in ancillary income from renting out parking spaces located on your lot. You expect irregular expenses as forecast below. Reversion value in 2025 will be estimated using 2026 NOI discounted at an exit cap rate of 5%. Suppose that the listed market price of this property is $12m and your required rate of return is 10%. Irregular 2021 2022 2023 2024 2025 Expenses Capex $5,000 $0 $0 $25,000 $20,000 Tenant $0 $60,000 $20,000 $0 $10,000 Improvements Leasing $0 $50,000 $20,000 $15,000 $5,000 Commissions Part! Given a 100% equity investment, would you make the purchase? a) Compute NOI for years 2021-2025 b) Compute the reversion value in year 2025 c) Compute the NOI-based present value of this property using 9% as your required rate of return. d) Compute the IRR of this investment provided a purchase price of $12 million e) Using your answers from a-c, discuss whether you would make the purchase provided you do not want to take any debt. 1-2 sentences will suffice. You can write this discussion directly on the excel sheet in a text box. Part II Now suppose you want to take out a 60% LTV mortgage to purchase this property. The terms of the loan are as follows: - 30-year amortization schedule - 4% per year - Constant payment mortgage (monthly payments) - Origination fee of 1% that you decide to borrow on top of contract balance - Prepayment penalty of 1% a) Fill in the amortization schedule using the mortgage template provided in the excel workbook. a. What is the contract balance? b. What is the disbursement amount? c. What is the beginning loan balance on which the payments will be calculated? d. Compute monthly payment amount. b) Fill in the debt service payments in the proforma. Remember that the mortgage payments are monthly payments and the proforma is in annual terms. (Hint: What is your total annual debt service payment for each year?] c) Compute the equity before tax cash flows (EBTCF). d) Given a $12m purchase price and a 60% LTV loan, how much equity is required to purchase this property? e) Compute your required return to equity using the IRR from Part 1 (d) as your return to the property and 4% as your cost of debt. Assume that there are no taxes. (Hint: WACC) ) Using the required return to equity computed above, compute the present value of equity cash flows (EBTCF). e) Given the parameters above, is this a profitable equity investment? What is your levered equity return (levered IRR)? Discuss whether you would make this equity investment given a 60% LTV mortgage. 1-2 sentences will suffice. h) EXTRA CREDIT What is your levered equity return if you increase the LTV to 70%? You are considering the purchase of a set of three flex industrial properties with a total square footage of 26,274 SF in Wynwood to add to an existing property portfolio. You expect to receive a rent-roll of $40/SF/Yr on average during the first year with a base rent increase of 1% built into the leases of all tenants. The first year, you have an in-place tenant in one of the properties and you expect the other two to be vacant resulting in an expected vacancy rate of 60% in the first year. You hope to lease-up the remaining units over the course of your holding period. You expect the vacancy rate to decrease by 10% each year. All the leases are triple net (NNN) and your holding period is five years (2021-2025). In the first year, CAM will cost $300,000, property insurance will cost $65,000, property taxes are expected to cost $600,000. You forecast that each of these expenses will increase by 2% each year. Additionally, you outsourced property management to a private firm that has agreed to manage your property at a cost of $8/SF/yr managed. Toshiba You will receive $20,000 a year in ancillary income from renting out parking spaces located on your lot. You expect irregular expenses as forecast below. Reversion value in 2025 will be estimated using 2026 NOI discounted at an exit cap rate of 5%. Suppose that the listed market price of this property is $12m and your required rate of return is 10%. Irregular 2021 2022 2023 2024 2025 Expenses Capex $5,000 $0 $0 $25,000 $20,000 Tenant $0 $60,000 $20,000 $0 $10,000 Improvements Leasing $0 $50,000 $20,000 $15,000 $5,000 Commissions Part! Given a 100% equity investment, would you make the purchase? a) Compute NOI for years 2021-2025 b) Compute the reversion value in year 2025 c) Compute the NOI-based present value of this property using 9% as your required rate of return. d) Compute the IRR of this investment provided a purchase price of $12 million e) Using your answers from a-c, discuss whether you would make the purchase provided you do not want to take any debt. 1-2 sentences will suffice. You can write this discussion directly on the excel sheet in a text box. Part II Now suppose you want to take out a 60% LTV mortgage to purchase this property. The terms of the loan are as follows: - 30-year amortization schedule - 4% per year - Constant payment mortgage (monthly payments) - Origination fee of 1% that you decide to borrow on top of contract balance - Prepayment penalty of 1% a) Fill in the amortization schedule using the mortgage template provided in the excel workbook. a. What is the contract balance? b. What is the disbursement amount? c. What is the beginning loan balance on which the payments will be calculated? d. Compute monthly payment amount. b) Fill in the debt service payments in the proforma. Remember that the mortgage payments are monthly payments and the proforma is in annual terms. (Hint: What is your total annual debt service payment for each year?] c) Compute the equity before tax cash flows (EBTCF). d) Given a $12m purchase price and a 60% LTV loan, how much equity is required to purchase this property? e) Compute your required return to equity using the IRR from Part 1 (d) as your return to the property and 4% as your cost of debt. Assume that there are no taxes. (Hint: WACC) ) Using the required return to equity computed above, compute the present value of equity cash flows (EBTCF). e) Given the parameters above, is this a profitable equity investment? What is your levered equity return (levered IRR)? Discuss whether you would make this equity investment given a 60% LTV mortgage. 1-2 sentences will suffice. h) EXTRA CREDIT What is your levered equity return if you increase the LTV to 70%
