How to Calculate the Growth Optimal Portfolio Weights in Excel?

This is probably the most important post you will read about investing

I previously wrote about Kelly Criterion and how it can help you to decide how much of your bankroll to bet on a single bet to maximize your winnings over a period of time. This works very well for situations where you are making one bet at a time.

But what do you do if you have a portfolio of bets?

Your portfolio contains multiple assets. Not only that, these assets are not independent of each other. Most of your assets are correlated to each other to some degree or other.

How do you calculate the optimal Kelly bet for each of these assets?

(What follows is a teeny bit mathematical, but if you continue on, I will give you steps to implement this in Excel or Google Sheets or LibreOffice.)

Making Kelly Bets in a Multi-Asset Portfolio

For a portfolio of multiple assets, the Kelly criterion is extended to account for the correlations between assets. The fraction of the bankroll (or your portfolio size) to allocate to each asset is determined by solving a more complex equation involving the expected returns, covariance matrix, and possibly the risk-free rate.

The formula in matrix form is:

\(w = \frac{1}{\gamma}\Sigma^{-1}(\mu - r_{f}) \)

where:

• w is the vector of Kelly fractions (weights) for each asset.

• γ is the risk aversion coefficient, often set to 1 for the pure Kelly criterion. You can keep this to 1. The resulting Kelly bets can be too high in which case you can scale them down to the size of your portfolio.

• Σ^(−1) is the inverse of the covariance matrix of the asset returns. To calculate the inverse of the covariance matrix, you will first need to calculate the covariance matrix. This can be done by finding correlations between the assets and the standard deviation of each individual asset (stock or ETF as the case may be).

• μ is the vector of expected returns for the assets. You will calculate the expected returns based on your analysis of the fair value of the security. This is where the valuation analysis as we do at Astute Investor’s Calculus becomes critical.

• r(f​) is the risk-free rate. I generally use the yield I receive on the uninvested cash in my Fidelity account (which is automatically swept into SPAXX, currently yielding about 4.95% as of Sep 2024).

Before we go into the Excel implementation, there is a question of sourcing data for this. I use Stock Rover to get the correlations data between the stocks that I own in my portfolio. You may find another service to provide this for you. Stock Rover can also be used to get the approximate Standard Deviations for each stock (you can also calculate this yourself if you wish based on historical returns data, but I am happy with the data I get from Stock Rover).

You can always see the current correlations, returns expectations, standard deviations and the Optimal Allocation of the stocks in my portfolio at the Premium Portfolio page.

Okay, now we can dive into the Excel implementation. Please note that if you use Google Sheets or LibreOffice, these instructions will still work.

---

Sponsored

Money Machine Newsletter

Market beating stocks in 5 min. Picked by elite traders. Delivered weekly to your inbox pre-market. Join today for free.

Tired of Missing Out on Hidden Stock Opportunities? Try This Game-Changing Tool for Free and Take Control of Your Portfolio!

Struggling to find undervalued stocks with strong fundamentals? Stock Rover is the ultimate tool for value investors, designed to cut through the clutter and give you deep insights where it counts. With powerful screeners and detailed analytics, you’ll quickly spot investment opportunities that others miss. No more sifting through endless data—Stock Rover helps you identify winning stocks, fast. And here’s the best part: you can try it free for 2 weeks! Imagine how much more confident you’ll feel with a tool built for your investing style. Don’t wait—unlock smarter investing with Stock Rover today!

Stay Organized on the Go: Handcrafted Leather Field Notes/Passport Cover for the Savvy Investor

Looking for a sleek and durable travel companion? This handcrafted leather Field Notes/Passport Cover from Arbor Trading Post is made with premium full-grain, vegetable-tanned leather from Tuscany. Designed to hold your Field Notes, passport, and essentials securely, it's perfect for any adventure. Customize with optional features like a snap closure or pen loop for added convenience. Each cover is hand-stitched and built to last. Made in Ann Arbor, MI.

Keep your essentials secure in style—order your handcrafted cover today!

---

