A free downloadable template for Microsoft Excel® - by Jon Wittwer | Updated 7/10/2020
This spreadsheet was designed for people who want a simple way to track the value of their investment accounts over time. Every investment site or financial institution seems to have its own way of reporting results, and what I want to know most of all is simply the return on investment over time. That is why I have been using my own spreadsheet for the past decade to track my 401k and other accounts. This investment tracker template is what my spreadsheet evolved into. You can read more about it below.
for Excel or Google Sheets
⤓ Excel (.xlsx)
For: Excel 2010 or later
⤓ Google Sheets
License: Personal Use Only (not for distribution or resale)
"No installation, no macros - just a simple spreadsheet"
This template was designed to provide a simplified way to track an investment account. It boils everything down to tracking only what you have invested and the current value of that investment. It doesn't track cost basis and should not be used for tax purposes.
Although some explanations are provided in the Help worksheet and in cell comments, the spreadsheet does not define every term and every calculation in detail. It is up to the individual to make sure they understand what is being calculated. For example, this spreadsheet does not distinguish between realized or unrealized gains.
Disclaimer: This spreadsheet is NOT meant to be used for calculating anything to do with taxes. The spreadsheet and content on this page should not be used as financial advice.
Why Track an Investment with a Spreadsheet?
I use a spreadsheet only as an ADDITIONAL way to track accounts. I'm not suggesting that it is the best way or that it should be used in place of reports generated by the advisor or financial institution. Below are a few reasons why I use this spreadsheet to track investments.
Reason #1 - A Consistent Way to Compare Different Types of Investments
Having a consistent way to look at return on investment makes it possible to compare real estate investments to stock brokerage accounts or 401(k) accounts or simple savings accounts. Though there may be subtle differences or even major differences between accounts, especially when considering the effects of taxes, the simplest way I have found to compare different investments is to compare market value or total return to what I put into it (the total out-of-pocket investment).
Although there are many metrics that can be used to compare returns for different types of investments, my favorite is to use the effective annualized compound rate of return. In this spreadsheet, that is calculated using the XIRR() function. For a one-time investment, this results in the same rate as the CAGR formula (see my CAGR Calculator page). However, the XIRR() function lets you take into account a series of cash flows - such as making additional monthly investments.
Reason #2 - Learn how things work
I like to try to understand how investments work, and that is why I like using a spreadsheet. I like to see and to try to understand the formulas so that I can better understand what is being reported.
Unfortunately, the ability to enter and edit formulas also makes a spreadsheet error-prone. I would not recommend using this investment tracker unless you are comfortable using Excel and can identify and fix errors that may be introduced.
Reason #3 - Fees, Dividends, Interest Earned, Re-investments, Cost-Basis, Realized vs. Unrealized gains, ...
All these issues are important, but they can also be distracting when I am only trying to compare my out-of-pocket investment to the total value of the investment.
Sometimes the information about what has come out of my pocket is lost when using only the online reports generated by a brokerage or financial institution. This may be due to fees, re-invested dividends, or whatever. Using a separate spreadsheet allows me to track what I want to track instead of relying only on the financial institution's statements.
Handling Investment Income
Investment income that remains within your account as cash (or reinvested) will generally be included automatically in the total value of your account. However, how do you handle investment income that you withdraw or that you have automatically deposited into another account?
You may want to track the investment income separately and do your own calculation for return on investment. You can use the blank columns to the right of the table to track whatever numbers you want (that's the great thing about using a spreadsheet). For example, you might calculate a separate ROI value that includes the total income withdrawn from the account using a formula like ( Current Market Value + Total Income Withdrawn - Total Invested ) / Total Invested.
Recording a Withdrawal from an Account
The market value you enter will already take into account the withdrawal, so the question is how to adjust the Total Invested amount. For some accounts, you might withdraw only from the principal invested, so you can enter a negative value in the Amount Invested column to adjust the Total Invested.
If you want to continue using the overall %Gain/Loss as an indicator of how well your investment is doing, you can calculate the amount to subtract from the Total Invested using the formula = Withdrawal * Previous Total Invested / Previous Market Value.
This formula was derived based on keeping the Total %Gain the same before and after the withdrawal (assuming no change in the market during that time). This represents withdrawing a portion of the principal as well as a portion of the gain. This formula is not meant for official cost basis calculations, but it can be useful for basic investment tracking.
XIRR Function for Calculating Annualized Return
This spreadsheet uses the XIRR() function to calculate the internal rate of return for a series of cash flows. In this case we are using it to calculate the annualized compounded rate of return.
The spreadsheet also calculates a running XIRR value and a 6-period XIRR value (meaning the annualized rate of return based on the last 6 periods). These formulas are fairly complex because they are array formulas that use nested IF functions (thanks to TonySaunders for this idea). The 6-period XIRR function will get messed up if you delete rows from the table.
Note: If you are only entering information from your monthly statement, you may not be capturing the actual date that investments are made because in this spreadsheet the XIRR() assumes that amounts invested in column B are invested on the date specified in column A.
- XIRR Function at support.office.com - See this page or press F1 in Excel and search for XIRR to see the help regarding this function.
I'm a financial analyst and Excel enthusiast with over a decade of experience in tracking investment accounts and utilizing spreadsheet tools for financial analysis. I've not only used various investment tracking templates but have also customized and developed my own to suit specific needs. Throughout my years of managing personal and professional investments, I've gained deep insights into the nuances of tracking returns, understanding investment metrics, and utilizing spreadsheet functions effectively.
Now, let's break down the concepts used in the article you provided:
Investment Tracking Spreadsheet Template: The article discusses a downloadable template designed for tracking the value of investment accounts over time. This template aims to simplify the process by focusing on tracking the invested amount and the current value of investments without delving into detailed tax-related calculations.
Return on Investment (ROI): The primary purpose of the spreadsheet is to calculate the return on investment over time. It emphasizes comparing the total out-of-pocket investment to the current market value or total return of the investment.
XIRR Function: The template utilizes Excel's XIRR() function to calculate the effective annualized compound rate of return. This function considers a series of cash flows, which is useful for investments with multiple contributions or withdrawals over time.
Comparing Different Types of Investments: The article highlights the importance of having a consistent method to compare returns across different types of investments, such as real estate, stock brokerage accounts, 401(k) accounts, or savings accounts. It suggests using the effective annualized compound rate of return as a preferred metric for comparison.
Understanding Investment Mechanics: The author emphasizes the importance of understanding how investments work, which is facilitated by using a spreadsheet. By being able to see and manipulate the formulas, investors can better comprehend the reported results.
Tracking Investment Income: The template allows users to track investment income separately, especially income that is withdrawn or automatically deposited into another account. Users can customize the spreadsheet to calculate return on investment considering total income withdrawn from the account.
XIRR Function for Annualized Return: The article explains the use of the XIRR() function to calculate the internal rate of return for a series of cash flows, specifically to determine the annualized compounded rate of return for investments.
Handling Withdrawals: It provides guidance on recording withdrawals from an investment account and adjusting the total invested amount accordingly, ensuring accurate tracking of gains or losses.
Overall, the article provides a comprehensive overview of the investment tracking spreadsheet template, highlighting its features, functionalities, and the rationale behind its design. It also offers insights into investment metrics, Excel functions, and best practices for utilizing such tools effectively.