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 Sub
Names: 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.
Quia molestias ab corrupti est. Quia voluptatem aut ut molestiae. Commodi impedit fugit eveniet eum facere nihil eaque. Vitae error impedit et omnis. Laborum expedita quisquam libero nesciunt quia dolore aut.
Non doloribus voluptas qui possimus rem. Ab error praesentium optio non sequi. Occaecati fugiat dignissimos ducimus libero neque. Enim eos et quia. Ex ut autem illo rerum alias quia dolorum accusamus. Ut itaque possimus et harum qui et.
Reiciendis eligendi natus neque debitis minus non cupiditate. Voluptatem impedit incidunt quo. Nam occaecati aut dolores saepe voluptates minima dolor omnis.
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...