How to Build an Investment Portfolio Tracker in Google Sheets

An investment portfolio tracker is a document that answers one question clearly: where is all my money right now?

Most investors can't answer that question accurately. EPF statements arrive annually and are hard to read. Mutual fund holdings are scattered across direct plans, regular plans, and multiple folios. Stocks sit in a demat account on one platform. PPF is a physical passbook. NPS shows up in quarterly statements. Gold is somewhere in a locker.

A portfolio tracker pulls all of this into one view — updated monthly, structured consistently, so you can see allocation, progress, and gaps without hunting across six platforms.

What a Good Tracker Covers

The tracker needs to capture every investable asset:

Market-linked investments:

• Equity mutual funds (direct and regular, across AMCs)
• Debt mutual funds
• Listed equity shares (demat)
• REITs and InvITs (if any)

Fixed/guaranteed instruments:

• EPF (Employee Provident Fund)
• PPF (Public Provident Fund)
• NPS (National Pension System) — Tier I and Tier II
• Fixed Deposits (bank and NBFC)
• NSC, SCSS, other small savings
• Sovereign Gold Bonds

Physical assets (if tracking net worth):

• Physical gold (estimated value)
• Real estate (property value, approximately)

For most people building a portfolio tracker (as opposed to a full net worth tracker), the focus is on the first two categories. Real estate is harder to value and changes infrequently.

Structuring the Google Sheet

The sheet should have multiple tabs, each focused on one category. A clean structure:

Tab 1: Summary Dashboard Tab 2: Mutual Funds Tab 3: Stocks Tab 4: EPF + PPF + NPS Tab 5: FDs and Debt Instruments Tab 6: Allocation Chart data

Tab 2: Mutual Fund Detail

Columns for each fund row:

Column	What to Enter
Fund Name	Full scheme name
AMC	Mirae, HDFC, Axis, etc.
Category	Large Cap, Flexi Cap, ELSS, Debt, etc.
Folio Number	From fund statement
Platform	Kuvera, Groww, MF Central, direct
Purchase Mode	SIP or Lump Sum
Monthly SIP Amount	Blank if not SIP
Total Units Held	From latest CAS
Current NAV	Manual update monthly
Current Value	=Units × NAV (formula)
Cost of Investment	Total amount invested
Gain/Loss	=Current Value − Cost
XIRR%	Optional — use XIRR formula with all cashflows
Lock-in Until	For ELSS — earliest lock-in date
Goal	Which financial goal this is for

For current NAV, you can type the latest NAV manually once a month from AMFI or the fund's page. For automation, the GOOGLEFINANCE function works for some Indian mutual funds using their ticker on BSE.

Tab 3: Stock Holdings

Column	What to Enter
Ticker / Symbol	NSE symbol (e.g., RELIANCE, TCS)
Company Name
Quantity	Number of shares held
Average Buy Price	Total cost ÷ shares
Total Cost
Current Price	=GOOGLEFINANCE("NSE:RELIANCE", "price")
Current Value	=Quantity × Current Price
Gain/Loss	=Current Value − Total Cost
Gain %
Sector	IT, FMCG, Banking, etc.

The GOOGLEFINANCE function updates automatically. Syntax for Indian stocks: =GOOGLEFINANCE("NSE:RELIANCE", "price") or =GOOGLEFINANCE("BSE:500325", "price") using the BSE code.

Tab 4: EPF / PPF / NPS

These change infrequently — update annually or when you receive a statement.

EPF:

• UAN number
• Balance as of last statement date
• Last statement date
• Monthly contribution (employer + employee)
• Approximate annual return rate (EPF interest rate declared each year)

PPF:

• Account number
• Bank/Post Office
• Balance as of last passbook update
• Annual contribution
• Account opening date (maturity is 15 years from opening)

NPS:

• PRAN number
• Tier I balance (pension wealth as on last statement)
• Asset allocation (equity/corporate bond/G-Sec %)
• Tier II balance if any

Tab 1: Summary Dashboard

This is the most important tab — it summarises everything from the other tabs with SUMIF or simple cell references.

Key summary rows:

• Total equity allocation (MF equity + stocks + equity portion of NPS)
• Total debt allocation (debt MF + EPF + PPF + FDs + debt portion of NPS)
• Total gold
• Total portfolio value
• Total cost of investment
• Overall gain/loss amount and percentage
• Portfolio XIRR (if tracking cashflows)

From this, you can see allocation percentages instantly. If your target is 70% equity / 30% debt and the current is 80% / 20%, that's a rebalancing flag.

Goal allocation section: Map investments to goals. Which funds are for retirement? Which for house down payment? Which for child's education? This turns a portfolio view into a goals view.

Updating the Tracker: A Monthly Routine

Monthly update takes about 20–30 minutes once the structure is in place:

1. Download the latest NAV for each mutual fund from AMFI (amfiindia.com) or the fund's website. Update in the NAV column.
2. Check stock prices (GOOGLEFINANCE updates automatically).
3. Log any new purchases or SIP confirmations.
4. Note any redemptions and update units/quantity.
5. Update EPF/PPF balance if a statement arrived.
6. Check the Summary tab: current allocation vs target, total value vs last month.

Using the XIRR Function for Real Returns

The simple gain/loss percentage doesn't account for when money was invested. XIRR (Extended Internal Rate of Return) is the correct measure for irregular cashflows like SIPs.

To calculate XIRR in Google Sheets:

• Create two columns: dates and amounts (negative for investments, positive for current value or withdrawals)
• Formula: =XIRR(amounts_column, dates_column)

For example, if you've invested ₹5,000/month for 36 months and the current value is ₹2,10,000, XIRR will calculate the annualised return rate accounting for the timing of each investment.

Most people are surprised to see how their actual XIRR compares to the "returns" quoted by fund platforms, which use different calculation methodologies.

What to Do With the Data

The tracker isn't just a historical record — it's a decision tool:

Asset allocation review: Is equity-to-debt ratio still in line with your target? If equities have outperformed and you're now 75% equity when you wanted 65%, consider rebalancing.

Fund performance check: Is any fund significantly underperforming its benchmark over 3–5 years? Not for short-term market moves, but for systematic underperformance.

Goal progress: Is each goal-mapped investment accumulating at the rate needed to reach the target by the goal date?

Concentration risk: Are any single stock positions disproportionately large? Rule of thumb: no single stock should exceed 5–10% of equity portfolio.

Duplicate funds: Many investors accumulate multiple funds in the same category through different platforms and SIPs. The tracker reveals overlap — you might have four large-cap funds all holding similar portfolios.

The tracker is a means to an end. The end is making better, more intentional investment decisions — not getting more comfortable with the size of the spreadsheet.

Getting CAS Data From CAMS and KFintech

The Consolidated Account Statement is the most accurate source for mutual fund data because it comes directly from the registrar's records — the authoritative record of unit holdings. Here is how to pull it:

CAMS (camsonline.com): Go to camsonline.com → Investor Services → Mailback Services → Consolidated Account Statement → Detailed Statement. Enter your registered email and PAN. Choose period. The CAS arrives in your email within minutes. CAMS covers: Nippon India, Mirae Asset, HDFC MF, ICICI Pru, SBI MF, DSP, Franklin, Sundaram, PGIM, IDFC, Kotak (some), and others.

KFintech (kfintech.com): Go to kfintech.com → MFI (Mutual Fund Investors) → Account Statement. Enter PAN and email. KFintech covers: Axis, UTI, Kotak (main registrar), Quant, PPFAS (Parag Parikh), Motilal Oswal, Edelweiss, Invesco, Tata MF, and others.

MFCentral (mfcentral.com): The joint CAMS+KFintech portal. A single CAS from here covers all AMCs across both registrars. Most comprehensive option.

The CAS shows for each fund: folio number, scheme name, units held, NAV as of the CAS date, and current value. These are the numbers that go directly into your tracker's mutual fund tab.

Using GOOGLEFINANCE for Live Stock Prices

The GOOGLEFINANCE function in Google Sheets pulls real-time or historical stock prices for listed companies. For Indian stocks:

NSE stocks: =GOOGLEFINANCE("NSE:INFY", "price") — pulls the current price of Infosys from NSE.

BSE stocks: =GOOGLEFINANCE("BOM:500209", "price") — using the BSE scrip code (500209 is for Infosys).

ETFs on NSE: =GOOGLEFINANCE("NSE:NIFTYBEES", "price") — for Nippon India Nifty BeES ETF.

These formulas update automatically when the sheet is opened during market hours. For your portfolio tracker, the Current Price column for direct equity simply uses this formula — no manual updates needed for stocks. Note: the formula uses the exchange-listed price, which may lag by 15 minutes.

