Wednesday, December 26, 2007

* Reaching Your 10-Year Investing Goal

A few weeks ago The Dividend Guy posted an article titled "What Will it Take to Have $300,000 by 2018?". In his article he calculated how much money he needed to save on a monthly basis and considered various scenarios by changing the rate of return.

Ultimately he settled on a "Below Average Scenario" that used a conservative rate of return of 8%, which is lower than the average market performance over the last 80 years. At 8%, it required him to make monthly payments of around $900 to grow his initial investment of $60,000 to $300,000 over the next 10 years.

In a comment to his post, I noted that if $900/month is currently not in the budget, he might consider another variable "Annual Increases" in the calculation. This would allow for a lower starting monthly contribution that would be made up over time with higher future contributions tied to expected salary increases. Given the uneven cash flows, this is more easily modeled in Excel using the Goal Seek option. The linked model [10-Year Investment Calculator.xls] does just that.

As with any financial calculation of this type you enter Starting Value [PV], Monthly Contribution [PMT], Rate of Return [I/Y], and for this model Period [N] is set at 10 years. The new field added is "Annual Increase in Contribution". When left blank, the model calculates $912 which is approximately what The Dividend Guy came up with. However, if you enter a number in the Annual Increase in Contribution field such as 4% and press the Calculate button next to Monthly Contribution Field, the calculated monthly contribution drops from $912 to to $779. Looking at the 10-year table below you can see that the contribution starts at $779 and grows to $1,109 in the final year. For the hard-core data junkies, each of the years can be expanded by pressing the [+] to the left of the year.

As noted in the spreadsheet, enter your desired 10-year goal in the blue cell and any three variables in the bright yellow cells. To calculate the remaining variable, press the Calculate button next to it. If you want to see what an investment will grow to based on the inputs, enter the variables in the bright yellow cells and watch the value in cell D17.

A few administrative notes:
  1. To download the file, right-click the link and select "Save Target As..."
  2. The spreadsheet was created in Microsoft Excel 2003
  3. To use the Calculate button, the Analysis Toolpak add-in must be installed (see the Analysis Toolpak Help tab in the spreadsheet for instructions on how to do this).
  4. Please note my Disclaimer in the spreadsheet. This model is for illustrative and educational purposes only.
I hope you find the model entertaining and useful.

Related Articles: