Master Investment Tracking and Analysis With Excel

Search Fintrev

Search our guides, reviews and glossary.

Excel can track an investment portfolio from a single position to dozens of holdings. You record each purchase (date, ticker, shares, entry price), add the current price, and let formulas calculate the price difference, percentage return, profit and loss, and standard deviation of returns. To show growth, the sheet also needs a dated log of values and contributions that you can chart and turn into an annualized return. The steps below build that workbook in order, from one row of data to a portfolio summary.

Decide What the Sheet Should Record

Choose your columns before you type any formulas. A simple stock tracking spreadsheet usually starts with:

• Date of the purchase or of each price observation
• Ticker or asset name
• Shares (position size)
• Entry price per share
• Current or closing price for each date you record
• Difference (current price minus entry price)
• Percent return
• Profit and loss in dollars

One common layout gives each holding its own worksheet, with rows for each periodic closing price, so you can calculate a return series and standard deviation for every stock. A separate summary sheet pulls the holdings together. The same columns work for funds and bonds; mutual funds use net asset value in place of a share price.

Two optional columns make the tracker more accurate. One is fees, so commissions are added to your cost basis instead of disappearing. The other is dividends or distributions, so income counts toward your return. Without them, a stock profit tracker shows price movement only.

Calculating the Price Difference

The price difference is the current price minus the entry price. If the current price is in E2 and the entry price is in C2, click the result cell, type =E2-C2 and press Enter. To copy it down the column, drag the fill handle (the small square at the cell’s lower-right corner). Excel adjusts the row references automatically, so row 3 becomes =E3-C3.

When every row should refer to the same cell, such as a single entry price on a per-stock sheet, lock that reference with dollar signs: =E2-$C$2. Without the lock, dragging the formula shifts the entry price reference down the column and produces wrong results.

Calculating Percentage Returns

Percent return is the price difference divided by the entry price:

Percent return = (current price − entry price) ÷ entry price

In Excel, select the result cell and type =(E2-C2)/C2. The parentheses matter. Without them, Excel divides first and subtracts second, which gives a meaningless number. If you already have a difference column in F, =F2/C2 gives the same answer.

The result displays as a decimal (0.15) until you format the column as Percentage through Format Cells. Then drag the formula down as before.

This is a price return. To include income, add dividends received per share to the numerator: =(E2-C2+G2)/C2, where G holds cumulative dividends per share. If you include commissions, use your total cost per share as the entry price.

Calculating Profit and Loss

Profit and loss for a position is the per-share difference multiplied by the number of shares: =F2*D2. Format the column as Currency and copy the formula down.

Hypothetical example: you buy 50 shares at $40. The stock later closes at $46.

• Difference: $46 − $40 = $6 per share
• Percent return: $6 ÷ $40 = 15%
• Profit and loss: $6 × 50 = $300

The $300 is an unrealized gain while you still hold the shares. Adding a “Status” column (open or closed) and a sale price keeps realized gains separate from paper gains. That matters for tax records, because only realized gains are generally taxable events.

Showing Investment Growth Over Time

A snapshot return tells you where a position stands today. It does not tell you how fast it got there or how your account grew while you added money. For a growth view, add a log sheet with three columns: Date, Contribution or withdrawal, and Account value.

Charting growth. Select the Date and Account value columns and insert a line chart. If you also track cumulative contributions in a fourth column, chart both lines. The gap between them is the growth that came from returns rather than deposits.

Annualized growth (CAGR). When no money was added or removed, the compound annual growth rate is:

=(End value / Start value)^(1 / Years) - 1

Hypothetical example: $10,000 growing to $14,000 over three years gives =(14000/10000)^(1/3)-1, about 11.9% a year. This assumes no deposits or withdrawals during the period.

Returns with contributions (XIRR). If you invest on a schedule, CAGR overstates performance because deposits inflate the ending value. Excel’s XIRR function handles irregular cash flows. List each contribution as a negative number with its date, then put today’s account value as a positive number with today’s date, and use =XIRR(values, dates). The result is an annualized, money-weighted return you can compare with the return your plan assumed.

Measuring Risk With Standard Deviation

Standard deviation measures how far individual returns fall from their average. A higher figure means returns have swung more widely; a lower figure means they have clustered more tightly. Because it uses every return in the series, it says more about volatility than a few periodic results.

To calculate it, type the function in a result cell and select only the return values in your percent return column, not the header, for example =STDEV.S(H2:H25). You can also find it under Formulas > Insert Function > Statistical.

Excel offers several versions:

• STDEV.S treats your returns as a sample, which fits a series of historical returns. The older STDEV function gives the same result and remains for compatibility.
• STDEV.P treats the data as the entire population.

Two cautions keep the figure meaningful. First, run it on returns, not prices. A $500 stock and a $20 stock would otherwise look wildly different in risk for reasons unrelated to volatility. Second, keep the interval consistent. If your returns are monthly, the result is a monthly standard deviation. A common approximation for an annual figure multiplies it by SQRT(12), which assumes returns in different months are independent.

A lower standard deviation means lower measured volatility over the period you sampled. It does not capture every risk, such as a company’s financial problems or a sudden drop that never appeared in past data.

Compiling Your Portfolio on One Sheet

Once each holding has its own sheet, a summary sheet shows the whole portfolio at a glance. Link cells rather than copying them: type = in the summary cell, click over to the stock’s sheet, and select the cell you want. The summary then updates whenever the source sheet changes.

Each summary row uses the same formulas as above: entry date and price, shares, current price, difference, percent return, and profit and loss. Add three portfolio-level calculations:

• Position value: =Shares*Current price
• Weight: position value divided by total portfolio value, for example =I2/SUM($I$2:$I$12)
• Weighted return: =SUMPRODUCT(weights, returns), which shows how much each holding contributed

The standard deviation on this sheet is calculated across the percent returns of all holdings instead of a single stock. That shows how spread out the results are across your positions. It is not the same as the volatility of the portfolio over time; for that, run standard deviation on the period returns from your value log.

A summary sheet also makes it easy to add a sector column and group holdings with a PivotTable. That helps you see concentration, for example whether most of your value sits in one industry.

Tips for Keeping the Tracker Accurate

Convert ranges to Excel Tables. Select your data and press Ctrl+T. New rows automatically pick up the formulas from the rows above, and column names appear in formulas, which makes them easier to read.

Use automated price data where available. Microsoft 365 versions of Excel include a Stocks data type that pulls quote fields for a ticker, and the STOCKHISTORY function, which returns historical prices for a date range. Both require a subscription and an internet connection, and quotes may be delayed. Older versions require you to enter closing prices manually.

Keep one record per transaction. Each additional purchase at a new price gets its own row, so your cost basis remains traceable. Averaging everything into one row hides when and at what price you bought.

Protect formula cells. Locking formula columns and protecting the sheet prevents an accidental keystroke from overwriting a calculation.

Consider alternatives. Downloadable templates and commercial portfolio software can save setup time. Before relying on one, check that its formulas match the definitions above, especially for fees, dividends and contributions.

FAQs

The Bottom Line

Investors who want a free, transparent way to follow their holdings can build a working tracker in Excel with a handful of formulas. Recording fees, dividends and each purchase on its own row keeps cost basis and returns accurate. A dated value log adds the growth view that a single snapshot cannot provide, and linked summary sheets show weights and concentration across the whole portfolio.