EXCEL SOLVER EXAMPLES EQUITY MANUAL

Project revenue: Revenue is expected to grow 10% annually.It is reasonable to round your intermediate calculations to the nearest integer in carrying over calculations to the next step.)

Also note that the numbers might not agree perfectly because of rounding. (Notice that, because the exit value at the end of Year 5 will be based on a forward EBITDA multiple, we must calculate six year’s worth of income statement, not 5. The given information assumes debt to equity ratio of 60:40 for the purchase price.ĭebt portion = 60% × $200 million, or $120 million.Įquity portion = 40% × $200 million, or $80 million. Calculate the debt and equity funding amounts used for the purchase price. $40 million in EBITDA (which represents a 40% EBITDA margin on $100 million in revenue) multiplied by 5. Using a 5.0x entry multiple, calculate the price paid by multiplying by Year 1 EBITDA.

Assume all debt pay-down occurs at the moment of sale at the end of Year 5 (this eliminates the iterative/circular dependency between debt pay-down/cash balances and interest expense in a computer-based LBO model).

XYZ exits the target investment after Year 5 at the same EBITDA multiple used at entry (5.0x FTM EBITDA).

Depreciation is expected to equal $20 million each year.

Operating working capital is expected to increase by $5 million each year.

Capital expenditures are expected to equal 15% of sales each year.

EBITDA margins are expected to remain flat during the term of the investment.

Revenue is expected to increase by 10% year-over-year (y-o-y).

ABC expects to reach $100 million in sales revenue with an EBITDA margin of 40% in Year 1.

Assume the weighted average interest rate on debt to be 10%.

The debt-to-equity ratio for the LBO acquisition will be 60:40.

XYZ Private Equity Partners purchases ABC Target Company for 5.0x Forward 12 months (FTM) EBITDA at the end of Year 0.

You have a great relationship with your lender, and you could utilize more leverage if necessary.

Current underwriting projects a 14.57% leveraged IRR. Let's say the property's price is firm at $12.5 million. Still, Google searching the Solver add-on application may be worth your time if you are looking for what-if analysis on multiple variables at once. The solver tool is far more advanced and goes beyond the scope of this article. You can even use Goal Seek's more sophisticated counterpart 'Solver' to set up rental increase thresholds for individual floorplans on a value-add analysis. Solving for a sponsorship profit goal by altering preferred return A few helpful instances are:įiguring out a loan amount that maximizes IRRĬalculating the mortgage interest rate that maximizes IRRįorecast a respectable IRR toggling purchase priceįorecast an acceptable IRR toggling residual sales price Goal Seek examples are endless in the context of multifamily real estate analysis. Other Useful Applications of Excel Goal Seek

EXCEL SOLVER EXAMPLES EQUITY MANUAL

In the example I referenced above, the price of the property would be the manual input. Goal Seek would figure out what the price needs to be to achieve a cash-on-cash yield of 8%. The desired outcome will be a target value that is a calculated cell (black text excel formula ) in the Tactica workbook. Normally, these desired outcomes will relate to a specific return metric, such as IRR, equity multiple, cap rate, cash-on-cash, or net profit. You can use the Goal Seek analysis tool when you need to go figure out a manual input (brown text cell) that will attain a desired result. Instead of guessing different prices and manually checking the cash-on-cash (that happens to be on a separate tab), Microsoft Excel's Goal Seek Analysis can make this procedure effortless and instant. Some of the most common questions I receive from Tactica template users revolve around 'what-if analysis.' Questions like, 'How can I quickly figure out how much to pay for a property that will give me a cash-on-cash return of 8%.'

Excel solver examples equity