Build Your Own Portfolio Tracker in Excel: Holdings, Returns, Dividends & Rebalancing
20 hands-on labs that build a personal portfolio tracker step by step: holdings and weights, cost basis and gains, a transaction log with SUMIFS, average cost, dividend income, total return, CAGR, a monthly savings plan with FV, asset allocation, rebalancing trades, fee drag and a dashboard with charts – then price updates with XLOOKUP, realised gains, a dividend calendar, rebalancing with new money, time-weighted and money-weighted returns, an all-in cost tracker and an annual review with a waterfall chart.
- One-time payment
- Instant access right after payment
- Secure checkout with Stripe
About this course
This beginner course builds a personal portfolio tracker in Excel, one lab at a time. In 20 hands-on labs you calculate holding values and weights, cost basis and unrealised gains, positions from a transaction log with SUMIFS, the weighted average cost, dividend income and yield on cost, price and total return, CAGR with RRI, a savings plan with FV, the asset allocation against a target, rebalancing trades and fee drag, and a first dashboard. The second part updates prices from a list with XLOOKUP and flags stale prices, calculates realised gains on sales, builds a dividend calendar with SUMIFS and EOMONTH, splits new money across underweight classes, chains monthly returns into a time-weighted return, measures your own return with XIRR, tracks all-in costs and closes the year with a waterfall chart.
Lessons
- Holdings Table: Shares × Price, Total Value and WeightsFree
- Cost Basis and Unrealised Gain or Loss Locked
- Transaction Log: From Buys and Sells to Positions Locked
- Average Cost per Share from Several Purchases Locked
- Dividend Income, Current Yield and Yield on Cost Locked
- Total Return vs Price Return Locked
- Annualised Return (CAGR) with RRI Locked
- Monthly Contributions and Future Value with FV Locked
- Asset Allocation by Class vs Target Locked
- Rebalancing: Trades Back to Target Weights Locked
- Fee Drag: What 1% a Year Costs over 20 Years Locked
- Mid-course Project: Portfolio Dashboard – Allocation Pie and Drift Alerts Locked
- Updating Prices with XLOOKUP: A Price List, Missing Tickers & Stale Prices Locked
- Realised Gains: Net Proceeds, Average Cost of Shares Sold & the Year’s Total Locked
- Dividend Calendar: Income per Month with SUMIFS, the Year’s Total & Reinvested Shares Locked
- Rebalancing with New Money: Shortfalls, Splitting a Deposit & Weights Without Selling Locked
- Monthly Returns Without the Deposits: Chained Time-Weighted Return vs Simple Growth Locked
- Your Own Return per Year with XIRR: Dated Deposits, a Withdrawal & Today’s Value Locked
- All-in Costs: Fund Costs per Holding, Trading Fees with SUMIF & Costs as a Share of the Portfolio Locked
- Final Project: The Annual Portfolio Review – a Waterfall from 1 January to 31 December Locked
Learner reviews
Be the first to review this course.