Arbitrage Pricing Theory Model with Excel

Last Update: December 15, 2020

Asset pricing models consist of estimating asset expected return through its expected risk premium linear relationship with factors portfolios expected risk premiums and macroeconomic factors.

This topic is part of Investment Portfolio Analysis with Excel course. Feel free to take a look at Course Curriculum.

This tutorial has an educational and informational purpose and doesn’t constitute any type of trading or investment advice. All content, including spreadsheet calculations and data, is presented for personal educational use exclusively and with no guarantee of exactness of completeness. Past performance doesn’t guarantee future results. Please read full Disclaimer.

An example of asset pricing models is arbitrage pricing theory APT [1] which consists of estimating asset expected return through its expected risk premium linear relationship with market portfolio expected risk premium and macroeconomic factors.

1. Formula notation.

1.1. Arbitrage pricing theory APT asset risk premium formula notation.

  • Note: macroeconomic factor not fixed and only included for educational purposes.

r_{a,t}-r_{f,t}=\alpha_{a}+\beta1(r_{m,t}-r_{f,t})+\beta2(\Delta CPI_{t})+e_{t}

Where r_{a,t}-r_{f,t} = asset realized risk premiums, r_{a,t} = asset realized returns, r_{f,t} = realized risk free returns, \alpha_{a} = asset average realized excess return, \beta1 = asset market beta coefficient, r_{m,t}-r_{f,t} = market realized risk premiums, r_{m,t} = market realized returns, \beta2 = asset macroeconomic factor beta coefficient, \Delta CPI_{t} = realized consumer price index percentage changes macroeconomic factor, e_{t} = linear regression residuals or forecasting errors.

1.2. Arbitrage pricing theory APT model formula notation.

  • Note: APT formula recasting using expected returns (ex-ante) instead of average realized returns (ex-post). For full reference, please read The Arbitrage Theory of Capital Asset Pricing [1].

E(r_{a})=E(r_{f})+\beta1(E(r_{m})-E(r_{f}))+\beta2(E(\Delta CPI))

Where E(r_{a}) = asset expected return, E(r_{f}) = expected risk-free return, \beta1 = asset market beta coefficient, E(r_{m})-E(r_{f}) = market expected risk premium, E(r_{m}) = market expected return, \beta2 = asset macroeconomic factor beta coefficient, E(\Delta CPI) = expected percentage change in consumer price index macroeconomic factor.

2. Excel example.

2.1. APT multiple factors model data.

  • Data: S&P 500® index replicating ETF (ticker symbol: SPY) adjusted close prices and market portfolio [2] monthly arithmetic returns risk premiums, U.S. consumer price index monthly arithmetic change (2007-2016).

2.2. APT multiple factors model multiple linear regression calculation.

  • Multiple linear regression calculation done using Microsoft Excel Data Analysis® Add-In Regression Analysis Tool.
3. References

[1] Stephen Ross. “The Arbitrage Theory of Capital Asset Pricing”. Journal of Economic Theory. 1976.

[2] Eugene F. Fama and Kenneth F. French. “Common Risk Factors in the Returns on Stocks and Bonds,” Journal of Financial Economics. 1993.