Congratulations, you have your apartment under contract for sale.
When announcing a sale to your passive investors, the primary metric they will want to know is “how much money will I receive at sale?”
Two of the most common return metrics used in apartment syndications are the equity multiple (EM) and the internal rate of return (IRR). However, these are fairly difficult calculations to make.
Rather than create a blog post with a bunch of formulas, I created a Disposition Calculator. Using the Disposition Calculator, you can determine how much money your passive investors will receive upon the sale of an apartment syndication deal. The model will also output the overall IRR and equity multiple for the project and to your passive investors.
The purpose of this blog post is to outline how to properly fill out the Disposition Calculator. (Only input data into cells highlighted yellow).
Click here to download the free Disposition Calculator.
“Cash From Ops and Escrow Refund” tab
- Input the remaining cash from operations at sale in cell B1: This is the cash that remains in the operations account for the asset. Make sure you input a positive number.
- Input the fees that still need to come out of the bank account starting in cell B3: These should be inputted as negative numbers. Currently, you can input the final loan payment (debt service), the final asset management fee (AM Fee), and escrow or reserve payment (Escrow/Reserve). If you need to pay additional fees, you can create additional cells, but make sure you adjust the total formula (currently in cell B6) to reflect any added cells. This total number is the cash from operations that is available for distribution upon sale.
- Fill out the second data table to calculate any refunds you will receive upon sale: Currently, you can input data for property tax and insurance escrow (for example, if you paid upfront rather than on a monthly basis), your lender reserves balance, and leftover capex refunds. Input the current balance in row 10 as positive numbers and any additional payments that you will need to make but haven’t made yet in row 11 as negative numbers. Create additional cells if needed, making sure to update the total formula, which is currently in cell E14. The total is the escrow refund you will receive at sale.
“Disposition Calculator” tab
- Input the sales price in cell C3.
- Input the outstanding loan balance in cell C5: This should be inputted as a negative number.
- Input consultant costs: If you used any type of consultant or broker to aid you in the sales process, input the cost in cell C6. This should be inputted as a negative number.
- Input lender exit fee: If you are required to pay your lender an exit fee or prepayment penalty, input the cost in cell C7. This should be inputted as a negative number.
- Input title costs: Input the costs associated with title in cell C8. This should be inputted as a negative number.
- Input your disposition fee: If you’re charging a GP disposition fee, input the cost in cell C9. This should be inputted as a negative number.
- Input the total closing cost and legal fees in cell C10: This should be inputted as a negative number.
- Input prepaid rent and security deposits: At sale, you will need to give the buyer the security deposits and prepaid rents for the month of sale. Input the total prepaid rents and security deposit cost that will be given to the buyer in cell C11. This should be inputted as a negative number.
Cell C12 and C13 will automatically populate from the “Cash From Ops and Escrow Refund” tab. But if you want, you can perform the calculation offline and input the total refunds amounts in C12 and C13. If this is the approach you use, make sure you are inputting positive numbers.
- Input the accounts payable and other accrued expenses that still need to be paid in cell C14: This should be inputted as a negative number.
Cell C15 will automatically populate once you input the project and LP cash flows in the Distribution Schedule (starting in step 17). This is the final monthly or quarterly distribution that will be paid out to investors.
- Input the total cash account balance in cell C16: This should be inputted as a positive number since you will receive this money at closing.
- Input any other sales fee or expense that is not listed above in cell C17: If it is an expense, it should be a negative number. If it is a refund, fee, etc., it should be a positive number.
Cell 18 will calculate the total profits from sale by subtracting the sales fees from the sales price.
- Input the total equity investment in cell C19: This is the amount of money invested by the LPs. This should be inputted as a negative number.
Cell C20 will subtract the equity owed to investors from the total proceeds from sale to calculate the total profits from sale.
Cell C21 will calculate the total cash flow and profits generated by the deal prior to sale from the Distribution Schedule.
- Input the percentage of profits that the GP receives in cell C22: From example, if the profit split is 70/30 LP/GP, input 30%.
Cell C24 is the money owed to the GP based on the profit split (i.e., the GP catch up). Cell 23 calculates the GP’s portion of the total cash flow and profits generated by the deal prior to sale based on the profit split in cell C22. The total cash flow and profits actually distributed to GP from the Distribution Schedule is subtracted from this number to determine the GP catch-up such that, for example, 30% of the cash flow and profits thus far go to the GP and 70% goes to the LP.
Cell C25 is the remaining profits after all sales fees, returning LP equity, and distributing the GP catch up. The remaining profits that go to the GP is calculated in cell C26 and the remaining profits that go to the LP is calculated in cell C27.
Lastly, you need to fill out the distribution schedule, which starts in cell F2.
- In cell F2, input the purchase date.
- Starting in cell G2, input the distribution dates: If you had more than eight distributions, you will need to create additional cells. If you create additional cells, make sure you update the IRR and Equity Multiple formulas to include those added distributions (cells P4, P6, Q4, and Q6)
- Starting in cell G4, input the total project cash flow and profits for the project.
- Starting in cell G6, input the total cash flow and profits to the LPs.
- Starting in cell G10, input the total cash flow and profits to the GP.
Once you’ve completed filling out the calculator, the outputs are the IRR and Equity Multiples for the project and to investors, plus an example of how much money an investor would make if they invested $100,000.
Click here to download the free Disposition Calculator.
Disclaimer: The views and opinions expressed in this blog post are provided for informational purposes only, and should not be construed as an offer to buy or sell any securities or to make or consider any investment or course of action.