Rolling beta

You really need to explain the terms you are using. Mom is usually one's female parent!! It would also help if you explained what beta is and attached a workbook containing sample data.

I want to investigate the performance of a portfolio over a 10 year period. I am using Carhart 4-factor model which is an extended CAPM model that use more factors to explain alpha (excess return).

It is defined as:

Excess return portfolio i = α + β*MKT + β*SMB + β*HML + β*MOM

Where:

β= Sensitivity of portfolio i to a change in one of the factors.
〖MKT〗= Return on the market portfolio.
〖SMB〗= "Small minus big" Difference in return of small cap portfolio and large cap portfolio.
〖HML〗= "High minus low" Difference in return of high book-to-market portfolio and low book-to-market portfolio.
〖MOM〗= "Momentum" Difference in return between the best performing portfolio and the worst performing portfolio.

The meaning of the factors is not really important. Just see it as a multiple regression model. I want to know the how to make a "rolling regression"/ "rolling beta" in excel. I would like to see how the betas for the 4 factors are changing over time. My sample data is over the financial crisis 2007-2009 so I expect the parameters in the regression not to be constant. I just do not know the technique how to chow this.

Do you get what I mean?

Thanks! Sample data.xlsx

How can I perform this in excel? I have heard something about using the Linest function?

If you have not used the LINEST() function for something like this, then I would start by reviewing the help file and examples of the LINEST() function: https://support.office.com/en-us/art...rs=en-US&ad=US

I'm not sure I understand your regression equation. It looks like excessi=a+b*(mkt+smb+hml+mom), does that look right? This form of the equation is in the y=mx+b form, so it should work very well with the LINEST() function.

Here's how I see this working out:
1) Enter data in spreadsheet. INclude "transformation" for the known_x (=sum(C2:F2) copied down).
2) Enter LINEST() function. Pay careful attention to relative and absolute references here, so that it will be easy to copy the function down.
3) Copy LINEST() function to get the regression for each desired time period.
4) Collect time and beta values, if needed.
5) Create chart.