Hey, I have been investing in the stock market for the last three years and, like many of you, I’ve encountered the challenge of calculating accurate returns from my portfolio. With irregular investments across two different Demat accounts, calculating my returns manually became a tedious task. To streamline this process, I created a Google Sheets XIRR Calculator for my stock portfolio, which helps me track my returns effectively and perform multiple analyses like CAGR, stock performance, and much more.
In this post, I’ll walk you through how I set up the Google Sheet and how you can use it to analyze your own portfolio.
Google sheet Link : XIRR Calculator , Make a copy of this sheet for personal use.
( I referred to a helpful YouTube video uploaded by Vaibhav Jain while creating this Google Sheet, which explains how to calculate XIRR for stock portfolios and mutual funds. You can follow that video to set up a similar calculator for mutual funds as well.)
Why I Needed This XIRR Calculator
As I mentioned, my investments have been irregular. Given the different dates and amounts of my stock purchases, a simple CAGR calculation wouldn’t be accurate for assessing my performance. XIRR (Extended Internal Rate of Return) solves this problem by taking into account the exact timing of each cash flow (investment or sale). However, most Demat platforms don’t provide a detailed XIRR report. That’s why I built this Google Sheet to automate the process.
Key Features of My XIRR Calculator:
- XIRR Calculation: Automatically calculates the XIRR of my portfolio based on trades.
- CAGR and Profit & Loss Analysis: Provides additional insights into the performance of each stock.
- GoogleFinance Function Integration: Automates some key data like current stock prices and overall portfolio value.
- Manual Trade Data Entry: You need to upload your trade data manually from your Demat account broker statement into the sheet.
Step-by-Step Guide to Using the Sheet
1. Export and Upload Your Trade Data
To get started, export your trade data from your broker’s statement into an Excel file. Typically, your broker will provide this statement in a CSV or Excel format.
Next, copy and paste the necessary columns (like stock name, trade date, buy/sell, and trade amount) into the Trades Table in the Google Sheet. Ensure that the date format is correct; this is critical for the XIRR calculation to work properly.
2. Sort Your Data
After pasting the trade data, make sure to sort the table A-Z on two important columns:
- First, sort by stock name (Column 1).
- Then, sort by the date column A-Z, so your trades are listed in chronological order.
This sorting is essential for accurate calculations and a clean display of your trades.
3. Automating Stock Data with GoogleFinance
I added a feature that calculates the profit and loss (P&L) for all unique stocks in my portfolio. The stock names are automatically derived from the Trades Table, but here’s where things get tricky: the GOOGLEFINANCE function doesn’t recognize stock names unless you input the ticker symbol.
To resolve this, I manually enter the ticker symbols (e.g., NSE:RELIANCE for Reliance Industries) into another table. Once I input the ticker, the GOOGLEFINANCE function retrieves the current stock price and other relevant data like market value, which makes the overall calculation dynamic and insightful.
While manually entering the ticker symbols might seem like extra work, it’s exciting to see your portfolio’s real-time value and XIRR as the stock prices update automatically. I also added conditional formatting for fun—so you’ll see your stock’s performance in color-coded form!
4. How to Maintain and Update the Sheet
Once you’ve set up the Google Sheet, it’s pretty easy to maintain. I update my sheet once or twice a week by copying and pasting new trades from my Demat account’s broker statement. Since the formulas and functions are already set up, all I need to do is paste the new data and the sheet takes care of the rest, giving me updated XIRR, CAGR, and P&L for each stock.
Challenges I Encountered
One of the minor challenges I faced was ensuring that the stock names were correctly mapped to the ticker symbols. The GOOGLEFINANCE function only works with valid ticker symbols, so manually entering them became necessary. However, once done, the sheet runs smoothly, and the effort is well worth the insights I get.
Conclusion
I encourage you to try creating a similar Google Sheet for your portfolio. It’s a powerful way to calculate your returns, analyze stock performance, and get detailed insights into your investments. If anyone’s interested in further details or needs help setting up their own version, feel free to reach out! Happy investing!
I am not a SEBI-registered advisor, and the information shared here is purely for educational purposes based on my personal experience. The trade data included in the sheet is a sample for illustrative purposes and should not be taken as actual trading recommendations.
Looking forward to your feedback and thoughts!
. I was actually looking for for an XIRR and portfolio tracking tool. Kudos for the great work .
