please help asap! this is for an excel hw due tonight. i must provide a
fundamental cash flow analysis for a property in excel that helps me answer these questions. Part A-Unlevered 930 Bridgeport Avenue is a two-story, three-unit building in a desirable urban neighborhood near a large university campus that you are entertaining purchasing and holding as a long-term investment. It is currently listed for $235,000 or $78,333 per unit. The first floor contains a 2BR/18A unit rented for $950 per month. The second floor contains two 18R/18BA units. The front unit has a private balcony and is rented for $750 per month, and the other unit is rented for $700 per month. The seller's representative provided you with historical income data suggesting that the seller has collected about $35 per month from each tenant in the form of late fees, pet fees, and laundry income. The property also has a detached garage accessible from the alley that one of the tenants currently rents for an additional $30 per month. A local property manager you met at the wedding of a mutual friend who manages several buildings in this neighborhood told you that the rentals rates at the building appear reasonable and that facancy rates have historically hovered around 5.00% with some seasonable variability around the beginning and end of the fall semester. Historically, rents have increased by about 5% per year, while other income tends to remain flat, increasing maybe 1% per year. As a back-of-the-envelope measure, operating expenses in this market typically run at about 35% of effective gross income. \begin{tabular}{|c|c|} \hline Part A - Questions & Part A-Answers \\ \hline What is the EGI in year 1 ? & $30,420 \\ \hline What effective gross income multiplier does this imply based on the list price for the property? & \\ \hline What is the NOI in year 1 ? & 519,773 \\ \hline What cap rate does that imply based on the list price for the property? & \\ \hline What is the gross income multiplier implied by the list price? & \\ \hline \begin{tabular}{l} Assuming you hold this investment for five years and you anticipate you can sell the property at a \\ capitalization rate of 8.00% based on pro-forma income, how much do you expect to sell the property \end{tabular} & \\ \hline \begin{tabular}{l} for? \\ If closing costs are typically about 5% of the sale price for a seller, what are your expected net ffoceeds \end{tabular} & \\ \hline from the sale at the end of year five? & \\ \hline \begin{tabular}{l} If your investment criteria dictate that the property needs to generate a target yield of 11.00% per year, \\ what is the maximum you could pay for this property while still meeting that objective? \end{tabular} & \\ \hline Would you purchase this property at its current list price? & \\ \hline \begin{tabular}{l} What is your projected internal rate of return (IRR), assuming you purchase the property for its list: \\ price? \end{tabular} & \\ \hline \end{tabular} Part A-Unlevered 930 Bridgeport Avenue is a two-story, three-unit building in a desirable urban neighborhood near a large university campus that you are entertaining purchasing and holding as a long-term investment. It is currently listed for $235,000 or $78,333 per unit. The first floor contains a 2BR/18A unit rented for $950 per month. The second floor contains two 18R/18BA units. The front unit has a private balcony and is rented for $750 per month, and the other unit is rented for $700 per month. The seller's representative provided you with historical income data suggesting that the seller has collected about $35 per month from each tenant in the form of late fees, pet fees, and laundry income. The property also has a detached garage accessible from the alley that one of the tenants currently rents for an additional $30 per month. A local property manager you met at the wedding of a mutual friend who manages several buildings in this neighborhood told you that the rentals rates at the building appear reasonable and that facancy rates have historically hovered around 5.00% with some seasonable variability around the beginning and end of the fall semester. Historically, rents have increased by about 5% per year, while other income tends to remain flat, increasing maybe 1% per year. As a back-of-the-envelope measure, operating expenses in this market typically run at about 35% of effective gross income. \begin{tabular}{|c|c|} \hline Part A - Questions & Part A-Answers \\ \hline What is the EGI in year 1 ? & $30,420 \\ \hline What effective gross income multiplier does this imply based on the list price for the property? & \\ \hline What is the NOI in year 1 ? & 519,773 \\ \hline What cap rate does that imply based on the list price for the property? & \\ \hline What is the gross income multiplier implied by the list price? & \\ \hline \begin{tabular}{l} Assuming you hold this investment for five years and you anticipate you can sell the property at a \\ capitalization rate of 8.00% based on pro-forma income, how much do you expect to sell the property \end{tabular} & \\ \hline \begin{tabular}{l} for? \\ If closing costs are typically about 5% of the sale price for a seller, what are your expected net ffoceeds \end{tabular} & \\ \hline from the sale at the end of year five? & \\ \hline \begin{tabular}{l} If your investment criteria dictate that the property needs to generate a target yield of 11.00% per year, \\ what is the maximum you could pay for this property while still meeting that objective? \end{tabular} & \\ \hline Would you purchase this property at its current list price? & \\ \hline \begin{tabular}{l} What is your projected internal rate of return (IRR), assuming you purchase the property for its list: \\ price? \end{tabular} & \\ \hline \end{tabular}