Stock Portfolio Tracker

What's included

• Position tracking with accurate cost basis calculation
• Dividend income tracking with payment history
• Annual dividend income projections
• Portfolio allocation analysis by sector and stock
• Performance tracking (total return, price return, dividend return)
• CAGR (compound annual growth rate) calculation
• Dividend yield on cost vs. current yield
• Buy/sell transaction log with tax lot tracking

Why Track Your Stock Portfolio in Excel?

Your brokerage shows you positions, but rarely gives you the complete picture. A dedicated stock portfolio tracker Excel spreadsheet lets you see total return (including dividends), yield on cost (what your original investment now yields), CAGR across multiple time periods, and portfolio allocation clearly. You can track performance across multiple brokerages in one view and build the exact reports you need. This is especially important for dividend investors building passive income.

Cost Basis Tracking for Tax Efficiency

Accurate cost basis tracking is essential for tax reporting and tax-loss harvesting. When you sell shares, you need to know exactly what you paid (including reinvested dividends) to calculate capital gains. This stock portfolio tracker maintains a complete transaction history so you can use specific identification, FIFO, or average cost methods when selling. Come tax time, you'll have everything you need.

Dividend Income Tracking for Passive Income Goals

Dividend investors often set income goals—$500/month, $1,000/month, eventually enough to cover expenses. This stock portfolio tracker Excel template calculates your current annual dividend income, projects forward based on holdings, and tracks yield on cost (your actual return on invested capital, which grows as companies raise dividends). Watch your passive income grow over time with detailed dividend history tracking.

Portfolio Allocation and Risk Management

Diversification is your main protection against individual stock risk. This portfolio tracker shows your allocation by sector, by stock, and by asset type. Identify when you're overweighted in a single position or sector and rebalance accordingly. Many investors are surprised to find they have 40%+ in technology when they thought they were diversified—visibility enables better decisions.

Key formulas

Cost Basis Calculation:
Total Cost Basis = Purchase Price × Shares + Commission
Average Cost Per Share = Total Cost Basis / Total Shares

Dividend Yield Formulas:
Current Yield = Annual Dividend / Current Price × 100
Yield on Cost = Annual Dividend / Your Cost Basis × 100

Performance Metrics:
Total Return = (Current Value + Dividends Received - Cost Basis) / Cost Basis × 100
CAGR = ((Ending Value / Beginning Value)^(1/years)) - 1 × 100

Setting Up Your Stock Portfolio Tracker

1. Enter all current positions: ticker, shares owned, purchase date, purchase price
2. Add transaction history for each buy/sell including dividends reinvested
3. Input current prices (or link to live data if you know Excel)
4. Review dashboard: Current value ($125,000), Cost basis ($95,000), Total return (31.6%)
5. Check dividend income: Annual dividends projected ($3,750), Portfolio yield (3.0%), Yield on cost (3.95%)
6. Analyze allocation: Technology 35%, Healthcare 20%, Financials 18%, Consumer 15%, Industrial 12%
7. Identify rebalancing needs: Tech overweighted, consider adding to other sectors

Complete portfolio visibility with $3,750 annual dividend income. Yield on cost of 3.95% means original investment now earns more than current 3.0% yield.

Frequently Asked Questions

How do I calculate my stock portfolio's cost basis?

Cost basis is the total amount you paid for shares, including commissions and reinvested dividends. If you've made multiple purchases, track each lot separately or use average cost: Total Cost / Total Shares = Average Cost Per Share. This portfolio tracker maintains complete transaction history for accurate cost basis.

What's a good dividend yield for income investing?

Dividend yields of 2-4% are considered healthy and sustainable for blue-chip stocks. Yields of 4-6% can be found in REITs and utilities. Be cautious of yields above 6-7%—they may indicate a company in distress, an unsustainable payout, or an impending dividend cut. Focus on dividend growth rate as much as current yield.

How do I track dividend income in Excel?

This portfolio tracker includes a dividend log where you enter each payment: date, ticker, amount per share, total received. It automatically calculates your annual dividend income, yield on cost, and projects future income based on current holdings and dividend rates. You can also track dividend growth over time.

What is yield on cost and why does it matter?

Yield on cost is your annual dividend divided by what you originally paid for shares—not current price. If you bought a stock at $50 yielding 3% ($1.50/share) and the dividend grows to $2.50/share, your yield on cost is 5% even if current yield is only 2.5%. This metric shows the true return on your invested capital.