Computing The Price Regression Line (PRL) Using Excel by W.G.S. Brown
Approach: First download into a spreadsheet the historical data for the base index (usually the S&P 500) and data for one or more funds/stocks/indexes that you want to analyze (sidebar Figure 1). Set up a template for the calculations, then copy the data into the template to
evaluate the price regression line (PRL) and how well the prices follow the index. I have used limited data here to make it easy for you to verify that you have set up the spreadsheet correctly. These steps apply to Internet
Explorer 6 and Excel 2000. Internet Explorer 6 puts the data directly into a temporary spreadsheet when downloaded, while version 5+ saves it to a comma-separated file, *.csv, which means you have to import it as a separate step.