FEEL ONLY THE ATTACHED 5 YEAR PRO FORMA WITH THE FOLLOWING PROMPTS THANK YOU VERY MUCH! :)

You are considering a purchase of a 4-plex, which is located in a desirable neighborhood. The cost of the property is $500,000. Effective rents are expected to average $1500 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 1500 sq. ft. . Financing information: Amount financed: $350,000 (70% of purchase price) . Amortization period: 30 years, compounded monthly Interest rate: 7.5% . . . . Complete a 5-year Pro Forma analysis using the excel worksheet in the assignment and the following information: 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: Land = $100,000 Building = $400,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 item; 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. 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. Year 1 Year 2 Year 3 Year 4 Year 5 Operating Revenues Gross Scheduled Income (GSI) Vacancy Loss (unit only) Net Rental Income Other Income (garage potential - garage vacancy) Gross Income (GI) Operating Expenses Repairs and Maintenance Property Management Fees Taxes Insurance Salaries and Wages Utilities Trash Removal Professional Fees Advertising Other Total Op Expenses 12,240 3,600 5,040 1,800 4,320 6,480 720 360 720 1,080 36,360 Net Operating Income (NOI) Interest on Loan Depreciation Expense Net Income Before Taxes Income Tax Rate (35%) Net Income After Taxes Cash Flow From Operations Net 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 8,179 10,257 12,441 15,034 16,992 You are considering a purchase of a 4-plex, which is located in a desirable neighborhood. The cost of the property is $500,000. Effective rents are expected to average $1500 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 1500 sq. ft. . Financing information: Amount financed: $350,000 (70% of purchase price) . Amortization period: 30 years, compounded monthly Interest rate: 7.5% . . . . Complete a 5-year Pro Forma analysis using the excel worksheet in the assignment and the following information: 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: Land = $100,000 Building = $400,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 item; 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. 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. Year 1 Year 2 Year 3 Year 4 Year 5 Operating Revenues Gross Scheduled Income (GSI) Vacancy Loss (unit only) Net Rental Income Other Income (garage potential - garage vacancy) Gross Income (GI) Operating Expenses Repairs and Maintenance Property Management Fees Taxes Insurance Salaries and Wages Utilities Trash Removal Professional Fees Advertising Other Total Op Expenses 12,240 3,600 5,040 1,800 4,320 6,480 720 360 720 1,080 36,360 Net Operating Income (NOI) Interest on Loan Depreciation Expense Net Income Before Taxes Income Tax Rate (35%) Net Income After Taxes Cash Flow From Operations Net 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 8,179 10,257 12,441 15,034 16,992