Analyze mutual funds in Excel - YouTube

Welcome to the exercise. In this exercise, we would like to

analyse two funds. We have here the returns of two funds.

We would like to know which of these two funds is better.

In the yellow areas, we can already see the solution. Let's delete the

solution and let's develop the solution step by step.

We know from the literature that there are various approaches to compare

mutual funds. For example, we can use the Sharpe ratio, the Treynor ratio or

the Alpha in a CAPM or Carhart four-factor model to analyse these funds.

We have here the annual returns of these funds. Additionally, we have the returns

of our four risk factors: the market risk premium, size, value, and momentum.

We have the risk-free rate in each year. Let's start here at the beginning.

In finance, we are usually working with excess returns. Here we see the

formulas. We see - here for example the Sharpe ratio or the Treynor ratio - that

we are using the returns of the fund in excess of the risk-free rate.

In the first step, we compute the excess returns by subtracting the

risk-free rate from our returns. We fix here column F with a dollar sign because

the risk-free rate is always in the same column. We can now fill to the right and

down.

We see here the last row is row 92. Let's keep this in

mind because it makes life a little bit easier going forward.

First, the Sharpe ratio: the average return or the average excess return

divided by the standard deviation. Let's assume we are working with

continuously compounded returns here. The average excess return is just

AVERAGE of this area. We see that fund B has a higher average excess return than fund A.

The volatility can be computed by using the STDEV.S function.

We see here that fund B also had a higher volatility than fund A. The Sharpe

ratio is defined as the average excess return divided by the volatility of returns.

We see that fund B had a better Sharpe ratio then fund A. The risk to reward

was better. The Sharpe ratio does not take into account the systemic risk

exposure of our fund. Therefore, the Treynor ratio uses the

beta. There are various ways to find the beta. Here the beta is the result of

a regression of the fund returns on the market portfolio. We have an

univariate regression and in this case we can use the SLOPE function.

Our Y variable - our dependent variable - are the fund returns. The returns of our

risk factor - the market risk - is in B4 to B92. We're fixing here

column B with dollar signs such that we can easily fill to the right as you see

here. We will regress fund B on the market returns. The Treynor ratio is the

average excess returns over the beta. We see that the Treynor ratio for fund B

is also highe. Now let's compute the Alpha in a one-factor CAPM model.

Basically, we are doing a regression of funds returns on the market risk.

Here the SLOPE function - we have used here - delivers the beta and the INTERCEPT

function delivers the alpha. We can basically just take this formula copy it,

paste it here, and replace SLOPE by INTERCEPT.

We see that fund B has a higher alpha than fund A in a one-factor model.

We know that there are a number of extensions to one-factor models.

The most popular one is a Carhart four-factor model which has market risk, size, value and

momentum as additional risk factors. When we are doing a multivariate analysis

in Excel- a multivariate regression - we can use the LINEST function.

The LINEST function is a matrix function. The output area is always 5 rows high. We

find here the coefficient, the standard error, the R^2, the F test and the sum of errors in

these rows. The number of columns depend on our independent variables.

We have here in this case 4 risk factors plus 1 constant. Therefore, the output area consists

of 5 columns. Please note that in Excel the sorting is in reverse order.

We see that here the market risk premium is in the first column. Here we have

the constant always in the in the very last column. In the second last

column, we have the first risk factor. Bear in mind that the order is reversed

in Excel. Now: This is a matrix function. We need to mark this whole

area when we start typing. We type LINEST - multivariate regression - our

dependent returns which in J4 to J92. Our risk factors are in

B4 to E92. The next parameter is basically whether Excel shall include

a constant. My recommendation is to include always a constant.

If you enter nothing, Excel includes a constant. Then, we would like to have

some statistics. This is a matrix function. When we have finished typing, we need

to press CMD + ENTER on a Macbook. On a Windows PC, CTRL + SHIFT + ENTER .

I'm working with the MacBook; so I press CMD + ENTER. We see

here the results. We see - for example - this fund has some exposure to the value

factor, exposure to the market risk and in a four-factor vector model the Alpha is 0.78% per year.

We can do something very similar for the second fund. We mark this

area, we have copied the formula and now we just need to replace our

returns. They are now in K4 to K92.

Press CMD + ENTER . We have here in this four factor model an

alpha of minus 0.8%. We see that fund B has an exposure about 0.5

to momentum, no exposure to value and size and a beta of 1 to the market.

In a nutshell, fund A follows a value strategy with a low

beta. Fund B follows the momentum strategy with a normal beta. We see here

fund A has positive returns, a positive alpha. Fund has had a

negative alpha. So: which of these funds is is better? We see here fund B had

higher returns, higher Sharpe ratio, higher Treynor ratio in a in an one-factor

model. But these returns, these apparent excess returns are due to a

different strategy. Fund B has decided to go for a momentum strategy.

This momentum strategy was more successful in this time range than a

pure value strategy. So: We have analysed our funds. Maybe two additional comments

You can also extract the alpha directly, if you combine the INDEX function with

the LINEST function. Sometimes, it's necessary to compute t values.

We can compute t values easily by dividing the coefficient by the standard error.

Let's summarise: we have started with two funds. The target of this exercise

was to analyze these two funds by using the Sharpe ratio, Treynor ratio and the

alphas in a CAPM and Earhart model. We started first by computing excess

returns. Then, we computed the Sharpe ratio. For the Treynor ratio, we use the INTERCEPT and

SLOPE function to compute the alpha in a one-factor model. We use the LINEST function to

compute the risk exposure in Carhart four-factor model and to compute the alpha in

a four-factor model. Thank you very much.