Investing money can be daunting for beginners, as it not only requires you to keep track of your investments but also review your investment portfolio based on the performance of different investment schemes.
Lucky for you, our Investment Tracking template offers a simple yet effective tool to help you understand your investments better and make more informed decisions that bring you closer to your financial goals.
Use our template to keep track of financial investments of any kind; from stocks and mutual funds to bonds and bitcoins. But note that each spreadsheet must be used for a specific investment only – for example, if you are investing your money in two different cryptocurrencies, you will need to use two different spreadsheets. You can do this by simply duplicating the sheet in the same Excel file – creating a single location to store & analyze all of your important investment data.
How Our Template Works
Our template is easy to use as it performs calculations automatically when you input certain data.
For each money contribution or withdrawal you make, you need to add the date, and specify the amount. In addition to these basic details, the only other information you need to enter manually is your Portfolio Value. This refers to the total monetary value of the assets held in your portfolio, which you get by adding up the current value of all your investments.
For example, if you bought 20 shares of a company for 80 USD last year, then your portfolio value will refer to the value of those shares today – which may be more or less than what you bought them for. When developing this template, our team was faced with an interesting choice as to whether this Value should be added manually or made into an automated feature connected to live data from major stock exchanges in the US. However, such a feature would have required our template users to have a paid subscription feature.
Once you have added this information (in the Blue Columns) for each investment, we use simple Excel functionalities to calculate everything else for you – including;
How profitable your investment is
Based on the difference between your Cumulative Investment and Portfolio Value at any given time, our spreadsheet is able to calculate your Net Profit or Loss, which refers to how much money you made or lost relative to your initial investment. Tracking this value helps you understand how well your investment is doing, and ensure that your investment portfolio is properly balanced. For instance, if a certain asset class (stocks, bonds, real estate) has significantly outperformed or underperformed, you might need to adjust your allocation to maintain your desired risk-return profile.
We also calculate the Percentage Profit & Loss using the following formula:
Applying this to our template, the current value will be the Portfolio Value while the initial investment will be the Cumulative Investment (listed in Column E). Knowing this percentage is helpful as it provides a clear, standardized way to compare the performance of different investments and understand your financial returns relative to investment size.
How To Adjust Your Investment Value after a Withdrawal
But what happens when you want to account for the effect of a withdrawal you made on the overall health of your investment – can you still use Net % Loss/Profit as an indicator of how well your investment is doing?
Well, yes. We do this by applying a simple formula where we multiply the withdrawn amount by the value of the Cumulative Investment and divide the resulting number by the last Portfolio Value before you make the withdrawal. This gives us the amount we need to subtract from your Cumulative Investment value to account for the effect of the withdrawal.
All you need to do is add a negative value under the column Contribution/Withdrawal when you make a withdrawal, and our automated functionalities will make sure the Net % Loss/Profit value reflects and accounts for this.
Calculate Short-term Returns on Your Investment
This is probably the most useful and informative part of our template, as it allows you to gain a time-sensitive understanding of how well your investments are doing.
First up, we calculate the Periodic Return for you – which measures the rate of return on an investment over a specific time period. This may be daily, weekly, monthly, quarterly, or otherwise – giving you a way to track short-term performance of your investments.
Calculating the Annualized Return on Your Investment
On the other hand, with the Annualized Return, or the compound annual growth rate (CAGR), we measure the average annual performance of an investment over a longer period of time. We do this by using an XXIR function that converts your investment performance for a certain duration (say for the first three months since you invested) and averages it out for a whole year based on the value of periodic returns in the previous column.
This allows you to compare investments based on the constant rate of return they would need to achieve over a set number of years to grow from its beginning value to its ending value. By smoothing out fluctuations in your investment’s performance over time, this value gives you a steady annual growth rate you can use as an estimate for future performance.
Note: If you are using an unprotected version of the Excel file, you are advised NOT to delete rows from the spreadsheet as this will disturb the functionalities we have programmed into it.
Summary Feature
This feature in our templates gives you a quick snapshot of everything you need to know about your investment as of the last transaction date in the mainsheet. This includes the Total value of your portfolio, the net investment you have put in, and the total net Gain or Loss you have experienced.
It also gives you an annualized return rate for your net investment over the whole duration of the investment period – in this case, the value we have is 7.60%. For perspective, note that the average annualized return of the stock market (e.g., S&P 500 index) over the long term is between 7% to 10% after accounting for inflation though it can vary a great deal year-to-year.
Remember, any changes in the mainsheet will be automatically reflected in this section. We have also added a bar graph and a donut chart for a quick visual representation of your investment journey.
To add more rows for tracking additional investments, you simply need to type information into the first three Columns (B, C & D) without the need to drag the cells. For your ease, all cells showing automatically calculated values have been locked to ensure their specific functionalities and formulae are protected.
Template Disclaimer
We have deliberately kept this template as simple and generic as possible. This spreadsheet does NOT take into account the effect of taxes on your investment trajectories, nor does it account for external economic factors such as inflation. While it is a great tool to monitor the current and projected progress of your investments, it should not be relied on as financial advice for which you should always consult a financial expert or analyst who is well-inversed in the type of investment you are making.
Be aware that spreadsheets are somewhat prone to error. Even if the spreadsheet is completely free of errors at the time you download it, there is always a possibility that you might accidentally introduce errors as you edit it. That’s why we would recommend using this investment tracking template only if you are comfortable using Excel and can identify and fix errors that may be introduced. With that said, download and enjoy!
Our editable template can be instantly accessed in .xlsx, .xltx, and .ods formats.
