In our third installment of the Modern Portfolio Theory series, we will be constructing the Capital Allocation Line, or CAL. In a way, the CAL is an extension of the efficient frontier. CAL takes into account a risk-free asset. In theory there is no risk free asset, so we use what is known as a virtually risk-free or “near” risk free asset.

What kind of asset is risk free? The most commonly used risk-free rate is the 3 month T-Bill rate from the United States Treasury. It is widely believed that this return is guaranteed. It is very unlikely that the US Treasury will default and become insolvent within the next 3-months.

With this “near” risk-free asset, the Efficient Frontier takes on a new shape. The new shape consists of a straight line connecting the risk-free rate (which is on the y-axis since SD=0) to what is known as the tangency portfolio, and then continues with the regular curved shape of the Efficient Frontier.

The tangency portfolio is where the CAL and Efficient frontier cross. This portfolio is considered the most efficient portfolio out of all possible portfolios (given everyone shares the same time horizon, risk preference, and return needs). This is the portfolio that maximizes Theta, or what might be more familiarly known as the Sharpe Ratio.

The Sharpe Ratio is a formula that tracks performance of a security or portfolio, taking into consideration the return of the portfolio, the return of the risk-free asset, and the SD of the portfolio. It is basically finding out what extra return, given the amount of risk, was generated by the portfolio. The new Efficient Frontier with the CAL only has one “most efficient” portfolio where the Theta is maximized. If you were to pick a portfolio below the tangency portfolio, your Sharpe Ratio would drop. And, if you picked a portfolio above the tangency portfolio, your Sharpe Ratio would drop.

In today’s tutorial we are going to show how to construct the CAL and how to create the tangency portfolio.

We will be continuing on from our previous two posts and using the data we found and generated there. If you haven’t read these posts yet I highly recommend going back and starting from the beginning. The previous posts are:

*1. Modern Portfolio Theory: Developing a Global Minimum Variance (GMV) Portfolio in Excel.
2. Modern Portfolio Theory: Developing the Efficient Frontier*

So let’s begin!

** **

**Creating the Capital Allocation Line (CAL)**

The first thing we need to do is find the risk-free rate. As we discussed above, the most common used risk-free rate is the 3-month T-Bill rate from the US Treasury. I think this will be a good rate to use because it is commonly used and we are using 6 American companies as our population of securities, so it makes sense to use an American Treasury rate. You are not limited to the 3-month US rate. You can use a 3-month rate from any financially stable country, such as Canada or Germany.

You can find the 3-month T-Bill rate at the US Treasury website’s Yield Curve page. From this data, we see that the US 3-month T-Bill rate is at 0.11% on 07/27/12. However, this is an annualized amount. Our data in our Excel file is monthly, so we need to transform the 0.11% into a monthly rate. Since the risk-free rate is just an estimate of a theoretical risk-free rate, we can use a simple method to compute the monthly risk-free rate. Take the 0.11% and divide it by 12 (RISK FREE RATE = .0011 / 12). This will give you a monthly rate of .000092or 0.0092%. If that sounds pretty low, it is. The reason it is so low right now is that the US government continues to keep interest rates low to stimulate the economy towards recovery.

Now that we have the rate, we can enter it into our Excel file. I put mine just below the “Portfolio Expected Return” data. We are also going to enter its SD (which is zero) for use in calculations and charts. Your file should look something like this:

The next step is to compute Theta (the Sharpe Ratio) of our portfolio. The formula for Theta is

THETA = (PORTFOLIO EXPECTED RETURN – RISK FREE RATE) / SD of PORTFOLIO.

Enter this into your Excel file. It should look something like this:

Next, we need to make a table to hold our values for the tangency portfolio and the Capital Allocation Line (CAL). I placed my tables below the Efficient Frontier table. They look like this:

Now we can use the Theta we calculated above in our SOLVER function to get the tangency portfolio. Our goal is to maximize Theta, so this is what we will be using SOLVER to find. Your SOLVER data should look like this:

- Set Target Cell = $D$41 (Theta)
- Equal to = Max
- By Changing Cells = $A$27:$F$27 (Portfolio Weights)
- Subject to the Constraints:
- $A$27:$F$27 <= 1
- $A$27:$F$27 >= 0
- $H$27 = 1

You can now fill in both tables we created. The tables should look like this:

We can now use the data in these tables to develop the CAL line and plot the tangency portfolio. We will be using a “Scatter with Smooth Lines and Markers” chart. You will have 5 different Data series in this chart (you can select the data series by right clicking on the chart). They will include:

**The Efficient Frontier:**Use your “Efficient Frontier” table. The Y-axis will be the expected returns and the X-axis will be the SDs of the portfolio.**GMV Portfolio:**Use your “Global Minimum Variance Portfolio” table. The Y-axis will be the expected return and the X-axis will be the SD of the portfolio. There will only be one data point.**Risk Free Rate:**Use your “Capital Allocation Line” table. The Y-axis will be the risk free rate expected return and the X-axis will be the risk free rate SD (zero). Only one data point.**Tangency Portfolio:**Use your “Capital Allocation Line” table. The Y-axis will be the tangency portfolio expected return and the X-axis will be the tangency portfolio SD. Only one data point.**CAL:**Use your “Capital Allocation Line” table. The Y-axis will be the expected return and the X-axis will be the SD. Use both points to draw a line.

You should now have a complete chart that looks like this:

As you can see, the new “Efficient Frontier” is the green straight line (this is the CAL) to the orange square (the tangency portfolio) and continues along the blue curved line (the Efficient Frontier).

How this can be interpreted is:

- Plots along the green line (CAL) have a specific mix of the tangency portfolio and the risk free asset
- The orange square Is the tangency portfolio (the portfolio with the highest Theta)
- Plots along the curved blue line (the efficient frontier) are the most efficient returns with given risk (not using the risk free asset).

So there you have it. You have completed the last section of our Modern Portfolio Theory series. Now you can fully compare your portfolio to efficient portfolios, find out if you are outperforming the tangency portfolio (if your Theta is higher), or simply create an efficient portfolio to start investing with a plan!

**Thanks for reading and good luck out there!**

Let me know what you think. If you have any questions or comments, respond below!

*Other posts in the Modern Portfolio Theory series:
1. Modern Portfolio Theory: Developing a Global Minimum Variance (GMV) Portfolio in Excel.
2. Modern Portfolio Theory: Developing the Efficient Frontier*

Very helpful…thanks a lot..!!

Thanks! Glad it was helpful. Let me know if there are any other finance topics you’d like to know more about.

These three article were great. Thanks a lot.

Glad they were useful!