Final Project - Real Estate Investment Note: Please include all calculations when the final project is submitted; Points are determined by accuracy of different parts of each question and not just on the final answer so if you get the answer wrong but had part of the calculation correct, [ will give partial credit ... if you don't give me the 'calculations' | can't give you partial credit for incorrect responses. You are considering a purchase of a 4-plex, which is located in a desirable neighborhood. The cost of the property is $1,500,000. Effective rents are expected to average $2800 per month. Every resident receives one free parking space but has to pay an additional $50 per month for a one car garage located adjacent to the 4-plex (Note: There are only four garages on the property). Annual vacancy expenses for the units are expected to average 5% (assume the garage rentals will coincide with the unit rentals so will also incur a 5% vacancy loss per year). The average unit is 1000 sq. ft. Financing information: Amount financed: $1,050,000 (70% of purchase price) s Amortization period: 30 years, compounded monthly Interest rate: 7.5% Calculate the following: 1. Average unit cost (1 pts.) 2. Gross Rent Multiplier (GRM) year one only ... leave one decimal place in answer (2 pts.) 3. Break-even Ratio (BER) year one only ... leave one decimal place in answer (2 pts.) 4, Operating Ratio (OR) year one only ... leave one decimal place in answer (2 pts.) 5. Owner's equity at purchase (1 pts.) 6. Annual payment (1 pts.) 7. Debt Service Coverage Ratio (DSCR) year one only leave one decimal place in answer (2 pts.) 8. Complete a 5-year Pro Forma analysis using the excel worksheet in the assignment and the following information (4.5 pts.): s Rental increase projections: 4% per year for years 2 and 3 and 4.5 % for years 4 and 5. No increase in garage fees. + Operating expense increase projections: 2.5% per year for years 2 and 3 and 2% for years 4 and 5. Assume a 35% tax rate for the investor. Depreciation is calculated on the 'building\" only using the straight-line method (27.5 years for residential properties and 39 years for commercial properties). For the purpose of this project, the breakdown of the building and land is as follows: o Land = 300,000 o Building = 51,200,000 to calculate the depreciation, take this figure and divide by 27.5 to arrive at the annual deduction (note: there is actually a convention known as 'mid-year\" that should be used but the number will be very close to this one and it can get a little confusing --- that's what accountants are for:-) + You will need to know the total annual payment for the loan and also be able to break the payment into its principal and interest components each year + The interest amount only is entered on the 'Interest on Loan\" line item and the principal amount only on the 'Plus Principal Reduction' line itemn; In order to find each amount you will need to do an amortization to calculate the interest and principal for each of the 5 years as the amount of interest will go down slightly and the amount of principal payment up slightly each year throughout the term of the loan. DON'T put the same amounts on each of the five years as this will be incorrect. s The 'Debt Service' line item is the total annual payment including both interest and principal so basically, the total of the line items noted in #1 above. Note: The proforma (and only the proforma) can be submitted for my review by the deadline noted in the due dates/deadlines chart under the 'Contents' organizer. It is important that the proforma is accurate in order to correctly calculate the other formulas needed for successful completion of the final project so please take advantage of this offer. It may take you several attempts to get the proforma correct so please leave ample time ... the deadline noted in the chart is the 'last day' so no reviews will occur after that date. YOU MUST INCLUDE THE FINAL VERSION OF YOUR PROFORMA WITH THE FINAL PROJECT SUBMISSION TO EARN THE 4.5 POINTS ALLOTTED FOR IT AS PART OF THIS ASSIGNMENT! 9, Using a cap rate of 7.0%, calculate the value at the end of years 1, 2, 3, 4, &5 (5 pts.). 10. Calculate the following for each of the five years (7.5 pts.): Net Income Return on Investment (NI - ROI) CashROI s Total ROI NOTE: ROl is actually about return on investment and refers to just the return on the 'original' investment adjusted for capital improvements planned at purchase, if there were any (which there aren't on the final project). If | asked you to calculate the ROE (Return on Equity), then you would adjust the equity in the denominator by the amount of other equity items found on a balance sheet but as it stands, only ROI calculations are required for the final project so you DON'T adjust for equity changes that occur in subsequent years. 11. Assuming the property were sold at the end of year 5 using a 9% cap rate, calculate the following: e Sale price (1 pts.) Annualized IRR ... must leave one decimal place for percentages (4 pts.) 12. Assuming the property were sold at the end of year 5 using an 6% cap rate, calculate the following: Sale price (1 pts.) Annualized IRR ... must leave one decimal place | Operating Revenues vear1 | Year2 | vear3 | vear4 Gross Scheduled Income (GSI) Vacancy Loss (unit only) Met Rental Income Other Income (garage potential - garage vacancy) Gross Income (Gl) Operating Expenses Repairs and Maintenance 10,487 Property Management Fees 8,212 Taxes 9,429 Insurance 8,800 Salaries and Wages 4,275 Utilities 1,287 Trash Removal 1,500 Professional Fees 1,020 Advertising 2,520 Other 1,355 Total Op Expenses 49.885' ' ' ' MNet Operating Income (NOI) Interest on Loan Depreciation Expense Met Income Before Taxes income Tax Rate (35%) 1 T T ] MNet Income After Taxes Cash Flow From Operations Met Income after Taxes Depreciation Expense Total Cash Flow from Operations Interest on Loan | Total Cash Available for | Loan Servicing Debt Service Remaining After Tax Cash Flow from Operations Plus Principal Reduction Total Return 1,653 6.265 11,108 17,147 23,812 Sheet 1: Key Metrics and Calculations 1. Headers: Metric, Formula, Calculation, Value 2. Input Section: Total Purchase Price: 1,500,000 Number of Units: 4 Annual Gross Rental Income: 129,960 Operating Expenses: 49,885 Amount Financed: 1,050,000 Net Operating Income (NOI): 80,075 A Metric Average Unit Cost Gross Rent Multiplier (GRM) Break-even Ratio (BER) Operating Ratio (OR) Owner's Equity at Purchase |Annua| Loan Payment Debt Service Coverage Ratio (DSCR) B Formula Purchase Price / Number of Units Purchase Price / Annual Gross Rental Income Operating Expenses / Annual Gross Rental Income Operating Expenses / Annual Gross Rental Income Purchase Price - Amount Financed PMT Function NOI / Annual Loan Payment @ D Calculation Value 1,500,000/ 4 375000 1,500,000 / 129,960 11.54 49,885 / 129,960 0.384 49,885 / 129,960 0.384 1,500,000 - 1,050,000 450000 [calculated 88101 80,075 / 88,101 0.909 3. Calculated Metrics: . Average Unit Cost: =B2/B3 . GRM: =B2/B4 . BER: =B5/B4 . OR: =B5/B4 . Owner's Equity: =B2-B6 . Annual Loan Payment: Calculated using PMT function: =PMT(Interest Rate, Loan Term, Loan Amount) . DSCR: =B7/B9A B C D Year Gross Rental Income (USD) Operating Expenses (USD) Net Operating Income (NOI) (USD) 2 129960 49885 80075 3 2 135158.4 51132.125 84026.275 4 140564.736 52410.42813 88154.30788 U A W 146890.1491 53458.63669 93431.51243 O UT 153500.2058 54527.80942 98972.39641 7Sheet 2: 5-Year Pro Forma Analysis 1. Headers: e VYear, Gross Rental Income, Operating Expenses, NOI 2. Inputs: e Year 1 Rental Income: 129,960 Year 1 Operating Expenses: 49,885 e Rental Growth Rates: 4% (Years 2-3), 4.5% (Years 4-5) Expense Growth Rates: 2.5% (Years 2-3), 2% (Years 4-5) 3. Formulas: e Rental Income for Year 2. =B2*(1+Growth Rate) e Operating Expenses for Year 2: =C2*(1+Expense Growth Rate) e NOI: =Rental Income - Operating Expenses \fSheet 3: Property Valuation 1. Headers: e Cap Rate, Value 2. Inputs: * Year 5 NOI: 98,972.40 e Cap Rates: 7%, 6%, and 9% 3. Formulas: e Value: =NOI / Cap Rate A B C Metric Formula Value 2 Net Income ROI Net Income After Tax / Initial Investment 11.6 3 Cash ROI Cash Flow After Debt Service / Initial Investment -1.8 4 Total ROI Total Return / Initial Investment 7.9 5 Annualized IRR (9% Cap Rate) IRR Function (Cash Flows & Sale Price) 13.23 6 Annualized IRR (6% Cap Rate) IRR Function (Cash Flows & Sale Price) 22.04Sheet 4: ROI and IRR Calculations 1. Headers: Metric, Formula, Value 2. Inputs: Initial Investment: 450,000 Net Income After Tax: 52,049 Cash Flow After Debt Service: -8,026 Total Return: 35,609 Sale Prices (at different cap rates) 3. Formulas: Net Income ROI: =Net Income After Tax / Initial Investment Cash ROI: =cash Flow After Debt Service / Initial Investment Total ROIl: =Total Return / Initial Investment IRR: Use the =IRR() function with the yearly cash flows and sale prices