Please help me fill in the Excel template step by step and show the formula and calculation step by step. Please.
PREFERRED EQUITY_BEST BOX You have been presented with the off-market opportunity to personally purchase a 40,000SF, singletenant retail store leased to Best Box, a regional leader in consumer electronics and appliances. You are a personal friend of the Best Box CEO, and she recently approached you about purchasing their flagship store. Upon selling the store, Best Box would leaseback the property from you on a 10-year lease beginning at $25ps with an annual rent step of $1.00 psf for years 1-10. You've been working hard the last few years at A.CRE Development and have banked several six-figure promotes. Those earnings have been sitting in a high-yield savings account, awaiting the right opportunity. This could be a great opportunity to put those funds to work for 5-10 years at a yield substantially better than your savings account. Before making a decision, you decide to analyze the investment. You open up an Excel workbook and begin to model the cash flows. ASSUMPTIONS - RSF is 40,000 . - Income is initially $25ps with an annual step of $1.00psf - Asking cap rate is 6.25% on year 1 NOI. - Disposition cap rate at the end of a 5-year hold period is 6.50% based on Year 6 Rent. - The investment is financed by a 10 -year loan at 70% LTV, 3.25% rate, monthly pay, and a 30 year amortization period. The loan has call protection from month 25 to month 117 in the form of yield maintenance. Assume reinvestment rates are 2.75% at the time of sale. QUESTIONS - What is the asking price amount? - What is the unlevered IRR? - What is the levered IRR? - What is the year 3 free and clear return (i.e. Return as if there is NO leverage)? - What is the year 3 cash on cash return (i.e. Return after leverage on the investment)? EQUITY CONTRIBUTION You approach your cousin Vinny about contributing 75% of the required equity. Your cousin is interested subject to understanding what his equity returns would be. You offer a 4% non-cumulative preferred return and then to split all cash flow pari passu and pro rata based on ownership share. You also ask for a 1\% acquisition fee based on his equity contributed, a 1% disposition fee of net sale proceeds allocated to him, and a 0.50% of an annual asset management fee based on his equity invested. - What is your levered IRR, net of fees? - What is your cousin's levered IRR, net of fees? Building SF Rent Growth rate-Rent Cap Rate Year 1 Terminal cap rate Purchase Price Debt: Debt Limitation 12 PV FV Rate Nper Pmt Type Equity \begin{tabular}{|l|l|} \hline 1 \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \\ \hline \end{tabular} YEAR Rent 0 0
