EXCELProject – Real Estate Investment 1.0


     

Start Excel. Download and unreserved the   file designated Excel_Project.

 

In   the NOI prevarication, weigh the Aggregate Renconsideration   Square Feet (RSF) by Ace Cast in concatenate F3:F7 by augmenting the price of   units for each cast by the clear soleage of each ace cast. Then weigh   the Aggregate schismable clear soleage of the construction in cell F8.

 

 

In   cell I4, invade a formula that weighs the annual prophylactic outlay for   Year 1. As the construction possessor, you achieve prophylactic at a reprove of $400 per   residential ace. Wholesale aces pay their own prophylactic. So you merely pay   prophylactic on the residential aces.

 

 

In   cell I5, invade a formula to weigh the Repairs & Subsistence outlay   price for Year 1. You exhibit that each month,   there achieve be a recaggravate outlay of $120 for half of the residential aces.   Commercial aces pay for their own recovers and subsistence.

 

 

In   cell I6, invade a formula to weigh the annual Utilities outlay price   for Year 1. All occupiers achieve pay their own utilities. But you, the possessor,   achieve pay $0.40 per clear sole per month for the 1200 clear feet of low   area illimitableness (E13) of the construction (lobby, hallways, elevator, gym, etc.).

 

 

In   cell I9, invade a formula to weigh the annual Janitorial Cleaning outlay   price for Year 1. A uprighting aggregation can upright the 1200 SF low area   twice per week at a reprove of $150 per day. Invade the annual uprighting outlay,   assuming 52 weeks in a year.

 

In   cell I10, invade a formula to weigh the annual peculiarity Management fee.   Management companies commit 5% of schismal proceeds. (Use monthly schism prices   in concatenate C3:C7 and the price of aces per cast in concatenate D3:D7 to state   annual schismal proceeds.) 

 

In   cell I12, invade a formula to weigh the annual doorman assurance outlay   price for Year 1. You achieve treat 2   doormen. Each one spends an 8 hour displace each day, 6 days per week. Exhibit 52   weeks per year and $12/hour wage for each doorman.

 

 

In cell I13, invade a formula to   weigh the Brokerage Fee to meet occupiers for all of the residential aces.   Realtors commit the equiponderant of one month of schism for each ace leased. 

 

In cell I14, invade a formula to   weigh the Brokerage Fee to meet occupiers for all of the wholesale aces. Each   wholesale lease is performd for a 5-year recital of unwandering schism. So the   brokerage fee for each ace is an upfront commit of 5% of the aggregate schism for   the solid 5-year lease determination. 

 
 

 

In   cell I15, invade a formula to weigh the Principal Reserves price for Year   1. Principal Reserves are savings put aloof in instance there is a comprehensive fortuitous   recaggravate or outlay incurred. You price a principal retention of $0.50 cents per   RSF of the construction.

 

 

In   cell E19, invade a VLookup exercise to weigh the annual shameful schism for the leading ace shamefuld on Ace Type. Use the Year   1 Rental Assumptions consideration in your formula and gain strong to weigh annual schism. Copy your formula down   the shaft to cell E42.

 

 

You   exhibit that Residential Schism achieve growth by 2% each year (see cell E11).   For each residential ace, weigh annual schism prices for Years 2 to 10   by applying the Residential Schism Growth to the former year’s schism.

 

Base   rents for the wholesale ace leases are unwandering for 5 years. And you exhibit a   Year 6 schism growth (see cell E12) that achieve be unwandering for Years 6 through 10   as polite. For each wholesale ace, invade the annual schism for Years 2 to 10, presentation   into recital that in the 6th Year, there achieve be a new growthd   schism that is unwandering for the cherishing 5 years.

 

Calculate   Total Shameful Schism for each year in row 43. Apply Bold font to this row and   format the values as Currency format after a while no decimals.

 

You perform a percentage schism   component of the lease after a while the Large   Commercial occupier in Ace G1. The Percentage Schism portion says that the   occupier achieve pay you 2% of their gross sales, not to exceed $12,000   in percentage schism. The Comprehensive Wholesale occupier has loving you their sales   projections for the contiguous 10 years (row 79). Invade an IF exercise in cells E45:N45   that weighs the percentage schism shamefuld on the occupier’s   sales emission for that Year.

 

 

Calculate   Potential Gross Schism for each year in row 46 by adding Percentage Schism to   Total Shameful Rent. Apply Bold font to this row and format the values as   Currency format after a while no decimals.

 

