This is an extension to my previous blog. The formula in this example is taken from my previous blog.

Presuming you want to analyze how a change in input values changes the result of the formula. Like when beta ranges from 0,6 to 1,6 and return for market portfolio from 2,5 to 6,5. What are the expected values for the capital asset.

Make a table as presented above. Formula in B6 is shown in B5. We select return for market portfolio and beta as variables. The risk-free rate is static 0,5 %.

Formula in E3 is written in E2.

Activate the data area and select data – what-if analysis – data table

Row is beta and column return for market portfolio. Press ok.

The result.

If the return for market portfolio is 4 % and beta is 0,8 and the risk free rate is 0,5 % then the formula returns the value of 3,3 %.

We can check this.

With custom formula.

Manually calculated.