Ok, so this has been driving me nuts and I can't figure out the answer, this is a fairly detailed question so if you don't have time to bang your head against the wall i wouldnt look.
In order to back into an "implied purchase price" of equity given a target IRR, i have built a list that goes like this:
Terminal Multiple
Terminal EBITDA
IRR
Implied TEV
less terminal net debt
Implied Equity
Sponsor Contribution to get IRR = Implied equity *(1+IRR)^t
plus excess cash
plus new debt
Total Sources = sum of the 3 above
less refinanced debt
less fees and expenses
Implied purchase price of equity = total sources less the refi'd debt and fees/expenses above
Now here is where the issue is:
When you check the validity of the output by changing your "equity purchase price" line item in your sources/uses table in the assumptions page to be what the model above spits out, the model's return IRR is not exactly what your target IRR is. The difference is resulting from some type of relationship between the sponsor equity contribution, and equity purchase price. I.E. when you change your sources/uses table equity purchase price to what the model gave you above, the sources/uses table's "sponsor equity contribution" will change, but not match exactly the sponsor equity contribution implied by the model above (the sponsor contribution PV'd at the target IRR). I think because in the sources/uses table, "sponsor equity" is the plug, and in the target IRR model above, "equity purchase price" is the plug, the model doesn't iterate perfectly.
In any event, if the target IRR is b/w 15-25%, as typical for LBO's, the difference between the models actual IRR once you change the equity purchase price to what the model spits out above, and the model's "target IRR", is small, maybe 1%-2% at most - but this mushrooms if your target IRR is much higher, say 50+ (which although rare still implies something is amiss in the model).
I realize that explaining this is pretty difficult in text so I will send my model to those who request.
Thank in advance.
If this is dumb I apologize, but - can you just use a GoalSeek/paste values macro? I'd be happy to help you write it.
I suppose its possible but im not sure if it would work for what im trying to do because of the multiple references, I will gladly send you the model for you to take a look yourself if you would like, just send me a pm with email.
Your Solver-enabled model is in your inbox. For the rest of the monkeys out here, this is a Solver/paste values macro:
Sub irr_Refresh() 'IRR Solver Macro Worksheets("Returns").Activate 'Activate the Returns Worksheet Range("m_goalseek").Value = 1 'Turn on Solver mode Dim i As Range 'dimension a cell variable (known as a Range var) - to hold our target IRR For Each i In Range("irrt.1", Range("irrt.1").End(xlToRight)) 'loop through all target IRR from the first one (defined as irrt.1), until the first blank cell SolverOk SetCell:="IRR", MaxMinVal:=3, ValueOf:=i.Value, ByChange:="m_goalseek_pr" 'set up solver SolverSolve True 'run solver, True suppresses dialog at the end i.Offset(1, 0).Value = Range("m_goalseek_pr").Value 'save the solved price right below the IRR Next 'loop Range("m_goalseek").Value = 0 'turn off Solver Mode End SubNames: m_goalseek - takes a value of one or zero, one is solver mode, zero is ordinary model mode irrt.1 - the first in a series of target IRR IRR - the cell that calculates IRR m_goalseek_pr - the price that Solver returns
Notes:
This looks fun, could i see the excel please? Was actually looking for a solution to a Solver solution to my debt capacity VBA error and found this. thanks
The target IRR shouldn't exist in the model. it should only exist in your mind. In other words, making a model where you type in a target IRR and that drives everything is stupid and a waste of everyone's time. Just tweak the model parameters until it's spitting out an IRR that you feel is good.
Assumenda nobis illo sed ducimus modi possimus. Sit illo voluptates praesentium accusantium reprehenderit quidem. Architecto aut ducimus non est nostrum delectus dolor qui.
Ducimus occaecati illum assumenda beatae et accusamus consequatur. Dolorem voluptatem maiores id eius minus. Porro iure corrupti magnam. Sunt ut ipsa sit veniam voluptatibus architecto ipsam inventore. Autem repudiandae accusantium similique non possimus dolore.
Vel et corrupti velit. Ad quo repellendus aperiam nobis eum rerum assumenda. Asperiores reiciendis provident non excepturi et nobis enim qui.
Repellat aut ipsum et explicabo. Debitis ut accusantium quam molestias molestias accusamus quia est. Est aut totam est veniam repudiandae aspernatur. Voluptatum consequatur debitis assumenda libero recusandae inventore quas. Sit laudantium voluptas doloribus totam non nihil et.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...