Over the years I’ve consistently found myself Googling, “investment projection calculator” or “retirement calculator” to size up a potential investment. What I found out was that each and every one ends with a different return, despite having provided the exact criteria. How is that even possible using static figures? To that, I have no idea.
However, in an effort to pull back the curtain I started searching for ways to create my own investment calculator. My rules were simple. First, calculations must be visible at each step. Second, the result must be real. That second step was necessitated by the fact many online calculators somehow end in millions with the smallest contributions. Just didn’t make any sense to me.
In any case, below you’ll find the steps to use the Investment Projection Calculator. I hope you’ll enjoy calculating your way to an early retirement and the life of your dreams. Please feel free to download the Calculator below whenever you like. It is free and safe to use.
OptionBoxer Investment Projection Calculator Spreadsheet
How to Use the Investment Projection Calculator
Step 1. Starting Balance
Enter the desired starting balance for any investment you prefer.
Step 2. Contribution & Contribution Periods
Select the desired contribution amount and the number of contribution periods. Furthermore, the spreadsheet maxes out at 41 periods. However, take notice that the contribution amount is altered by the time horizon selection discussed below.
Step 3. Annual Growth Rate
Input the expected annual rate of return. For example, the S&P 500 has historically returned about 9% per year.
Step 4. Initial Share Price
Enter the current share price for the investment.
Step 5. Dividend Yield
Input the yearly percentage dividend yield. To put it differently, add up the number of dividend payments per year and divide by the current share price. However, most brokers do provide the yield as a yearly figure.
Step 6. Annual Dividend Growth Rate or Dividend CAGR
Finding this metric isn’t always easy but calculating it isn’t difficult. We can never know when a company may just do away with the dividend so rather than hunt down specific details over the last 30 years I typically use a 5 year growth rate. To explain, simply calculate the 5 year dividend growth rate as follows;
Dividend CAGR = (Current Dividend Amount / Historical Dividend Amount) ^ (1 / # years) – 1
Additionally, the dividend CAGR or growth rate can be found here from SeekingAlpha.com. Enter any asset in the search box at the top of the screen. Select dividends > dividend growth.
Step 7. Options Yield
Many will enter this as “0.00”. However, as an options trader I’d like to know how that income is boosting my return. Calculate options yield as follows;
Options Yield = (Options Income / Investment Amount)
To clarify, this figure will change each year so determining in average over time would be useful.
Step 8. Annual Inflation & Starting Age
Inflation is the metric on everyone’s mind right now. Typically, the US maintains about a 2% inflation rate each year. However, moving that percentage higher would be ideal for the more conservative among us.
Additionally, you could input the starting age to see at what age milestones will be achieved. Although, this entry doesn’t alter the calculations one way or another so avoiding it wouldn’t cause any issue.
Step 9. Time Horizon
Enter in the desired time horizon. The Investment Projection Calculator is built to allow for custom settings of weekly, monthly, or yearly. However, it simply alters the age settings so it can easily be ignored.
Step 10. Projection Graph
Once all the criteria above has been provided an investment projection graph will generate. Then click the “View Projection Graph” button to jump to the graph. Indeed, seeing the data represented graphically is somehow more satisfying.
To sum up, the above steps and the included spreadsheet provide every step I use to calculate potential return on an investment over time. All in all I believe its a realistic approach to determining a potential end goal. In the long run, knowing the possible outcome for an investment can add some much need resolve to weather every storm. Particularly, the bear market we find ourselves in as I write this tonight and even more so the bear markets that follow.
Finally, I hope you enjoy using the spreadsheet and have some fun calculating your way to a prosperous tomorrow.