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.