• 075 07/01/2018

    This video explains how to use SUMIF and the INDEX + MATCH function combination in Excel to dynamically reference columns in a financial model. This has been particularly useful to me when I am organizing a company's data by location or product category.

    Excel Trick: This formula relies on one input in the INDEX function. If you look to the formula below you will notice that the "row_num" input in the INDEX function is the value zero (highlighted red for emphasis). With this input the INDEX function will return the entire column.

    =SUMIF( ... ,INDEX($B$13:$G$13,0,MATCH($C9,$B$13:$G$13,0))) 

    Note: Ellipsis used above to emphasis INDEX + MATCH portion of the formula. Please see video for the inputs that replace the ellipsis. 

    If you are unfamiliar with the SUMIF function I would recommend watching this video first. A tutorial on the INDEX + MATCH function combination can be found in the Excel for Models video series (subscriber content).

    Download template in videoLINK

     

 



  • 074 07/01/2018

    This video provides a quick introduction to the =SUMIF() function in Microsoft Excel. The video also provides a quick demonstration of what is likely the most common use of =SUMIF() in a financial model: organizing monthly information into quarters and annual periods (skip to 1:15 for this example).

     

 



  • 073 01/12/2018

    The calculation behind the catch-up provision that determines the general partner's (GP) carried interest at a private equity fund can cause some confusion. In this post we will explain the math in the Excel template available on ASM.

    For reference, the calculation refers to the second example cited in this DISTRIBUTION WATERFALL (follow link for Excel template). The language for the second example cited in the Excel file is as follows:

    First, 100% of all cash inflows to the LP until the cumulative distributions equal the original capital invested plus some preferred return.  

    Second, a "20% catch up" to the GP equivalent to 20% of the of the distributions realized in step 1 plus the distributions realized in this step. 

    Third, thereafter, cash flows in excess of distributions made in step 1 and step 2 (if any) are distributed 80% to the LP and 20% to the GP. 

    Based on the emails I receive, most of the confusion comes from the calculation used in the second step. So let's reframe the objective:  In step 2 you want the GP to receive 20% of all distributions up to and including step 2. For this exercise, think about (All Distributions up to and including Step Two) as all cash flows received by both the GP and LP up to and including Step Two.

    If the GP is supposed to get 20% of (All Distributions up to and including Step Two), it follows that the LP has received 80% of (All Distributions up to Step Two):

    (All Distributions up to and including  Step Two) * 0.8 = (LP First Distribution)

    (All Distributions up to and including Step Two) = (LP First Distribution) /0.8 

    And since the GP only receives what is not allocated to the LP (what the GP receives = the catch up):

    (Catch Up) = (LP First Distribution) /0.8 - (LP First Distribution)

    If subtracting the "(LP First Distribution)" is confusing, think about it this way (different formula, same result):

    (Catch Up) = ((LP First Distribution) /0.8)*0.2

    To help this sink in I thought I would provide an additional way to think through this exercise: The Catch Up is equal to 20% of all cash flows received in both steps 1 and 2. It follows that:

    C = Catch Up

    P = LP return in First Distribution

     

    C = 0.2*P + 0.2*C

    0.8*C = 0.2*P

    C = P*0.2/0.8

     

    C = P * 0.25

    For the exercise I thought the first approach would make it easier to follow the formulas (I find the 0.25 in the second formula has the potential to be confusing), but generally multiple examples help.

    Click HERE to have similar posts sent directly to your inbox.

 




 



Models are:
 
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above

 

 


*Answers a, b, c, d, e, f and g are all correct.