The next 5 screenshots were provided my the professor to help us.

This is what we have come up with.

Evaluate the attached proforma information. You need to do a ten year Spreadsheet similar to Tables 14.3 through .5 In Chapter 14. Also Determine the Internal rate of return before and after TAX on equity. Based on your analysis provide a Executive Summary as if you were a loan officer presenting to your board of directors detailing your interpretation as to whether you would loan the money on the project and why. If the answer is yes what are the major risks that could cause the loan to default. If the answer is no, what factors could the borrower change to make the project feasible. Do a detailed paper including all your assumptions and the process you used for your analysis. Also create a 15 Minute presentation based on your 1-2 page executive summary which you will all present in class.

Fina 3387 Class Project Spring 2020 120 unit Apartment Complex $3,056,600 10 Years Ten year Project Purchase Price: Holding Period: Depreciation in years: Land Value % of Price: Loan to Value: Term of Loan: Points to close: 25% 70% 20 years 2% Interest Rate: 6.5% 1% of the loan before 15 years 34% Prepayment Penalty Investor Tax Rate Growth rate in rents: Vacancy Rate: 4.5% 13% 12% Going out Cap rate 7% of selling price 15% 12% Cap rate for resale: Selling Costs: Capital gains Tax Rate: Target Equity IRR after Tax AVERAGE RENT Per Type Average Rent Studio Apartments: $400 One bedroom Apartments: $600 Two bedroom Apartments: $800 Annual Operating Expenses Management % of effective gross income: Other Annual Income: w of Units 40 8% including Manager Property Taxes: Property Tax Growth rate: Property Insurance Property insurance growth rate: Maintenance and repair: Other Expenses Other Expenses Growth rate: $136,329 2% year $35,000 2% $200,000 includes replacement reserves $12,000 $3,056,600 Summary Information: Purchase Price: Acquisition Costs: Acquisition Going in Cap rate: Initial Equity Investment Including Points: Cost Recovery Value of Improvements (Depreciation) Useful Life in Years 11.5 Month year Cost Recovery Deduction (IRS Rule) Full yr depreciation based on 27.5 yrs (IRS rule) Disposition Cap Rate based on 11" yr NOI Projected Sale price (end of 10yrs) Projected Cost of Sale (end of 10 yrs) Acquisition Financing Information 10% _(based on 7% of price) Loan amount: Interest Rate: 20 years Payments per yo Periodic Payment: Annual Debt Service: Amortization Period: Term: Loan cost at 2% of Loan: Effective cost of Borrowed Funds: (with prepayment at end of 10 yrs) 20 years Other Information Lease Term information (units): Month to Month 1 year + 2 year+ 10 Studio Apartments: 30 One bedroom Apartments: Two bedroom Apartments: (Remember if on lease other than month to month cannot increase rent until lease is up. Assume lease term ends at period (ie, 1 Year leases end in 1 year and 2 year in 2 years. Also assume similar rotation for each year). Property Valuation: Assume Property increases in value at rate if inflation. THE ELLWOOD FORMULATION FOR INCONTE PROPERTY APPRAISAL Initial NOI = R-Y-M C (+ or -adjustment for depreciation or appreciation) Growth Rate N is the loan te value ratio Effective Constant NOI Annual Loan Pymts - USE THIS AS BASE CASE VARIABLE Resulting DCR 55,150 15.05 Value = $682,838 Holding Period in Years = Y Equity Yield M=Loan/ Value Ratio Term of Mortgage Interest Rate Expected Net Appreciation After Transactions Costs 24.98% Coccocent Annualized tort Constant 0.07571 CYP(1/5a)-F 0.04925 SINKING FUND FACTOR Yks the equally yield before tax Pis the proportion of loan paid "BASIC RATE = 0.09321751 0.05091301 R CAP RATE WITH R- 8.09% O=0 Using the Miller Geltner Spreadsheet from Chapter 14 Starting: When the spreadsheet opens, hit the icon that says "enable macros". You will be on the Input page of the spreadsheet. Your inputs feed into light yellow colored cells. The input page looks something like this with new inputs as shown: Investor Assumptions 1. Tax Rate on Ordinary Income 34.00 2.Tax Rate on Capital Gains 15.00 3. Tax Rate on Cost Recon Recapture 25.000 4.Projected Holding Period 5.EOY 10 Disposition Price (11th Year NOI Capped or 5 Amount) 10.00 $760,566 6. Disposition Cost of Sale 7.0094 Property Assumptions 7. Purchase Price (Acquisition Cap Rate or S Amount) 650,000.00 $650.000 8. Acquisition Costs 9. Improvement Allocation 80.00 10.Useful Life 27.5 11. Year 1 Potential Rental Income: Constant Annual Growth Rate $90,000 3.0096 12.Constant Vacancy Rate 8.00 13. Property Management Expense (% of Effective Rental Income) 8.000 14. Year 1 Property Taxes/Constant Annual Growth Rate $8,926 15. Year 1 Property Insurance Constant Annual Growth Rate $2,437 2.0054 16. Year 1 Repairs and Maintenance Constant Annual Growth Rate $10,560 3.00% 17. Year 1 Accounting and Advertising Constant Annual Growth Rate $1,200 3.00% Acquisition Financine Assalons Caleulation of Loan Amount (% of Purchase Price, DCR. or Loan 18.Amount) 80.00 S$20.000 9. Interest Rate Amortization Period 21. Term 22. Payments Per Year 23.Loan Costs of Loan Amour mount) Refinance Assumptions 24.Projected Year of Refinance (EOY) 25.Calculation of Lean Amount of Value DCR, or Loan Amount) 26. Interest Rate 27.Amortization Period 28. Term 29.Paymats Per Year 30.Lean Costs of Loan Amount or s Amount) 2009 LLE The Acquisition Information page is a summary of inputs and should show these figures: Property Information Purchase Price $650.000 Acquisition Costs SO Acquisition Cap Rate 8.54% Initial Investment $135,200 Cost Recovery Value of Improvements $520,000 Useful Life 27.5 11 1/2 Month Year Cost Recovery Deduction $18,122 Full Year Cost Recovery Deduction $18,907 Disposition Cap Rate 10.00% Projected End of Year 10 Sale Price $760,566 Projected End of Year 10 Cost of Sale $53,240 S520.000 8.25% 12 Acquisition Financing Information Loan Amount Interest Rate Payments Per Year Periodic Payment Annual Debt Service Amortization Period Term Loan Costs Effective Cost of Borrowed Funds $4,099.94 $49,199 25 $5.200 8.41% The cash flow page is shown below. You need to look at the lines that show the NOI in line 10, the before tax cash flow on line 20 and the financial ratios below on lines 23-31. Note in particular the Debt Coverage Ratio or DCR on line 23. It is the first year DCR that counts the most. You will see 10 years even though only 3 years are shown below. END OF YEAR 1. POTENTIAL RENTAL INCOME 590,000 92.700 595,481 2. Vany loss 54.615 54,500 $85.500 54,774 $90,707 3.EFFECTIVE RENTAL INCOME $88,665 57,257 59,287 $2.515 5 7,045 59.105 S2.46 510,877 $1.236 557317 $11.203 $1.27 $6.840 $8.926 $2.437 $10,560 $1.2001 555.537 $42.656 $18,122 S205 (55.449) 4.Less: Operating Expenses 5 Manager 6. Property Taxes 7. Property Insurance & Maintenance & Repairs 9. Accounting & Advertising 10.NET OPERATING INCOME 11. Less Interest 12. Cost Recovery 13. La Costs Amortization 14 TAXABLE INCOME 15.Times Tax Rate 16. Tas Liability (Savings) 17.NET OPERATING INCOME $59.15 $42.00 $41,487 $18,907 S205 34.00 $18.907 5208 (53.894) 14.00 (51,324) $57,317 (51,450) 34.00% ($493) (51,853) $55,537 S49,199 $59,152 $49,199 $49,199 18.Less Annual Debt Service 19. Capital Contributions 20.CASH FLOW BEFORE TAX 21. Less Tax Liability 22.CASH FLOW AFTER TAX $6,339 $8.117 59,953 (5491 ($1,853 ($1.024 $8,190 $9.441 $10,446 1.13 1.16 87.96 8 6.24% 23.Debt Coverage Ratio 24. Breakeven Oceupaney Percent 25. Expense Ratio 26. Before Tax Cash on Cash 27. Return on Asset 35.04 34.929 8549 28. Aner Tax Cash on Cash 6.064 S$13,457 SS06153 $498.641 29.EOY Balance (Acquisition Loan) 30. EOY Balance (Refinanced Loan) 31. Property Value (Acquisition CR) S670,830 692,311 $714.46 23.Debt Coverage Ratio 24. Breakeven Occupancy Percent 25. Expense Ratio 87.9694 86 2490 84.5894 35.04944 34.7994 26. Before Tax Cash on Cash 7.369 9.109 27. Return on Asset 8.54% 6,06% 4 $513,457 6.009 8.8298 6 6.989 .9894 5506,353 7.7394 5498,641 28. After Tax Cash on Cash 29.EOY Balance (Acquisition Loan) 30. EOY Balance (Refinanced Loan) 31. Property Value (Acquisition CR) 5670,8.30 692,311 5714,464 There is a page summarizing the sale proceeds that are sensitive to taxes and the residual cap rate, as follows: Sale Price Cost of Sale Adjusted Basis Total Gain Total Cost Recovery Taken Capital Gain Unamortized Loan Costs $760,566 $53,240 $462,500 $244,826 $187,500 $57,326 ($3,120) Sale Price Cost of Sale Mortgage Balance Sale Proceeds Before Tax Tax on Cost Recovery Recapture Tax on Capital Gain Tax (Loss) on Ordinary Income Sale Proceeds After Tax $760,566 $53,240 $422,613 $284,713 $46,875 $11,465 ($1,061) $227,434 Last, we have yield calculations. It is the leverage after tax yield that you want to focus upon for any taxable entity and the before tax yield for non-taxable entities. END OF YEAR 900 2.0 VESS. 25 700 STES 1 $742.70 57138 9 PRETINGOS $22.000 45.40 SP057 S5.68 5224958 18 16 EN LES ES SOLES 3 RIS ($179,417) ($116,175) (1343) (011, M2) ( ) (964,135) (537,925) 1. MET OPERATING INCOME 9931 0114.735 8137,453 1957,195 $172.727 995,39 MA 124 30 CASH FLOW NEPORETAN ) OT) GEOTERE BITS) S) Cast2 ( G Cons) TORTODOXITIONS ) Careers) Ders) lorze) $) C ') ( SRCES 1. Reperty ( A R ) Clipboard 5 Number B -118,841 -101017 88140 6888 4786628190 -88140 G 68488 F -26199 47856 G -3469 H 20,386 IJK 45416 71678 -17% before tax irr -5985547917 42256 30210 17597 -4393 9426 2388539010 54829 -7% afer tax in | Fina 3387 Class Project Spring 2020 120 unit Apartment Complex $3,056,600 10 Years Ten year Project Purchase Price: Holding Period: Depreciation in years: Land Value % of Price: Loan to Value: Term of Loan: Points to close: 25% 70% 20 years 2% Interest Rate: 6.5% 1% of the loan before 15 years 34% Prepayment Penalty Investor Tax Rate Growth rate in rents: Vacancy Rate: 4.5% 13% 12% Going out Cap rate 7% of selling price 15% 12% Cap rate for resale: Selling Costs: Capital gains Tax Rate: Target Equity IRR after Tax AVERAGE RENT Per Type Average Rent Studio Apartments: $400 One bedroom Apartments: $600 Two bedroom Apartments: $800 Annual Operating Expenses Management % of effective gross income: Other Annual Income: w of Units 40 8% including Manager Property Taxes: Property Tax Growth rate: Property Insurance Property insurance growth rate: Maintenance and repair: Other Expenses Other Expenses Growth rate: $136,329 2% year $35,000 2% $200,000 includes replacement reserves $12,000 $3,056,600 Summary Information: Purchase Price: Acquisition Costs: Acquisition Going in Cap rate: Initial Equity Investment Including Points: Cost Recovery Value of Improvements (Depreciation) Useful Life in Years 11.5 Month year Cost Recovery Deduction (IRS Rule) Full yr depreciation based on 27.5 yrs (IRS rule) Disposition Cap Rate based on 11" yr NOI Projected Sale price (end of 10yrs) Projected Cost of Sale (end of 10 yrs) Acquisition Financing Information 10% _(based on 7% of price) Loan amount: Interest Rate: 20 years Payments per yo Periodic Payment: Annual Debt Service: Amortization Period: Term: Loan cost at 2% of Loan: Effective cost of Borrowed Funds: (with prepayment at end of 10 yrs) 20 years Other Information Lease Term information (units): Month to Month 1 year + 2 year+ 10 Studio Apartments: 30 One bedroom Apartments: Two bedroom Apartments: (Remember if on lease other than month to month cannot increase rent until lease is up. Assume lease term ends at period (ie, 1 Year leases end in 1 year and 2 year in 2 years. Also assume similar rotation for each year). Property Valuation: Assume Property increases in value at rate if inflation. THE ELLWOOD FORMULATION FOR INCONTE PROPERTY APPRAISAL Initial NOI = R-Y-M C (+ or -adjustment for depreciation or appreciation) Growth Rate N is the loan te value ratio Effective Constant NOI Annual Loan Pymts - USE THIS AS BASE CASE VARIABLE Resulting DCR 55,150 15.05 Value = $682,838 Holding Period in Years = Y Equity Yield M=Loan/ Value Ratio Term of Mortgage Interest Rate Expected Net Appreciation After Transactions Costs 24.98% Coccocent Annualized tort Constant 0.07571 CYP(1/5a)-F 0.04925 SINKING FUND FACTOR Yks the equally yield before tax Pis the proportion of loan paid "BASIC RATE = 0.09321751 0.05091301 R CAP RATE WITH R- 8.09% O=0 Using the Miller Geltner Spreadsheet from Chapter 14 Starting: When the spreadsheet opens, hit the icon that says "enable macros". You will be on the Input page of the spreadsheet. Your inputs feed into light yellow colored cells. The input page looks something like this with new inputs as shown: Investor Assumptions 1. Tax Rate on Ordinary Income 34.00 2.Tax Rate on Capital Gains 15.00 3. Tax Rate on Cost Recon Recapture 25.000 4.Projected Holding Period 5.EOY 10 Disposition Price (11th Year NOI Capped or 5 Amount) 10.00 $760,566 6. Disposition Cost of Sale 7.0094 Property Assumptions 7. Purchase Price (Acquisition Cap Rate or S Amount) 650,000.00 $650.000 8. Acquisition Costs 9. Improvement Allocation 80.00 10.Useful Life 27.5 11. Year 1 Potential Rental Income: Constant Annual Growth Rate $90,000 3.0096 12.Constant Vacancy Rate 8.00 13. Property Management Expense (% of Effective Rental Income) 8.000 14. Year 1 Property Taxes/Constant Annual Growth Rate $8,926 15. Year 1 Property Insurance Constant Annual Growth Rate $2,437 2.0054 16. Year 1 Repairs and Maintenance Constant Annual Growth Rate $10,560 3.00% 17. Year 1 Accounting and Advertising Constant Annual Growth Rate $1,200 3.00% Acquisition Financine Assalons Caleulation of Loan Amount (% of Purchase Price, DCR. or Loan 18.Amount) 80.00 S$20.000 9. Interest Rate Amortization Period 21. Term 22. Payments Per Year 23.Loan Costs of Loan Amour mount) Refinance Assumptions 24.Projected Year of Refinance (EOY) 25.Calculation of Lean Amount of Value DCR, or Loan Amount) 26. Interest Rate 27.Amortization Period 28. Term 29.Paymats Per Year 30.Lean Costs of Loan Amount or s Amount) 2009 LLE The Acquisition Information page is a summary of inputs and should show these figures: Property Information Purchase Price $650.000 Acquisition Costs SO Acquisition Cap Rate 8.54% Initial Investment $135,200 Cost Recovery Value of Improvements $520,000 Useful Life 27.5 11 1/2 Month Year Cost Recovery Deduction $18,122 Full Year Cost Recovery Deduction $18,907 Disposition Cap Rate 10.00% Projected End of Year 10 Sale Price $760,566 Projected End of Year 10 Cost of Sale $53,240 S520.000 8.25% 12 Acquisition Financing Information Loan Amount Interest Rate Payments Per Year Periodic Payment Annual Debt Service Amortization Period Term Loan Costs Effective Cost of Borrowed Funds $4,099.94 $49,199 25 $5.200 8.41% The cash flow page is shown below. You need to look at the lines that show the NOI in line 10, the before tax cash flow on line 20 and the financial ratios below on lines 23-31. Note in particular the Debt Coverage Ratio or DCR on line 23. It is the first year DCR that counts the most. You will see 10 years even though only 3 years are shown below. END OF YEAR 1. POTENTIAL RENTAL INCOME 590,000 92.700 595,481 2. Vany loss 54.615 54,500 $85.500 54,774 $90,707 3.EFFECTIVE RENTAL INCOME $88,665 57,257 59,287 $2.515 5 7,045 59.105 S2.46 510,877 $1.236 557317 $11.203 $1.27 $6.840 $8.926 $2.437 $10,560 $1.2001 555.537 $42.656 $18,122 S205 (55.449) 4.Less: Operating Expenses 5 Manager 6. Property Taxes 7. Property Insurance & Maintenance & Repairs 9. Accounting & Advertising 10.NET OPERATING INCOME 11. Less Interest 12. Cost Recovery 13. La Costs Amortization 14 TAXABLE INCOME 15.Times Tax Rate 16. Tas Liability (Savings) 17.NET OPERATING INCOME $59.15 $42.00 $41,487 $18,907 S205 34.00 $18.907 5208 (53.894) 14.00 (51,324) $57,317 (51,450) 34.00% ($493) (51,853) $55,537 S49,199 $59,152 $49,199 $49,199 18.Less Annual Debt Service 19. Capital Contributions 20.CASH FLOW BEFORE TAX 21. Less Tax Liability 22.CASH FLOW AFTER TAX $6,339 $8.117 59,953 (5491 ($1,853 ($1.024 $8,190 $9.441 $10,446 1.13 1.16 87.96 8 6.24% 23.Debt Coverage Ratio 24. Breakeven Oceupaney Percent 25. Expense Ratio 26. Before Tax Cash on Cash 27. Return on Asset 35.04 34.929 8549 28. Aner Tax Cash on Cash 6.064 S$13,457 SS06153 $498.641 29.EOY Balance (Acquisition Loan) 30. EOY Balance (Refinanced Loan) 31. Property Value (Acquisition CR) S670,830 692,311 $714.46 23.Debt Coverage Ratio 24. Breakeven Occupancy Percent 25. Expense Ratio 87.9694 86 2490 84.5894 35.04944 34.7994 26. Before Tax Cash on Cash 7.369 9.109 27. Return on Asset 8.54% 6,06% 4 $513,457 6.009 8.8298 6 6.989 .9894 5506,353 7.7394 5498,641 28. After Tax Cash on Cash 29.EOY Balance (Acquisition Loan) 30. EOY Balance (Refinanced Loan) 31. Property Value (Acquisition CR) 5670,8.30 692,311 5714,464 There is a page summarizing the sale proceeds that are sensitive to taxes and the residual cap rate, as follows: Sale Price Cost of Sale Adjusted Basis Total Gain Total Cost Recovery Taken Capital Gain Unamortized Loan Costs $760,566 $53,240 $462,500 $244,826 $187,500 $57,326 ($3,120) Sale Price Cost of Sale Mortgage Balance Sale Proceeds Before Tax Tax on Cost Recovery Recapture Tax on Capital Gain Tax (Loss) on Ordinary Income Sale Proceeds After Tax $760,566 $53,240 $422,613 $284,713 $46,875 $11,465 ($1,061) $227,434 Last, we have yield calculations. It is the leverage after tax yield that you want to focus upon for any taxable entity and the before tax yield for non-taxable entities. END OF YEAR 900 2.0 VESS. 25 700 STES 1 $742.70 57138 9 PRETINGOS $22.000 45.40 SP057 S5.68 5224958 18 16 EN LES ES SOLES 3 RIS ($179,417) ($116,175) (1343) (011, M2) ( ) (964,135) (537,925) 1. MET OPERATING INCOME 9931 0114.735 8137,453 1957,195 $172.727 995,39 MA 124 30 CASH FLOW NEPORETAN ) OT) GEOTERE BITS) S) Cast2 ( G Cons) TORTODOXITIONS ) Careers) Ders) lorze) $) C ') ( SRCES 1. Reperty ( A R ) Clipboard 5 Number B -118,841 -101017 88140 6888 4786628190 -88140 G 68488 F -26199 47856 G -3469 H 20,386 IJK 45416 71678 -17% before tax irr -5985547917 42256 30210 17597 -4393 9426 2388539010 54829 -7% afer tax in |