Catching Up Elegantly: An Algebraic Solution
Eun Oh, Managing Director of Real Estate Solutions, offers a simple algebraic solution for modeling a private equity "Catch-up" on MS Excel that enables analysts to run iterations of the Catch-up model more efficiently.
This is an extract from The Real Estate Finance Journal, Fall 2020 issue. The whole publication is available at https://store.legal.thomsonreuters.com/law-products/c/The-Real-Estate-Finance-Journal/p/100029115
"The concept is widely understood; however, those who tried to model it in a spreadsheet may find the math rather tricky and elusive. In order to solve for the circular logic, some use "Goal Seek" or the "Solver" function in MS Excel which requires manual intervention every time an input changes, making the model cumbersome to use. With additional functionalities such as clawback or sensitivity tables, you will find yourself (or your analyst) running the "Goal Seek" hundreds of times with every input change. Such agony can be mitigated by writing a "macro," but at this point, you will have to admit that something is not quite elegant.
A small imperfection in a financial model can quickly multiply itself and make the model heavy and clunky, if not inaccurate. To avoid any unnecessary complication, formulas should be as simple as can be, and if possible, one should avoid the use of Goal Seek/Solver or any break points where manual interventions are necessary."
Posted with permission from The Real Estate Finance Journal. All rights reserved.