How to set up a balanced stock portfolio
To set up a balanced portfolio, first choose a split between stocks, bonds, and cash that fits your risk tolerance and time horizon, then spread the stock portion across enough holdings and sectors that no single position dominates, and rebalance back to your targets on a schedule. Rules of thumb such as the 120 rule (120 minus your age in stocks), the 70/30 stock/bond model, and a 5% cap per position give you a starting point. "Optimal" allocation goes one step further: it uses each asset's expected return, volatility, and correlations to find the mix with the best return for the risk you accept.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
In Excel, the MarketXLS add-in supplies those inputs as formulas, such as =StockReturnOneYear("AAPL"), =StockVolatilityOneYear("AAPL"), and =StockReturnCorelationLastOneYear("AAPL","TLT"), and Excel Solver finds the weights. This article is educational and not investment advice.
Why asset allocation matters
Asset allocation sets how much risk your portfolio carries and how it behaves in a downturn, which is why it is usually decided before picking individual stocks.
Diversification: Spreading money across assets that do not move together reduces the impact of any one poor performer on the whole portfolio.
Risk management: Choosing the stock/bond/cash mix up front keeps the portfolio's risk in line with what you can tolerate.
Risk-adjusted performance: An allocation that accounts for correlations can deliver the same expected return with lower volatility than a naive mix. It cannot guarantee higher returns.
Common portfolio allocation rules
The four rules below are simple starting points. None of them accounts for your full financial situation.
The 90/10 rule
A 90/10 rule puts 90% of the portfolio in investments and keeps 10% in cash or cash equivalents for liquidity and new opportunities. A widely cited version comes from Warren Buffett's 2013 shareholder letter, which suggested 90% in a low-cost S&P 500 index fund and 10% in short-term government bonds. Factors to weigh when adapting it include time horizon, risk tolerance, fees, goals, and volatility. You can also learn how to create your own Excel stock tracker with the MarketXLS Excel stock-tracker guide.
The 120 rule
The 120 rule sets your stock allocation at 120 minus your age, with the rest in lower-risk assets such as bonds, bond funds, and cash. A 40-year-old would hold 80% in stocks (including growth stocks, index funds, and ETFs) and 20% in bonds and cash, shifting toward bonds each year. Rebalancing periodically keeps the portfolio at its target mix; each rebalance can create trading costs and, in taxable accounts, taxes. To learn more about rebalancing, click here.
The 5% rule
The 5% rule caps any single investment at 5% of the portfolio. With at least 20 positions, no one holding can do outsized damage. Review the portfolio periodically, because a winning position can grow past the cap. For more, see our portfolio management tips before retirement.
The 70/30 model
The 70/30 model invests 70% in stocks and 30% in bonds. It suits investors with a moderate to higher risk tolerance who can ride out stock market declines. Within the stock portion, diversify further with index funds or mutual funds. Cash equivalents such as money market funds sit outside the 70/30 split because they generally return less (see 10 Portfolio Tips before Retirement).
Finding an optimal allocation in Excel with MarketXLS
MarketXLS does not choose an allocation for you. It supplies the data an optimization needs as Excel formulas, and Excel's Solver add-in does the optimization. The steps:
- Enter your portfolio. List each stock, the number of shares, purchase price, and acquisition date in a sheet with the MarketXLS add-in installed.
=QM_Last(A2)returns the current price for position values (15-minute delayed on the Standard plan, real-time on Advanced and Business). - Pull return, risk, and correlation data. Use
=StockReturnOneYear(A2)for trailing 1-year total return,=StockVolatilityOneYear(A2)for 1-year volatility, and=StockReturnCorelationLastOneYear(A2,B1)for each pair's 1-year correlation. Trailing returns are a rough proxy for expected return; replace them with your own assumptions if you have them. - Compute portfolio risk and the Sharpe ratio. Build the covariance matrix from the volatilities and correlations, then compute portfolio volatility and the Sharpe ratio for the current weights.
- Run Solver. Maximize the Sharpe ratio (or minimize volatility for a target return) by changing the weight cells, with constraints such as weights at least 0, weights summing to 1, and a per-position cap like the 5% rule. Our Excel Solver max-Sharpe guide walks through this with a downloadable template.
- Rebalance and track. Compare the result with your current weights, rebalance when they differ materially, and track performance in the MarketXLS portfolio manager or your own sheet.
On Excel for Mac or Excel for the web, the MarketXLS Microsoft 365 add-in uses the mxls. prefix, for example =mxls.StockReturnOneYear("AAPL").
Summary
A balanced portfolio starts with a stock/bond/cash split that matches your risk tolerance and time horizon, caps single positions, and gets rebalanced on a schedule. To go from a rule of thumb to an optimized allocation, use MarketXLS formulas for returns, volatility, and correlations and let Excel Solver find the weights. Related MarketXLS templates include a portfolio comparison template and a Cathie Wood vs user portfolio strategy template; browse them in the MarketXLS templates library.
Find any MarketXLS function in the formula reference. The spreadsheets pull current data if you have MarketXLS installed; see pricing for plans.
Relevant blogs that you can read to learn more about the topic
Build Your Own Stock Portfolio Tracker in Excel with MarketXLS
Unlocking the Benefits of Portfolio Rebalancing
10 Portfolio Management Tips Before Retirement
Portfolio Optimization in Excel with Solver