For mutual funds, GOOGLEFINANCE does not support Indian fund NAVs directly. Use IMPORTXML or IMPORTDATA to pull NAVs from AMFI (amfiindia.com) if you want automated NAV updates, or update manually once a month.

Building an Allocation Pie Chart

The allocation chart is the most decision-useful visual in the tracker. Here is how to build it:

1. In Tab 1 (Summary Dashboard), create a small table with two columns: Asset Class and Total Value.
2. Use SUMIF formulas referencing the Category column in your mutual fund and stocks tabs to aggregate by asset class (Equity MF, Debt MF, Direct Equity, EPF+PPF, Gold, etc.).
3. Select both columns → Insert → Chart → Pie chart.
4. Label each slice with the percentage.

The pie chart immediately shows your equity-to-debt ratio at a glance. If your target is 70:30 equity:debt and the chart shows 80:20, that is your rebalancing signal for the next annual review.

Add a second small table showing target allocation vs actual allocation, with a "Gap" column (Actual% − Target%). Cells where the gap is positive (overweight) can be conditionally formatted red; underweight cells formatted yellow. This turns the allocation review into a 2-minute exercise rather than a calculation exercise.

Tracking XIRR Per Goal, Not Just Per Fund

Most investors track returns at the fund level — "this fund has given 14% XIRR." But the more useful metric for goal-based investors is XIRR at the goal level: what is the actual return on all the money deployed toward the home down payment goal, or the child's education goal?

To calculate goal-level XIRR in Google Sheets, group all SIP transactions for that goal into a single cashflow column:

Date	Amount
2022-04-10	-10,000
2022-05-10	-10,000
...	...
2026-06-01	6,20,000 (current value — positive)

=XIRR(amounts_column, dates_column)

The output is the annualised return on all capital deployed toward that goal, accounting for the timing of each instalment. This is the number that actually tells you whether the goal is on track — not the point-in-time returns shown on any platform dashboard.

If the XIRR for a 5-year-old goal is 11% and you originally assumed 9%, you're ahead — consider either moving the goal timeline forward or reducing the monthly contribution. If the XIRR is 6% against a 9% assumption, you may need to increase contributions or extend the timeline to stay on target.

Identifying Duplicate and Overlapping Funds in Your Tracker

One of the most valuable exercises the tracker enables is identifying fund duplication — a silent problem that increases paperwork without improving diversification.

After populating your mutual fund tab, add a column for "Fund Category" (Large Cap, Mid Cap, Flexi Cap, ELSS, Short Duration Debt, etc.). Then sort by this column. Any category with more than one fund is a duplication candidate.

Common duplication patterns:

• Multiple flexi-cap funds from different AMCs (HDFC Flexi Cap, Axis Flexi Cap, Parag Parikh Flexi Cap) holding largely similar large-cap and mid-cap portfolios
• ELSS fund and a non-ELSS large-cap fund in the same portfolio — both serve equity allocation, and the ELSS lock-in reduces flexibility without meaningful diversification benefit over a good flexi-cap
• A Nifty 50 index fund and a Nifty Next 50 index fund plus a large-cap active fund — three products covering overlapping market segments

The tracker makes this visible. The decision of whether to consolidate depends on exit loads, capital gains tax from redemption, and whether the lock-in on ELSS units has expired. Evaluate the cost before consolidating — but having the map of overlaps is the starting point.

What Good Portfolio Maintenance Looks Like Year by Year

A well-maintained tracker tells a story over years. Here is what healthy portfolio evolution looks like in practice:

Year 1–3: Mostly contributions, minimal compounding. XIRR may appear low because each new SIP instalment is "new money" pulling down the blended return. Focus on consistency — did every SIP run? Are the numbers growing steadily?

Year 4–7: Compounding starts to be visible. A portfolio that was Rs.5 lakh 4 years ago is now Rs.12–15 lakh from a combination of contributions and returns. The gap between cost and current value begins to widen meaningfully.

Year 8+: Returns begin to contribute more than new contributions in absolute rupee terms. A Rs.40 lakh portfolio generating 12% returns adds Rs.4.8 lakh per year from returns alone — more than many people's annual SIP contribution. This is the compounding inflection point. The tracker makes this visible and tangible in a way that motivates continued discipline.

Seeing this progression year by year in your own tracker — not a textbook example but your actual numbers — is one of the most effective long-term motivators available.

---

This article is for educational purposes only. It describes personal finance tools and frameworks, not personalised investment advice. Consult a SEBI-registered investment advisor for guidance specific to your situation and goals.