Creating a Custom CAPM Function Using Excel’s Lambda Feature

In Excel, you can leverage the Lambda function to create user-defined custom functions. Let’s build a function that calculates the Expected Rate of Return using the Capital Asset Pricing Model (CAPM) formula within the Lambda framework.

Setting Up the CAPM Function :

• Go to the Formulas menu and select Name Manager.
• Click on New Name
• Assign the Name as "CAPM" to the function
• In the Refers to the field, enter the following expression: LAMBDA(Rf, Ba, Rm, Rf + (Ba * (Rm - Rf)))
• Click OK to save the function

Using the CAPM Function:

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

• Parameter 1 (Rf): Risk-Free Return (e.g., cell B1 with a value of 8).
• Parameter 2 (Ba): Beta of the Security/Stock (e.g., cell B2 with a value of 1.5).
• Parameter 3 (Rm): Market Return (e.g., cell B3 with a value of 15).

To calculate the Expected Rate of Return, enter the following formula in cell B4: =CAPM(B1, B2, B3). This will yield an Expected Rate of Return of 18.5%.

It will be more handy to transform the formulas into functions using Lambda.

Could you use the function and share your experience in the comments?

#FinancialModelling #Lambdafunction #CAPM #FinancialAnalysis