Figure 3.8.1 Ms. Johnson is buying an apartment building in Queens. The seller sent her an annual Income and Expense report of the building as indicated in Figure 3.8.1. The price of the building is one million dollars. which is equal to annual rental income times 5.555. Seller will provide an interest-only loan of 800,000 dollars at the annual rate of 6% for 5 years. Ms. Johnson plans to sell the property at the end of fifth year. Ms. Johnson estimated that the rental incomes would increase by 4% annually. Annual percent increase of wages and real estate taxes are 3%. Electricity, heating fuel, and insurance will increase by 6%. Repairs and supplies will increase by 5%. Maintenance will increase by 2%. Management fees and the 52 CHAPTER 3 Functions interest rate of the loan will stay the same. Ms. Johnson created an Excel worksheet to include all the data provided to her, including formulas to allow for the annual increases of each major item of income and expenses as in Figure 3.8.1. To create the EXCEL model for the real estate investment, start with the numbers in column B as provided by the seller. There is no formula for column B except the Sum function. Formulas will be created in Column C as follows: 1. For rental incomes to increase by 4% annually, enter C6=B61.04,D6=C61.04, etc. 2. Wages will increase by 3% annually. C8=B81.03,D8=C81.03, etc. 3. Total expense equals the sum of all expenses. C18=Sum(C8:C17), D 18= Sum(D8:D17), etc. 4. Gross Profit from Operation is Annual Rental Income - Total Expenses. C20=C6-C18 and D20 = D6-D18, etc. 5. The outgoing cash flow at the end of year 0 is the down payment at closing, which is a negative number. 6. Ms. Johnson estimates that she could also sell the property at 5.555 times the annual rent roll at end of the fifth year, for $1,216,531 dollars. G2=G5.555. 7. At that time she will pay back the $800,000 dollar loan and earn $416,531 in net profit as shown in G4 in Figure 3.8.3. The cash inflow at end of the fifth year is the net profit from sale plus the gross profit from operations for the year. G22=G4+C20. As indicated in the above example, during the first five years of operation, there will be outgoing cash flows because the rental income is less than total expenses. At first glance, it does not look like a good investment. The total incoming and outgoing cash flows are shown in the range B22 through G22 in Figure 3.8.3. Click on cell B24 and use the Internal Rate of Return function to cvaluate Ms. Johnson's investment. You will find that Ms. Johnson's investment yield is 13.69%, which is in fact a decent investment. EXERCISE 3,8 1. Using the data in Figure 3.8.3, assume that all the annual increases are the same and assume that Ms. Johnson is able to keep the building for ten years and will be able sell the building at 5.555 times the annual rent roll afier ten years. What will be the IRR of the investment? You plan to invest 1,000 dollars in a value fund for five years. The fund pays an annual dividend equal to 5% of the investment at the end of the year, which will be reinvested into the same fund. The market value of the fund also increases by two percent each year. Assume also that you invest at the beginning of the first year. What is the value of your investment if it is sold at end of the first year, second year, or fifth year (or at the beginning of the sixth year)? What is your Internal Rate of Return if it sold at end of fifth year? 54 CHAPTER 3 - Functions Create a worksheet as the one below (Figure 3.9.1), 1. You invested $1,000 at the beginning of the first year (B2). The market value of the investment becomes $1,020 at the end of the first year. Enter =B31.02 in B4. 2. The dividend is equal to 5% of the investment at the beginning of the year. Enter B30.05 in B 5. 3. Cash investment at the beginning of the second year is equal to the cash investment at the beginning of the first year plus the dividend reinvested. Enter =B2+B5 in C2. 4. Market value at the beginning of the second year is equal to the market value at end of the first year plus the dividend from the previous year. If you sell the investment, your return will be equal to the current market value plus the dividend from the previous year. Enter =B4+BS in C3. 5. Market value at the end of year is equal to the market value at the beginning of the year times 1.02. Enter C3J.02 in C4, or copy the formula from B4. 6. Dividend at end of the year is equal to the cash investment at the beginning of the year. Enter C2O.05 in C5, or copy the formula from B5. 7. Copy formulas in C2 :C5 to columns D through G. 8. The investment may be sold ot the beginning of the sixth year for $1,391.08 (market value). 7. Copy formulas in C2:C5 to columns D through G. 8. The investment may be sold at the beginning of the sixth year for $1,391.08 (market value). Figure 3.9.1 EXERCISE 3.9 1. Compute the Internal Rate of Return of the above investment. (Hint: Use the Market value at beginning of the years in B2 and in G2. (Value: 1000,0,0,0,1276.28 ) 2. In the above case in Figure 3.9.1, if you keep the investment for 10 years, what is the IRR