Create custom function for WACC using LAMBDA

Streamlining Financial Models with Custom Functions in Excel

Creating financial models can be a time-consuming process, requiring significant resources to set up basic templates. But what if you could save time by creating custom functions that you frequently use?

One critical aspect of financial modeling is the Weighted Average Cost of Capital (WACC). It represents a blend of a company’s equity and debt cost of capital, based on the company’s debt-to-equity capital ratio. Typically, we calculate the Cost of Debt and Cost of Equity separately and then incorporate them into the WACC formula.

Here’s a suggestion: Let’s encapsulate the WACC formula within a Lambda function. By doing so, we can call it anytime without having to rewrite the entire formula repeatedly. This approach not only saves time but also minimizes the risk of manipulation.

Setting up the WACC Function:

• Go to the Formulas menu and select Name Manager
• Click on New Name
• Assign the Name as "WACC" to the function
• In the Refers to the field, enter the following expression:

LAMDA(Ke,Kd,Ev,Dv,t, (Ke*(EV/(EV+DV)))+(Kd*(1-t)*(Dv/(Ev+DV)))))

• Click OK to save the function

Using the WACC Function:

Now that the WACC function is saved in your Workbook, you can call it just like any other function. The Parameters should be provided in the same order

• P1 : Ke --> Cost of Equity (e.g. cell B1 with value 18.5)
• P2 : Kd --> Cost of Debt (e.g. cell B2 with value 14.5)
• P3 : Ev --> Equity Value (e.g. cell B3 with value 150,000)
• P4 : Dv --> Debt Value (e.g. cell B4 with value 200,00)
• P5 : t ----> Tax Rate for Debt (e.g. cell B5 with value 0.3)

To calculate the Weighted Average Cost of Capital (WACC) enter the following formula in cell B6: =WACC(B1, B2, B3, B4, B5). This will result in a WACC of approximately 13.73%

As of now, this feature is available exclusively for Microsoft 365 users and may be rolled out to other versions soon. Consider upgrading to the latest version of MS Excel to take advantage of this functionality.

Feel free to use the function and share your feedback in the comments!

Have a nice day!!!