Step-by-Step Method: Calculate Kelly Fractions in Excel

Please note that this method will also work for Google Sheets and LibreOffice Calc.

This guide will walk you through calculating Kelly fractions using expected returns, a correlation matrix, standard deviations, and the risk-free rate. For this example, we assume a portfolio of 5 stocks.

Step 1: Input Your Data

• Expected Returns:

  • Input the expected returns for each asset in a column (e.g., cells B2 to B6).

• Standard Deviations:

  • Input the standard deviations of returns for each asset (e.g., cells C2 to C6).

• Correlation Matrix:

  • Input the correlation matrix between the assets in a grid (e.g., E2 to I6). If you only have correlations, this will be used to calculate the covariance matrix.

• In Column D, subtract the risk-free rate from the expected returns to get the Excess Returns

Step 2: Calculate the Covariance Matrix

The covariance matrix can be calculated by multiplying the standard deviations by the correlations between each asset.

Formula for Covariance (Cov) between two assets (A and B):

\(Cov(A,B)=Corr(A,B)\times\sigma_{A}\times\sigma_B\)

This is calculated by multiplying the correlation between the assets by their standard deviations.

You can set this up in the columns J-N. For example, the cells J2-N6.

Step 3: Invert the Covariance Matrix

You can do this by selecting the range where inverse of the Covariance Matrix will be placed. In this example, it is O2-S6. Once selected, type in the minverse() function and select the covariance matrix range as the argument. In this case it would be =minverse(J2:N6)

Press Ctrl+Shift+Enter to apply it as an array formula.

Step 4: Calculate Kelly Fractions

Multiply the inverse covariance matrix by the excess returns (from Step 1). You can du this by using the matrix multiplication function mmult().

In this example, select the range T2-T6. Then enter =mmult(O2:S6,D2:D6) and press Ctrl+Shift+Enter.

This will give you the Kelly Fractions for each asset in your portfolio.

Step 5: Normalize the Kelly Fractions

You will notice that the Kelly Fractions you calculated above do not sum up to 100. Sometimes they will be more than 100 and sometimes the sum will be less than 100. You will scale these fractions up or down proportionally so they make up 100% of your portfolio.

You can easily do this by adding the calculated Kelly Fraction, let’s say by using the function sum(t2:t6) in the cell t7. Then in the cell u2, calculated the normalized Kelly Fraction for Asset 2 by calculating t2*100/$t$7. Use this formula for each of the assets. This will give you the % of your portfolio you should allocate to each of these assets.

Some Considerations as You Calculate Your Growth Optimal Weights

As you do these calculations, you may run into some questions. Here are some of the most common situations:

1. Negative Kelly Fraction: You might see negative weights for some of your assets. This means you should short this stock. If you are not into shorting stocks, then your best bet is to remove this stock from your portfolio and recalculate the weights for the remainder without this stock.

2. Correlations and Standard Deviations change all the time: To get the best results, you should be very clear about your investment time horizon. I use 1 year’s worth of historical data to calculate the standard deviations and correlations. If you are a short term trader, you may want to use shorter time horizon, for example, 3 months. (Please note that this means you won’t be able to invest in IPOs as these stocks don’t have enough history! This is a benefit!)

3. Stock prices changed and therefore my expected returns are now different. You should consider recalculating your fractions periodically and then rebalance. This will reduce your exposure to stocks that have appreciated and increase your exposure to stocks that have declined. This, by the way, is perfectly in line with the value investing principles and will force you into disciplined buying and selling.

4. So what are the benefits of doing this? This will allow your portfolio to grow at the fastest rate mathematically possible (given the assets you have selected) while reducing your risk of loss to ZERO. It will also balance the volatility in such a way that your overall portfolio volatility will be reduced. Of course, to make all this work properly, you need to have a diverse portfolio with assets of low correlations with each other.

Again, I do this all for you in our Premium Portfolio that you have access to as a paid subscriber.

PS: Please bookmark this post for future reference. If you want me to prepare a sample Google Sheets spreadsheet for you to use, please let me know in the comments and I will link one for you.