The subject property is a three-tenant retail center, 50,000SF total, with a grocery store anchor. All rents and expenses are expressed on an annual basis. Grocery: 40,000SF Tenant A: 6,000SF Tenant B: 4,000 SF Scheduled Rents Grocery pays on a straight percentage basis plus reimbursable expenses, but they are obligated to pay a minimum rent of $12.50/SF per year starting in Year 1 . The guaranteed minimum increases $0.50/SF/ year each year. Otherwise, rent is 3% of all gross retail sales. The grocer will pay whichever amount is the highest - guaranteed minimum or 3% of gross retail sales. In either case, they also reimburse the owner for specified expenses. Gross retail sales are $20MM per year in Year 1 , increase by $2 MM per year over years 26, then stay flat. Tenants A and B pay a Year 1 rent of $25/SF/ year plus reimbursable expenses. Their contract rents increase by 50c/SF each year. Rent bumps up to Market Rent on renewal or replacement with a new tenant. Market Rent Tenant spaces A and B:$25/SF in Year 1. Market rent increases by 3% per year (round each year's total market rent to nearest $1 ). Grocery space: N/A. Not applicable to the problem due to long-term lease with national credit tenant. Lease Renewals / Tenant Turnover Grocery lease expires in 20 years. Tenant A: Renews at market at beginning of Year 8 Tenant B: Lease terminates in the middle of Year 6 . The space stays vacant for 6 months. New tenant signs a lease at the end of Year 6 and pays market rent starting at beginning of Year 7. Expense Reimbursements Each tenant pays a pro rata share of property taxes, insurance, common area maintenance, and an Expense Reimbursements Each tenant pays a pro rata share of property taxes, insurance, common area maintenance, and an administrative fee of 7% on top of that amount. Pro rata share for each tenant space is proportional to the tenant's net leasable area. Each tenant pays the same percentage of expense as its percentage of the Net Leasable Area. Show Reimbursements as a one line summary for each year, including Year 11. Vacancy and Credit Loss - Nominal 2% of PGI for each year Vacancy and Credit Loss - Actual Show the rent loss for the period when Tenant Space B is vacant. Deduct both the loss of rent and the loss of expense reimbursements. Operating Expenses Property Taxes: $1.40/ SF per year in Year 1 ; increases 3% per year Insurance: $0.60/SF per year in Year 1 ; increases 2.5% per year Common area maintenance: $0.30/SF per year Management: 5% of EGI Reserves: $0.30/ SF per year; increases 2% per year Non-recurring Expenses There are no non-recurring expenses in Year zero or in Years 1-5. They only happen when a new or renewal tenant is signed up, or if there is a major capital expense. Tenant Space A : Renewal at the beginning of Year 8 , for five more years. Tenant A will renew at Market Rent for Year 8 , regardless of what the scheduled rent was prior to renewal. The renewal rent will keep up with market, so it steps up 3% each year. Owner pays $10/SF for tenant improvements and a broker's commission of 3% of the total rent contract $ for years 8-12. Tenant Space B: Existing tenant vacates in the middle of Year 6 . A new tenant takes the space at the end of Year 6, and starts to pay rent at the beginning of Year 7 . The rent will start at Year 7 's market rent, and it will keep up with market, stepping up 3% each year. Owner pays $20/ SF for tenant improvements and a broker's commission of 5% of the total rent contract $ for years 7-11. For both Tenant spaces, A and B, assume that the entire amount of the broker's commission and tenant improvements are paid upon the signing of the lease. Capital Expenditures: The entire center needs a replacement roof in Year 3, at a cost of $4.00/SF of leased area. Direct Cap Rates: "Going-in" Cap Rate at time of purchase (Year zero): 6.50% Terminal Cap Rate at reversion sale (Year 10): 7.00% Reversion Sale: Deduct Broker's commission of 5\% Reversion Sale: Deduct Broker's commission of 5% Annual Cash Flows: On Line 46, show all cash flows from Year zero (purchase price) to Year 10 (NOI minus non-recurring expenses, plus net proceeds from Year 10 reversion sale). Express negative cash flows as negative numbers. Total Equity Cash Flows: On Line 60, show all equity cash flows from Year zero (Down Payment) to Year 10 (Equity dividend plus net equity proceeds from reversion sale). Please fill in the following answers on your Excel spreadsheet: 2.1 If the purchase price (Year zero) is based on Year 1 income using a cap rate of 6.50%, what is the purchase price? 2.2 Looking at Year 11 income, and using a terminal capitalization rate, RT of 7.00% what is the reversion sale price at the end of Year 10 ? 2.3 What are the net proceeds of the Year 10 sale after deducting a 5% broker's commission? 2.4 What is the total Cash Flow in Year 10, including the net proceeds from the sale? 2.5 If the initial purchase price (Year zero) is based on Year 1 income and a cap rate of 6.50%, what is the Internal Rate of Return? 2.6 Determine the Present Value of the investment (including the present value of cash flows and of the reversion) for each of these discount rates: 9%10%11% Hint: Break out the NPV of the cash flows from the PV of the reversion and use the PV formula to find the present value of the reversion sale. \#2.7-2.12 Mortgage and Equity: Use the Purchase price that you determined in Question \#2.1 for the following problems. 2.7. If you purchase the property for the amount you determined in Question \#2.1, and you borrow year amortization schedule with a 5.00% annual interest rate, payable monthly. 2.8 What is the Debt Coverage Ratio for Year 1? Use the formula Io divided by IM and express the result as 1xx. 2.9 What is the first year equity capitalization rate, REE ? 2.10 Using the Excel formula for FV, calculate the balance of the loan at the end of Year 10 . 2.11 What are the annual Equity Cash Flows for Year zero through Year 10 ? 2.12 Find the Equity IRR for the investment. Discounted Cash Flow Model - Grocerg Anchored Retail Center Total Dperating Eupenses Net Operating Income Non-recurring Expenses. Brokers' Leasing Commissions Tenant Improvements Capital Expenditures Total - Non-recurring Expenses Reversion Sale at end of Holding Period [Yr. 10] Broker"s commission 5% of Year 10 Sale Net Proceeds from Reversion Sale Purchase Price [Yr. Zero] Annual Cash Flow [Including Year 10 Sale) Loan Amount [Yr. Zero] Down Pagment [Yr. Zero] Annual Cash Flow (Including Year 10 Sale) _oan Amount [Yr. Zero] Down Pagment [Yr. Zero] Annual Debt Service Equity Dividend _oan Pagoff [Yr. 10] Vet Equity Proceeds from Reversion Sale Total Equitg Cash Flow Present Yalue Capitalization Rates IRR - Internal Rate of Return DCR - Debt Coverage Ratio \begin{tabular}{|l|l|l|} \hline \multicolumn{1}{|c}{ NOI } & ADS & DCR \\ \hline & & \\ \hline \end{tabular} The subject property is a three-tenant retail center, 50,000SF total, with a grocery store anchor. All rents and expenses are expressed on an annual basis. Grocery: 40,000SF Tenant A: 6,000SF Tenant B: 4,000 SF Scheduled Rents Grocery pays on a straight percentage basis plus reimbursable expenses, but they are obligated to pay a minimum rent of $12.50/SF per year starting in Year 1 . The guaranteed minimum increases $0.50/SF/ year each year. Otherwise, rent is 3% of all gross retail sales. The grocer will pay whichever amount is the highest - guaranteed minimum or 3% of gross retail sales. In either case, they also reimburse the owner for specified expenses. Gross retail sales are $20MM per year in Year 1 , increase by $2 MM per year over years 26, then stay flat. Tenants A and B pay a Year 1 rent of $25/SF/ year plus reimbursable expenses. Their contract rents increase by 50c/SF each year. Rent bumps up to Market Rent on renewal or replacement with a new tenant. Market Rent Tenant spaces A and B:$25/SF in Year 1. Market rent increases by 3% per year (round each year's total market rent to nearest $1 ). Grocery space: N/A. Not applicable to the problem due to long-term lease with national credit tenant. Lease Renewals / Tenant Turnover Grocery lease expires in 20 years. Tenant A: Renews at market at beginning of Year 8 Tenant B: Lease terminates in the middle of Year 6 . The space stays vacant for 6 months. New tenant signs a lease at the end of Year 6 and pays market rent starting at beginning of Year 7. Expense Reimbursements Each tenant pays a pro rata share of property taxes, insurance, common area maintenance, and an Expense Reimbursements Each tenant pays a pro rata share of property taxes, insurance, common area maintenance, and an administrative fee of 7% on top of that amount. Pro rata share for each tenant space is proportional to the tenant's net leasable area. Each tenant pays the same percentage of expense as its percentage of the Net Leasable Area. Show Reimbursements as a one line summary for each year, including Year 11. Vacancy and Credit Loss - Nominal 2% of PGI for each year Vacancy and Credit Loss - Actual Show the rent loss for the period when Tenant Space B is vacant. Deduct both the loss of rent and the loss of expense reimbursements. Operating Expenses Property Taxes: $1.40/ SF per year in Year 1 ; increases 3% per year Insurance: $0.60/SF per year in Year 1 ; increases 2.5% per year Common area maintenance: $0.30/SF per year Management: 5% of EGI Reserves: $0.30/ SF per year; increases 2% per year Non-recurring Expenses There are no non-recurring expenses in Year zero or in Years 1-5. They only happen when a new or renewal tenant is signed up, or if there is a major capital expense. Tenant Space A : Renewal at the beginning of Year 8 , for five more years. Tenant A will renew at Market Rent for Year 8 , regardless of what the scheduled rent was prior to renewal. The renewal rent will keep up with market, so it steps up 3% each year. Owner pays $10/SF for tenant improvements and a broker's commission of 3% of the total rent contract $ for years 8-12. Tenant Space B: Existing tenant vacates in the middle of Year 6 . A new tenant takes the space at the end of Year 6, and starts to pay rent at the beginning of Year 7 . The rent will start at Year 7 's market rent, and it will keep up with market, stepping up 3% each year. Owner pays $20/ SF for tenant improvements and a broker's commission of 5% of the total rent contract $ for years 7-11. For both Tenant spaces, A and B, assume that the entire amount of the broker's commission and tenant improvements are paid upon the signing of the lease. Capital Expenditures: The entire center needs a replacement roof in Year 3, at a cost of $4.00/SF of leased area. Direct Cap Rates: "Going-in" Cap Rate at time of purchase (Year zero): 6.50% Terminal Cap Rate at reversion sale (Year 10): 7.00% Reversion Sale: Deduct Broker's commission of 5\% Reversion Sale: Deduct Broker's commission of 5% Annual Cash Flows: On Line 46, show all cash flows from Year zero (purchase price) to Year 10 (NOI minus non-recurring expenses, plus net proceeds from Year 10 reversion sale). Express negative cash flows as negative numbers. Total Equity Cash Flows: On Line 60, show all equity cash flows from Year zero (Down Payment) to Year 10 (Equity dividend plus net equity proceeds from reversion sale). Please fill in the following answers on your Excel spreadsheet: 2.1 If the purchase price (Year zero) is based on Year 1 income using a cap rate of 6.50%, what is the purchase price? 2.2 Looking at Year 11 income, and using a terminal capitalization rate, RT of 7.00% what is the reversion sale price at the end of Year 10 ? 2.3 What are the net proceeds of the Year 10 sale after deducting a 5% broker's commission? 2.4 What is the total Cash Flow in Year 10, including the net proceeds from the sale? 2.5 If the initial purchase price (Year zero) is based on Year 1 income and a cap rate of 6.50%, what is the Internal Rate of Return? 2.6 Determine the Present Value of the investment (including the present value of cash flows and of the reversion) for each of these discount rates: 9%10%11% Hint: Break out the NPV of the cash flows from the PV of the reversion and use the PV formula to find the present value of the reversion sale. \#2.7-2.12 Mortgage and Equity: Use the Purchase price that you determined in Question \#2.1 for the following problems. 2.7. If you purchase the property for the amount you determined in Question \#2.1, and you borrow year amortization schedule with a 5.00% annual interest rate, payable monthly. 2.8 What is the Debt Coverage Ratio for Year 1? Use the formula Io divided by IM and express the result as 1xx. 2.9 What is the first year equity capitalization rate, REE ? 2.10 Using the Excel formula for FV, calculate the balance of the loan at the end of Year 10 . 2.11 What are the annual Equity Cash Flows for Year zero through Year 10 ? 2.12 Find the Equity IRR for the investment. Discounted Cash Flow Model - Grocerg Anchored Retail Center Total Dperating Eupenses Net Operating Income Non-recurring Expenses. Brokers' Leasing Commissions Tenant Improvements Capital Expenditures Total - Non-recurring Expenses Reversion Sale at end of Holding Period [Yr. 10] Broker"s commission 5% of Year 10 Sale Net Proceeds from Reversion Sale Purchase Price [Yr. Zero] Annual Cash Flow [Including Year 10 Sale) Loan Amount [Yr. Zero] Down Pagment [Yr. Zero] Annual Cash Flow (Including Year 10 Sale) _oan Amount [Yr. Zero] Down Pagment [Yr. Zero] Annual Debt Service Equity Dividend _oan Pagoff [Yr. 10] Vet Equity Proceeds from Reversion Sale Total Equitg Cash Flow Present Yalue Capitalization Rates IRR - Internal Rate of Return DCR - Debt Coverage Ratio \begin{tabular}{|l|l|l|} \hline \multicolumn{1}{|c}{ NOI } & ADS & DCR \\ \hline & & \\ \hline \end{tabular}