Please, help asap! I have one day. Feedback will be given. & show some work. [in Excel]
For the final project you will need you to create a spreadsheet /proforma of the cash flows from a property. There is no magic. All you have to do is layout the cash flows on a monthly basis. Then you need to (on a second tab) aggregate the cash flows by year. When you have the second tab showing annual cash flows for the 5 years of holding period plus the reversion, you can calculate value, mortgage and all the other metrics. I am not looking for clegant coding. I am just looking for an understanding of how the cash flows work and how to calculate the various metrics. This assignment is to create an analysis for purchasing a property, projecting rents, expense pass throughs, expenses, capital over a five-year holding period and the sale. Below is all the information that you need to do the calculations and determine the metrics behind the investment. Market rent $50/sf for gross leases with a new base year based on expenses at time of new lease $30/sf for net leases. 2.5% annually for everything Growth rate 10 yrs Rent Roll Tenant SF Rent/yr/sf Stop Lease Start Mo. Term Steps Tenant A 30,000 $49.00 BY $20.00/sf 1/1/2021 3%/year Tenant B 20,000 $45.00 percentage rent 1/1/2021 8 y see belowrs Tenant C 15,000 $30.00 Net 1/1/2021 3 yrs N/A Tenant D 35,000 $50.00 BY $21.50/sf 6/1/2021 10 yrs $56/sf in month 57 Total Base year (BY) The tenant will pay for expenses that have risen to a level above their expense stop. Net leases have a stop of zero, so they pay all reimbursable expense. (Expense - stop) x sf Tenant B Percentage rent - the tenants will pay 10% on sales per year exceeding $9,800,000. The sales are Yrl 450/s, yr2=550/sf, yr3=510/sf, yr4=465/sf and yr5=600/sf. Tenants A and D are modified gross with expenses passed through over a base year while tenant C is Net. Vacancy/Credit Loss 10% of PGI Expenses Expense Category Taxes R&M Insurance Utilities Payroll Security G&A Total Reimbursable Expense per foot $10.00/sf $ 3.00/sf $ 0.50/sf $ 4.00/sf $ 1.75/sf $ 1.00/sf $ 0.80/sf $21.05/sf Non Reimbursable Management 3% of EGI Assume zero rollover possibility. Downtime (vacancy at end of lease) 6 months Capital Expenditures Total Capital at rollover 25/sf growing at inflation Structurural reserve = $0.15 per square foot each year. From the above information, you should be able to create a proforma. Use following parameters. 7.0% 10.0% 5 years Reversion Discount Rate Term Loan Info Amortization Term Rate LTV 30 years 5 years 4% rate 70% Create a proforma for a five-year hold aggregating the cash flows from each lease. Calculate Value Loan IRR Cash flow after debt Service Cash on Cash Return Debt service coverage Ratio Leveraged IRR Debt Yield Break-even ratio Extra Credit -worth 10% The developer will invest 20% of the equity and the money partner will invest 80%. The investment partner has been promised an 8% preferred return. They will split cash flows after the preferred return 50/50. Please calculated each investors IRR and Equity Multiple. How to begin Start by creating a column for each month of the hold period and additional 12 months for the reversion year. In these columns enter in the first line 1 increasing each month by 1. The next line put in the dates. Starting with 1/1/21. Then for each lease put in the monthly rent that is due for each month. Add a line for the sum. Then put in the pass through that is due in each month for each tenant. Add a line for the sum of the pass throughs. Sum of pass throughs and rent equals PGI Apply the vacancy for the property Calculate EGI. Enter in each column the appropriate expense for each line for each column. Sum the expenses Subtract the expenses from the EGI Don't forget that rent and expenses are shown as $/sf/year so when creating the cash flows on a monthly basis you have to adjust accordingly.. For the final project you will need you to create a spreadsheet /proforma of the cash flows from a property. There is no magic. All you have to do is layout the cash flows on a monthly basis. Then you need to (on a second tab) aggregate the cash flows by year. When you have the second tab showing annual cash flows for the 5 years of holding period plus the reversion, you can calculate value, mortgage and all the other metrics. I am not looking for clegant coding. I am just looking for an understanding of how the cash flows work and how to calculate the various metrics. This assignment is to create an analysis for purchasing a property, projecting rents, expense pass throughs, expenses, capital over a five-year holding period and the sale. Below is all the information that you need to do the calculations and determine the metrics behind the investment. Market rent $50/sf for gross leases with a new base year based on expenses at time of new lease $30/sf for net leases. 2.5% annually for everything Growth rate 10 yrs Rent Roll Tenant SF Rent/yr/sf Stop Lease Start Mo. Term Steps Tenant A 30,000 $49.00 BY $20.00/sf 1/1/2021 3%/year Tenant B 20,000 $45.00 percentage rent 1/1/2021 8 y see belowrs Tenant C 15,000 $30.00 Net 1/1/2021 3 yrs N/A Tenant D 35,000 $50.00 BY $21.50/sf 6/1/2021 10 yrs $56/sf in month 57 Total Base year (BY) The tenant will pay for expenses that have risen to a level above their expense stop. Net leases have a stop of zero, so they pay all reimbursable expense. (Expense - stop) x sf Tenant B Percentage rent - the tenants will pay 10% on sales per year exceeding $9,800,000. The sales are Yrl 450/s, yr2=550/sf, yr3=510/sf, yr4=465/sf and yr5=600/sf. Tenants A and D are modified gross with expenses passed through over a base year while tenant C is Net. Vacancy/Credit Loss 10% of PGI Expenses Expense Category Taxes R&M Insurance Utilities Payroll Security G&A Total Reimbursable Expense per foot $10.00/sf $ 3.00/sf $ 0.50/sf $ 4.00/sf $ 1.75/sf $ 1.00/sf $ 0.80/sf $21.05/sf Non Reimbursable Management 3% of EGI Assume zero rollover possibility. Downtime (vacancy at end of lease) 6 months Capital Expenditures Total Capital at rollover 25/sf growing at inflation Structurural reserve = $0.15 per square foot each year. From the above information, you should be able to create a proforma. Use following parameters. 7.0% 10.0% 5 years Reversion Discount Rate Term Loan Info Amortization Term Rate LTV 30 years 5 years 4% rate 70% Create a proforma for a five-year hold aggregating the cash flows from each lease. Calculate Value Loan IRR Cash flow after debt Service Cash on Cash Return Debt service coverage Ratio Leveraged IRR Debt Yield Break-even ratio Extra Credit -worth 10% The developer will invest 20% of the equity and the money partner will invest 80%. The investment partner has been promised an 8% preferred return. They will split cash flows after the preferred return 50/50. Please calculated each investors IRR and Equity Multiple. How to begin Start by creating a column for each month of the hold period and additional 12 months for the reversion year. In these columns enter in the first line 1 increasing each month by 1. The next line put in the dates. Starting with 1/1/21. Then for each lease put in the monthly rent that is due for each month. Add a line for the sum. Then put in the pass through that is due in each month for each tenant. Add a line for the sum of the pass throughs. Sum of pass throughs and rent equals PGI Apply the vacancy for the property Calculate EGI. Enter in each column the appropriate expense for each line for each column. Sum the expenses Subtract the expenses from the EGI Don't forget that rent and expenses are shown as $/sf/year so when creating the cash flows on a monthly basis you have to adjust accordingly
