L18 fx 2 A B D E F G H | K 1 Assignment 2 3 6 7 8 4 You are an investor looking to acquire a 150 unit apartment in Gainesville, FL. It is a Class B property with no major 5 damages. You can purchase the property for $10,750,000 today and you charge $850 a month for each unit on a 12 month lease. You also believe that your rent will grow at a constant yearly rate of 1.5% and project that you will have vacancy and collection losses of 5% annually since you account for some tenants not paying their rent on time even though you have 100% occupancy. You assume 60% of your tenants will need parking so you charge $75 a month for their parking spot. Every year maintenance and utilities will cost you $250,000 and it will grow at 2.5% yearly. You think 9 that based on your market projections, you can sell your property in 5 years for $11,250,000 and you expect there to be 10 selling expenses of 6%. You will not invest in this property unless you can realize an unlevered before tax return of 11 10%, a 14% levered return before taxes, and a 9.8% levered return after taxes on the property. Furthermore, you 12 successfully acquired the property with 60% financed by ABC Bank with a 15-year fixed interest rate loan at 4.25% per 13 year. You will have to pay 3% in loan expenses and you plan on using an amortization term of 15 years making this a 14 fixed interest rate fully amortized loan. You will have yearly taxes of $115,000 for the next 5 years and you will have taxes due on sale of 4% on the property. Once you complete the model, answer the quiz questions in canvas. 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1. In order to lease the building, you can only charge ($775/unit) a month in rent. By what percentage does this drop your "Unlevered Before Tax" IRR? o 2.53% 0.74% 1.23% 1.98% 2. During the interest only period of a loan, how much principal is paid down each month? c The entirety of the principal None Divide the principal by the loan term Depends on the loan term 3. You incur major CapX expenses of $250,000 in year 5. What does this change the Levered After Tax NPV to? $1,050,809 $1,147,826 $1,072,235 $1,100,927 4. What is the "Remaining Mortgage Balance" at the year of sale? $5,263,834 $4,736,736 $4,637,736 $4,276,386 5. You miscalculated your operating expenses heading into the project. You find out that they are now ($350,000/year) and will grow at 3%. What is your "Levered Before Tax" IRR? 18.01% 19.01% 17.64% 21.52% 6. After 5 years, the market isn't doing well and you can only get 80% of what you anticipated the selling price to be. How much does this change the Levered After Tax IRR by? 1.26% 8.99% 4.74% 6.98% 7. How much of your monthly payment will be used to pay down your remaining mortgage balance in Month 40? $92,474 $23,539 $29,474 $32,935 8. What payment are you left with when the Loan Term is shorter than the Loan Amortization? Balloon Payment Overage Payment Term Payment Loan Fees 9. What Levered After Tax IRR would make the Levered After Tax NPV = 0? 10.50% 9.37% 16.53% 9.80% 10. What is your Debt Service Coverage Ratio in year 2? (Debt Service Coverage Ratio = Net Operating Income/Debt Service) 1.25 2.23 2.78 3.42 L18 fx 2 A B D E F G H | K 1 Assignment 2 3 6 7 8 4 You are an investor looking to acquire a 150 unit apartment in Gainesville, FL. It is a Class B property with no major 5 damages. You can purchase the property for $10,750,000 today and you charge $850 a month for each unit on a 12 month lease. You also believe that your rent will grow at a constant yearly rate of 1.5% and project that you will have vacancy and collection losses of 5% annually since you account for some tenants not paying their rent on time even though you have 100% occupancy. You assume 60% of your tenants will need parking so you charge $75 a month for their parking spot. Every year maintenance and utilities will cost you $250,000 and it will grow at 2.5% yearly. You think 9 that based on your market projections, you can sell your property in 5 years for $11,250,000 and you expect there to be 10 selling expenses of 6%. You will not invest in this property unless you can realize an unlevered before tax return of 11 10%, a 14% levered return before taxes, and a 9.8% levered return after taxes on the property. Furthermore, you 12 successfully acquired the property with 60% financed by ABC Bank with a 15-year fixed interest rate loan at 4.25% per 13 year. You will have to pay 3% in loan expenses and you plan on using an amortization term of 15 years making this a 14 fixed interest rate fully amortized loan. You will have yearly taxes of $115,000 for the next 5 years and you will have taxes due on sale of 4% on the property. Once you complete the model, answer the quiz questions in canvas. 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1. In order to lease the building, you can only charge ($775/unit) a month in rent. By what percentage does this drop your "Unlevered Before Tax" IRR? o 2.53% 0.74% 1.23% 1.98% 2. During the interest only period of a loan, how much principal is paid down each month? c The entirety of the principal None Divide the principal by the loan term Depends on the loan term 3. You incur major CapX expenses of $250,000 in year 5. What does this change the Levered After Tax NPV to? $1,050,809 $1,147,826 $1,072,235 $1,100,927 4. What is the "Remaining Mortgage Balance" at the year of sale? $5,263,834 $4,736,736 $4,637,736 $4,276,386 5. You miscalculated your operating expenses heading into the project. You find out that they are now ($350,000/year) and will grow at 3%. What is your "Levered Before Tax" IRR? 18.01% 19.01% 17.64% 21.52% 6. After 5 years, the market isn't doing well and you can only get 80% of what you anticipated the selling price to be. How much does this change the Levered After Tax IRR by? 1.26% 8.99% 4.74% 6.98% 7. How much of your monthly payment will be used to pay down your remaining mortgage balance in Month 40? $92,474 $23,539 $29,474 $32,935 8. What payment are you left with when the Loan Term is shorter than the Loan Amortization? Balloon Payment Overage Payment Term Payment Loan Fees 9. What Levered After Tax IRR would make the Levered After Tax NPV = 0? 10.50% 9.37% 16.53% 9.80% 10. What is your Debt Service Coverage Ratio in year 2? (Debt Service Coverage Ratio = Net Operating Income/Debt Service) 1.25 2.23 2.78 3.42