what is the equity multiplier, operating expense, before tax IRR, PV of BTCF @IRR sale, going in cap rate, and what is the percent of equity?

Autosave Home Insert Paste Draw Page Layout Formulas Calibri (Body) 11 A A B I U A. fx E41 fx A B C D E F G Purchase Price plus Renovation Expenses Total Acquisition Cost less CMBS Loan Amount Equity \begin{tabular}{r} $15,125,000 \\ $0 \\ $15,125,000 \\ $10,780,000 \\ \hline$4,345,000 \end{tabular} Rental income growth rate Non-rental income growth rate Vacancy and collection loss rate Operating expense ratio Terminal cap rate Selling Cost Prepayment Penalty Rental Income plus Non-rental income Gross Potential Income less Vacancy and Collection Loss Expected Selling Price less Selling Costs Sales Proceeds less Mortgage Balance less Prepayment Penalty Before Tax Cash Flow from the Sale Total Before Tax Cash Flows Going-in cap rate Equity Multiplier Before Tax IRR PV of BTCF from Sale @ IRR Rate Percent of Equity $4,345,000 3.0%3.0%10.0%26.8% 8% \begin{tabular}{|c|c|c|c|} \hline \\ \hline & $1,547,220 & $1,593,637 & $1,641,446 \\ \hline \multicolumn{4}{|r|}{\begin{tabular}{r} $1,641,446 \\ $2,993 \end{tabular}} \\ \hline- & \begin{tabular}{r} $2,993 \\ $1550,213 \end{tabular} & \begin{tabular}{r} $2,993 \\ $1596,630 \end{tabular} & \begin{tabular}{r} $2,993 \\ $1,644,439 \end{tabular} \\ \hline & \begin{tabular}{r} 1,550,213 \\ $155,021 \end{tabular} & \begin{tabular}{r} 1,596,630 \\ $159,663 \end{tabular} & \begin{tabular}{l} $1,644,439 \\ $164,444 \end{tabular} \\ \hline & \begin{tabular}{r} $153,021 \\ $1,395,192 \end{tabular} & \begin{tabular}{r} $159,663 \\ $1436,967 \end{tabular} & \begin{tabular}{r} $164,444 \\ $1,479,095 \end{tabular} \\ \hline & \begin{tabular}{l} $1,395,192 \\ $415,457 \end{tabular} & \begin{tabular}{l} $1,436,967 \\ $427,897 \end{tabular} & \begin{tabular}{r} $1,479,995 \\ $440710 \end{tabular} \\ \hline & \begin{tabular}{l} $415,457 \\ $979,735 \end{tabular} & \begin{tabular}{r} $427,897 \\ $1,009,070 \end{tabular} & \begin{tabular}{r} $440,710 \\ $1,039,286 \end{tabular} \\ \hline & \begin{tabular}{l} $979,735 \\ $964,473r \end{tabular} & \begin{tabular}{r} $1,009,070 \\ $964,473 \end{tabular} & \begin{tabular}{r} $1,039,286 \\ $964,473 \end{tabular} \\ \hline & $15,261$964,4/3 & $44,597$964,473 & $74,812$964,473 \\ \hline & & & $14277208 \\ \hline & & & \begin{tabular}{r} 14,272,098 \\ $285,442 \end{tabular} \\ \hline & & & \begin{tabular}{r} $285,442 \\ $13,986,656 \end{tabular} \\ \hline & & & \begin{tabular}{r} $13,986,656 \\ $9,226,507 \end{tabular} \\ \hline & & & \begin{tabular}{r} $9,226,507 \\ $107,800 \end{tabular} \\ \hline & & & \begin{tabular}{r} $107,800 \\ $4,760,148 \end{tabular} \\ \hline & & & \\ \hline$4,345,000 & $15,261 & $44,597 & $74.812 \\ \hline 8% & 72.7% \end{tabular} & & & = \\ \hline$11,868,034 & 1 & & \\ \hline \begin{tabular}{r} 8,034 \\ 273 \end{tabular} & PV in cell CAO & & \\ \hline \end{tabular} Review View Automate Tell me Wrap Text General Merge 8 Center v $=% , 48 0 Data Merge \& Center \begin{tabular}{|c|c|c|c|c|} \hline c & D & E & F & G \\ \hline$15,125,000 & & & & \\ \hline \$o & & & & \\ \hline$15,125,000 & & & & \\ \hline$10,780,000 & & & & \\ \hline$4,345,000 & & & & \\ \hline & & & & \\ \hline & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline & 3.0% & 3.0% & 3.0% & 3.0% \\ \hline & 10.0% & 10.0% & 10.0% & 10.0% \\ \hline & 26.8% & 26.8% & 26.8% & 26.8% \\ \hline & & & 7.5% & \\ \hline & & & 2.0% & \\ \hline & & & 1.0% & \\ \hline & & & & \\ \hline & $1,547,220 & $1,593,637 & $1,641,446 & $1,690,689 \\ \hline & $2,993 & $2,993 & $2,993 & $2,993 \\ \hline= & $1,550,213 & $1,596,630 & $1,644,439 & $1,693,682 \\ \hline & $155,021 & $159,663 & $164,444 & $169,368 \\ \hline & $1,395,192 & $1,436,967 & $1,479,995 & $1,524,314 \\ \hliner & $415,457 & $427,897 & $440,710 & $453,907 \\ \hline & $979,735 & $1,009,070 & $1,039,286 & $1,070,407 \\ \hliner & $964,473 & $964,473 & $964,473 & \\ \hline & $15,261 & $44,597 & $74,812 & \\ \hline & & & & \\ \hlinez & & & $14,272,098 & \\ \hline & & & $285,442 & \\ \hline & & & $13,986,656 & \\ \hline a. & & & $9,226,507 & \\ \hline & & & $107,800 & \\ \hline & & & $4,760,148 & \\ \hline & & & & \\ \hline$4,345,000 & $15,261 & $44,597 & $74,812 & \\ \hline 8% & - Calculate & & & \\ \hline 0.29 & & & & \\ \hline72.7% & & & & \\ \hline \end{tabular} Exhibit 2 Pro-forma Assumptions Restrictions on the CMBS loan will require Bascom to fund all the renovation expenses with equity. Without renovating, Bascom estimates that it will be able to sell the property at a terminal cap rate of 7.5% at the end of three years. Capitalize the year 4 NOI to estimate the selling price. With renovations, the terminal cap rate is expected to be 6.1%. In either case, Bascom will have to pay a 2% selling commission when the property is sold. Develop pro-formas that estimate expected cash flows for this income producing property for a three-year holding period if: (1) the property is not renovated; and (2) if the property is renovated. In addition, compute the expected return on the renovation cash flows. $10.780MM with 20 years remaining on the loan's amortization period. The annual interest rate on the monthly payment mortgage is 6.5%. There is a 1% prepayment penalty if the loan is repaid prior to maturity. Bascom estimates that the renovations will cost $4.055MM($20,902/ unit). The renovations will commence immediately after the property is acquired. Rents have not changed in years and Bascom estimates that it will be able to increase rents about 52% from their current levels following the renovation. For purposes of the investment pro-forma you may assume that the renovation expenses and subsequent rent inereases will occur at acquisition. Exhibit 1 provides the unit sizes, number of apartments, and both pre-renovation and postrenovation rents for the property. Currently, the average rent for studio apartments is $415.00/ month; $665.00/ month for two-bedroom units and $910.00/ month for three-bedroom units. If the property is not renovated, the first year's annual rental income is expected to be $1,547,220. The rents in Exhibit 1 are end of year 1 rents. Rental income is expected to increase 3% per year for the foresecable future (Exhibit 2 ). In addition to rental income, the property is expected to generate an additional $99,780 in non-rental ineome (application fees, pet deposits and fees, and parking revenue). Non-rental income is also expected to increase 3% per year. The current vacancy and collection loss rate is 10.0% and the current operating expense ratio is 26.8%. These rates are expected to remain constant over the next four years. The renovations will allow Bascom to increase average rents for studio apartments to $632.14; to $1,012.95 for two-bedroom units and to $1,386.15 for three-bedroom units. The renovations will also allow Bascom to increase rents 6% per year. The $99,780 in non-rental income is expected to increase 3% per year. The renovations are expected to reduce the vacancy and collection loss rate to 5% and the operating expense ratio to 17.6%. The Bascom Group, LLC ("Bascom") was formed in 1996 by Derek Chen, Jerome Fink, and David Kim. The original business plan was to identify, acquire, and renovate 300 to 500 unit apartment properties in desirable locations. Primary sources of properties for their strategy include foreclosures, short sales, and repositions. Since 1996, Bascom has purchased and renovated 163,118 apartments and completed over $16.2B in multifamily and commercial valueadd transactions in Arizona, California, Colorado, Connecticut, Florida, Georgia, Hawaii, Illinois, Nevada, New York, the Pacific Northwest, Tennessee, Texas, Utah, Washington and other select markets in the U.S. Since its founding. Bascom has supported local community groups. The company encourages employees to volunteer 10 hours per quarter to an institution of their choice. Bascom matches hours spent volunteering with additional vacation time. In 2015, Bascom employees completed over 100 hours of volunteer work with local charitable organizations. Bascom's professional and community outreach efforts have been recognized by numerous professional and charitable organizations. Bascom was recognized as one of the Best Places to Work in Multifamily, was a Finalist for the Best Repositioning of a Multifamily Asset by the National Associate of Home Builders; and by Ernst and Young as the Entrepreneur of the Year. Bascom is currently evaluating the acquisition of a value-add multifamily property in Flagstaff, AZ. The Commons at Sawmill is a 194 unit apartment community located at 901 South O Leary Street in Flagstaff. Built in 1992, the property suffers from substantial deferred maintenance and has an archaic exterior, interior water damage, mold, failing appliances and outdated interiors. Failing single-pane windows and exterior concrete masonry unit (CMU), or conerete block, construction has created significant mold problems throughout the property that will require extensive repairs. In addition to updating, the exterior faade will need better moisture protection. The property is well located in the Flagstaff apartment market. It is adjacent to the University of Northern Arizona, home to about 17,500 students. In addition, the property is within walking distanec of downtown Flagstaff and Aspen Place at the Sawmill-a neighborhood shopping center containing over 30 restaurants and retail establishments, including a Whole Foods grocery store and an REI. The property is pet-friendly and close to walking/hiking/bicycling trails. The property is located about 6.5 miles from the Flagstaft Pulliam Airport. The renovations will allow Bascom to increase average rents for studio apartments to $632.14; to $1,012.95 for two-bedroom units and to $1,386.15 for three-bedroom units. The renovations will also allow Bascom to increase rents 6% per year. The $99,780 in non-rental income is expected to increase 3% per year. The renovations are expected to reduce the vacancy and collection loss rate to 5% and the operating expense ratio to 17.6%. Exhibit 1 Rent Roll