can someone please help me with this excel sheet?

thank you!

An Owner just purchased a 62,000 square foot office building for $15,750,000 with 60% LTV with a loan of 3.25%, 27-year term. The owner intends to hold the building for five years. The building has three floors of rentable space with a single tenant on each floor. . The first floor has 19,500 square feet of rentable space and is currently renting for $19.75 per square foot. Two years remain on the lease. The lease has an expense stop at $5.00 per square foot. The second floor has 20,500 square feet of rentable space and is leasing for $20.25 per square foot and has three years remaining on the lease. This lease has an expense has an expense stop at $5.25 per square foot. The third floor has 20,500 square feet of leasable space and a lease just signed for the next five years at a rental rate of $24.50 per square foot, which is the current market rate. The expense stop is at $6.00 per square foot, which is what expenses per square foot are estimated to be during the next year (excluding management). Each lease also has a CPI adjustment that provides for the base rent to increase at 25% of the increase in the CPI. The CPI is projected to increase 1.90 percent per year. (Please note that CPI and a rent adjustment are not the same thing. A rent adjustment is just that, a change in rent, and can be based on CPI but it is not CPI.) The market rental rate at which leases are expected to be renewed is also projected to increase 1.90 percent per year. To account for any time that may be necessary to find new tenants after current leases expire and new leases are made, vacancy is estimated to be 10 percent of total PGI for the years in which a lease renews (aka when the new lease takes effect). Management expenses are expected to be at 6 percent of effective gross income and are not included in the expense stop. Estimated operating expenses for the first year of ownership includes the following: Property taxes $ 102,000 Insurance 30,500 Utilities 93,000 Janitorial 82,000 Maintenance 64,500 Total $ 372,000 Total $ 372,000 All expenses are projected to increase 2.5 percent per year. At the time of sale, you are assuming the going out cap rate is.25% less than the going in cap rate. And that there will be 4.15% selling costs and need to pay 4.5% in commissions. Place the answers to the following questions in a highlighted box in the spreadsheet: 1. Project the net operating income (NOI) for the next five years. 2. What is the building efficiency? 3. How much does the NOI increase (compound average growth rate) over the five years? 4. What is the BTCF each year? 5. What is the overall capitalization rate ("going-in" rate)? 6. What is the sale price at the end of year five? 7. What is the reversion value after sale? 8. What is the levered IRR? 9. What is the unlevered IRR? 10. With a 12% discount rate is this a worthwhile investment? D G H J B 1 Purchase Data 2 Purchase Price $ 15,750,000.00 3 Loan Costs $ 307,125.00 4 Debt $ 9,450,000.00 5 Initial Investment $ 25,507,125.00 6 F Property Information Purchase Price $ 15,750,000.00 Total Building Area (SF) 62,000 Management Feel% of EGI) Vacancy & Loss Going-in Cap Rate Building Efficiency L . Rent Increase CPI 25% CPI Adjustment 1.90% Annual Increase 1.90% 7 8 9 Loan Information 10 Interest Rate 11 Amort Period 3.25% Operating Enpenses Property Taxes $ 102,000.00 Insurance $ 30,500.00 Utilities $ 93,000.00 Janitorial $ 82,000.00 Maintance $ 64,500.00 5 Floor SF 1st Total Rent Roll Rate Years Exp. Base Rent Left Stop/SF 19,500 $ 19.75 2 $ 5.00$ 385,125.00 20,500 $ 20.25 3 $ 5.25 $ 415,125.00 20,500 $ 24.50 5 $ 6.00 $ 502,250.00 60,500 Year 1 Market Rent $ 1,302,500.00 Term 27 12 13 LTV Option $ 9,450,000.00 14 Lender Costs 15 Annual Payment 16 17 18 Disposition Information 19 Hold Period $372,000.00 2nd 3rd Total Rentable $ 24.50 Input Data Current Market Rent Projected Increase in Market Rent Management Costs Estimated Increase in CPI Vacancy Rate Starting 4th Yr 1.9% 6.0% 1.9% 10.0% 20 Selling Costs 21 Commission Costs 22 Terminal Cap Adjust 23 24 25 26 Annual Cash Flows 27 Period 28 1st Floor 29 2nd Floor 30 3rd Floor 31 Total PGI 32 VIL 0 4 5 6 B E F G H M N 0 P ol 3 5 Year 1 3 4 5 6 385, 125.00 $385,125.00 25 26 Annual Cash Flows 27 Period 28 1st Floor 29 2nd Floor 30 3rd Floor 31 Total PGI 32 V/L 33 EGI 34 Expenses 35 OpEx 36 Management 37 Total Expenses 38 Expense Recovery 39 1st Floor 40 2nd Floor 41 3rd Floor 42 Total Exp Recovery 43 NOI 44 Debt Service 45 BTCF 46 Levered BTCF 47 Unlevered BTCF 48 49 50 le Proceeds Before Tux 51 Terminal Cap Rate 52 Sale Price 53 Selling Costs 54 Commissions 55 Debt Retirement 56 Net Value Base Income Tenant 1 $385,125.00$ Tenant 2 Tenant 3 Total Base Income (a) Return Summary Levered IRR Unlevered IRR 57 58 Sheet1 + L An Owner just purchased a 62,000 square foot office building for $15,750,000 with 60% LTV with a loan of 3.25%, 27-year term. The owner intends to hold the building for five years. The building has three floors of rentable space with a single tenant on each floor. . The first floor has 19,500 square feet of rentable space and is currently renting for $19.75 per square foot. Two years remain on the lease. The lease has an expense stop at $5.00 per square foot. The second floor has 20,500 square feet of rentable space and is leasing for $20.25 per square foot and has three years remaining on the lease. This lease has an expense has an expense stop at $5.25 per square foot. The third floor has 20,500 square feet of leasable space and a lease just signed for the next five years at a rental rate of $24.50 per square foot, which is the current market rate. The expense stop is at $6.00 per square foot, which is what expenses per square foot are estimated to be during the next year (excluding management). Each lease also has a CPI adjustment that provides for the base rent to increase at 25% of the increase in the CPI. The CPI is projected to increase 1.90 percent per year. (Please note that CPI and a rent adjustment are not the same thing. A rent adjustment is just that, a change in rent, and can be based on CPI but it is not CPI.) The market rental rate at which leases are expected to be renewed is also projected to increase 1.90 percent per year. To account for any time that may be necessary to find new tenants after current leases expire and new leases are made, vacancy is estimated to be 10 percent of total PGI for the years in which a lease renews (aka when the new lease takes effect). Management expenses are expected to be at 6 percent of effective gross income and are not included in the expense stop. Estimated operating expenses for the first year of ownership includes the following: Property taxes $ 102,000 Insurance 30,500 Utilities 93,000 Janitorial 82,000 Maintenance 64,500 Total $ 372,000 Total $ 372,000 All expenses are projected to increase 2.5 percent per year. At the time of sale, you are assuming the going out cap rate is.25% less than the going in cap rate. And that there will be 4.15% selling costs and need to pay 4.5% in commissions. Place the answers to the following questions in a highlighted box in the spreadsheet: 1. Project the net operating income (NOI) for the next five years. 2. What is the building efficiency? 3. How much does the NOI increase (compound average growth rate) over the five years? 4. What is the BTCF each year? 5. What is the overall capitalization rate ("going-in" rate)? 6. What is the sale price at the end of year five? 7. What is the reversion value after sale? 8. What is the levered IRR? 9. What is the unlevered IRR? 10. With a 12% discount rate is this a worthwhile investment? D G H J B 1 Purchase Data 2 Purchase Price $ 15,750,000.00 3 Loan Costs $ 307,125.00 4 Debt $ 9,450,000.00 5 Initial Investment $ 25,507,125.00 6 F Property Information Purchase Price $ 15,750,000.00 Total Building Area (SF) 62,000 Management Feel% of EGI) Vacancy & Loss Going-in Cap Rate Building Efficiency L . Rent Increase CPI 25% CPI Adjustment 1.90% Annual Increase 1.90% 7 8 9 Loan Information 10 Interest Rate 11 Amort Period 3.25% Operating Enpenses Property Taxes $ 102,000.00 Insurance $ 30,500.00 Utilities $ 93,000.00 Janitorial $ 82,000.00 Maintance $ 64,500.00 5 Floor SF 1st Total Rent Roll Rate Years Exp. Base Rent Left Stop/SF 19,500 $ 19.75 2 $ 5.00$ 385,125.00 20,500 $ 20.25 3 $ 5.25 $ 415,125.00 20,500 $ 24.50 5 $ 6.00 $ 502,250.00 60,500 Year 1 Market Rent $ 1,302,500.00 Term 27 12 13 LTV Option $ 9,450,000.00 14 Lender Costs 15 Annual Payment 16 17 18 Disposition Information 19 Hold Period $372,000.00 2nd 3rd Total Rentable $ 24.50 Input Data Current Market Rent Projected Increase in Market Rent Management Costs Estimated Increase in CPI Vacancy Rate Starting 4th Yr 1.9% 6.0% 1.9% 10.0% 20 Selling Costs 21 Commission Costs 22 Terminal Cap Adjust 23 24 25 26 Annual Cash Flows 27 Period 28 1st Floor 29 2nd Floor 30 3rd Floor 31 Total PGI 32 VIL 0 4 5 6 B E F G H M N 0 P ol 3 5 Year 1 3 4 5 6 385, 125.00 $385,125.00 25 26 Annual Cash Flows 27 Period 28 1st Floor 29 2nd Floor 30 3rd Floor 31 Total PGI 32 V/L 33 EGI 34 Expenses 35 OpEx 36 Management 37 Total Expenses 38 Expense Recovery 39 1st Floor 40 2nd Floor 41 3rd Floor 42 Total Exp Recovery 43 NOI 44 Debt Service 45 BTCF 46 Levered BTCF 47 Unlevered BTCF 48 49 50 le Proceeds Before Tux 51 Terminal Cap Rate 52 Sale Price 53 Selling Costs 54 Commissions 55 Debt Retirement 56 Net Value Base Income Tenant 1 $385,125.00$ Tenant 2 Tenant 3 Total Base Income (a) Return Summary Levered IRR Unlevered IRR 57 58 Sheet1 + L