Assessment #$6-$ Pro$-$Forma

You have client who is looking to invest in an apartment complex, and you have been asked to prepare a basic

pro forma for them to understand if it is worthwhile and so they can go talk to the bank about financing the

project. The property consists of $300$ units with an average monthly rent of $$2,400$ per unit with an expected

growth rate of that rent at $3\mathrm{.}0\%$ per year. You expect to have a $5\%$ vacancy and loss each year and $32\%$ of the

effective gross income to go towards operating expense. You are also told that the complex makes roughly

$$25,000$ annual in other income $($and is not impacted by vacancy and loss$)$ from rental of the guest house and

some vending machines around the property, and this increases proportional the rent and is considered when

calculating the operating expenses. Your client wants to hold the investment for $5$ years. The current market

cap rate on this type of investment is $6\%$ and expect it to be $6\mathrm{.}25\%$ at the end of the five$-$year hold. Also, the

client is anticipating the bank to offer a $65\%$ loan$-$to$-$value so would like to know how much equity they are

going to need to raise to make the deal happen. Based on the given information prepare a pro forma that

shows the expected annual cash flows, purchase price, needed equity, expected sale price, best discount rate

for the project, project IRR, NPV$,$ as well as the maximum price that your client should pay for the property, as

a

good negotiating point for them if they pursue the project. Are my inputs so far correct? Please help with the blank cells as well and their formulas.