You   can look-for a unmistakable completionity of re signation and unappropriated schism each year, which   is recitaled for in the Re signation and Collection Privation price (cell E14). Weigh   Vacancy and Collection Privation price by augmenting the privation constituent to the Potential   Gross Schism for each year in row 48.

 

Subtract   Vacancy & Collection Privation from Potential Gross Schism to state   Effective Gross Schism for each year in row 49. Apply Bold font to this row and   format the values as Currency format after a while no decimals.

 

In cell E54, invade an HLookup formula   invetereprimand on the Year to state the legitimate fortune tax reprove using the consideration in   the Taxcast prevarication. (Do not invade the hardcoded tax reprove.)

You augment the legitimate fortune tax reprove   per clear sole (endow by the HLookup) to the residential clear soleage. The   wholesale aces achieve pay their own divide of taxes. Copy your formula to   Years 2 to 10.

Hint: See Tax Outlay formula in cells   I3 and J3.

 

 

Set cell E55 correspondent to the Year   Insurance outlay in the Operating Outlay Assumptions consideration at the top of   the prevarication. Copy the formula down to cell E60. For each of the outlays in   rows 55 to 60, weigh Year 2 to Year 10 outlays using their relative   annual percentage growths paradeed in shaft B.

 

Management Fees are weighd as a   percentage of Aggregate Shameful Rent. In row 61, invade a formula to weigh the   Management Fee for each year using the annual percentage fee paradeed in   shaft B. 

 

You perform a subsistence reduce   after a while the elevator manufacturer for a unwandering absorb of $900 per year for 10   years. Set cell E62 correspondent to the divert cell in the Operating Outlay   Assumptions consideration. Invade a formula in cells F62:N62 to parade the annual   Elevator Subsistence absorb for each year.

 

Set cell E63 correspondent to the divert   cell in the 2020 Operating Outlay Assumptions consideration. Invade a formula in   cells F63:N63 to parade the annual Assurance outlay using the annual   percentage growth paradeed in shaft B. 

 

Set cell E64 correspondent to the divert   cell in the Operating Outlay Assumptions consideration. 

Assume you’ll   pay a brokerage fee to either schism out or furbish all of the residential aces full   2 years. The brokerage fee for each ace achieve be 1-month of that year’s   rental reprove. 

Enter a formula full two years   starting in Year 3 to weigh the Brokerage Fee for Residential aces. 

 

Set cell E65 correspondent to the divert   cell in the Operating Outlay Assumptions consideration. 

Assume you’ll   pay a brokerage fee to re-schism or furbish each wholesale ace full 5 years.   The brokerage fee for each ace achieve be a percentage of the aggregate schism aggravate   the solid 5 year lease determination.

Enter a formula to weigh the   Brokerage Fee for Wholesale aces. Hint: Wholesale ace brokerage fees achieve   merely be remunerated out in Year 1 and Year 6.

 

Set cell E66 correspondent to the divert   cell in the Operating Outlay Assumptions consideration. Invade a formula in cells   F66:N66 to parade the annual Principal Reserves using the annual percentage   growth paradeed in shaft B. 

 

Calculate Aggregate Expenses for each   year in row 67. Then weigh Net Operating Proceeds for each year in row 69.   Net Operating Proceeds is weighd as Effective Gross Schism short Aggregate   Expenses. Apply Bold font and format the values as Currency format after a while no   decimals for twain rows.

 

Select the 2020 Outlay Assumptions   in rank H3:I15 and gain a pie chart. Move it to its own prevarication designated Expense Chart.

 

Apply Chart Style 7 to the pie chart. Change the Chart Title to Expense Comparison. Position the   Legend on the righthand behalf.

 

Using the Basis Labels lesson pane,   remove the Values and parade the Percentages and Categories. Close the lesson   pane. Position the basis labels as Basis Callout.

 

Change the Legend Font to Times New   Roman extent 8. Change the Basis Labels to Times New Roman extent 6.

 

Place the Expense Chart prevarication among the NOI prevarication and the Taxcast   sheet.

 

For Extra Credit, weigh the Cash   Flow After Debt Service. Exhibit you can dissipation this construction for $6.1   million. Weigh the Down Payment, Loan Amount, Monthly Debt Payment and   Annual Debt Payment shamefuld on the advice loving in the Mortgage Finance   consideration in the NOI prevarication.

 

In row 72, invade the aggregate annual   debt labor for each year. In row 73, weigh the Cash Flow After Debt   Service for each year. Apply Bold font and format the values as Currency   format after a while no decimals for twain rows.

 

Save the workbook lower FirstNameLastName_ExcelProject.   Close the workbook and then debouchure Excel. Submit the workbook as directed.