$$ Use Excel to build a $5-$year Pro Forma for an Office Building

$$ Calculate a Property$$s NPV$,$ IRR, Cash$-$on$-$Cash $($Coc$),$ and Modified Internal Rate of Return

$$ Use $$DataTable$$ Function to Conduct a Sensitivity Analysis using Excel

Due Date:

Upload the completed assignment to Canvas before $11$:$59$pm on Feb $22.$ You may not work with a

partner on this assignment. No credit for late work.

Instructions:

$1.$ Create an Excel File from the proforma template.

$2.$ Enter the following property assumptions:

a$.$ In cells C$4$:C$11$ of the $$Property$$ Worksheet: Acquisition Costs $=$ $$1,900,000$; $1$st

Year PGI $=$ $$280,000$; Expected Vacancy: $10\%$ of PGI; Operating Expenses: $40\%$ of

EGI; Capital Expenditures: $$18100$ per year; Annual Growth Rate of PGI: $3\%$;

Brokerage Expenses: $5\%$; Terminal CAP Rate: $8\mathrm{.}5\%$

b$.$ In cells C$15$:C$16$ of the Property Worksheet: Alternative Return to Equity: $7\%$;

Finance Rate of Negative Cash Flows: $5\%$

$3.$ Calculate the First Year NOI for the Property.

$4.$ Calculate the NOI for year $2-5$ of the property.

$5.$ Calculate the Property$-$Specific Cash Flows in C$31$:G$31$ assuming the property is sold at the

very end of the $4$th year.

a$.$ Link the $$Acquisition and Improvements$$ amount in cell C$4$ to C$27.$ This should be a

negative number $($which may be highlighted in red and in parentheses$).$

b$.$ Link calculated NOI in row $25$ to cells D$28$:G$28.$ These should be positive.

c$.$ Calculate the Net Sale Proceeds in cell G$29.$ Divide Year $5$ NOI by Terminal Cap Rate

and multiply by $1$ minus brokerage fees. These should be positive.

d$.$ Add all three amounts for each year in cells C$31$:G$31.$

$6.$ Use Excel Functions to calculate NPV$,$ IRR, CoC, and MIRR in cells F$4$:F$7.$

a$.$ Important Note: The NPV$()$ assumes that the first period is period $1.$ In this case, you

have the purchase in period $0.$ Type $=$NPV$($C$15,$D$31$:G$31)+$C$31$ in F$4$

$7.$ Conduct a Sensitivity Analysis of NPV in cells K$6$:M$11$ using Two$-$Variable $$DataTable$$

a$.$ Type $=$F$4$ in cell J$5,$ Type $9\%,10\mathrm{.}5\%,$ and $12\%$ in K$5$:M$5,0-5\%$ in K$6$:K$11$

b$.$ Highlight cells J$5$:M$11$

c$.$ Select $$What$-$If Analysis$$ From Data Tab and Further Select $$DataTable$$

d$.$ Type $$$C$$11$ for Row Input Cell and $$$C$$9$ for Column Input cell. Select $$ok$$

$8.$ Conduct a Sensitivity Analysis of MIRR with the same assumptions as in Q$7$