In previous posts (see link below), I have talked about several types of financial calculators available on the Internet to calculate asset allocation and emergency fund goals. However, what I realized I had not yet addressed was a tool that I use almost daily with this website: Retirement / Time Value of Money Calculators!
My Money Blog – Financial Calculators
The Time Value of Money
First, what is the concept of The Time Value of Money? Basically, it is the idea that by saving a Dollar today and investing it for the long-term with compound interest, the money will grow exponentially over time.
The basic equation for the Time Value of Money is shown in the graphic below. Essentially, it is computed by multiplying the present value (PV) x (1+ interest rate)^(number of years).
Exponential growth of money sounds very good right? Let’s now see how it can be applied to your retirement calculations.
Essentially, there are two methods to do the calculations regarding your retirement nest-egg (both employ exponential growth): using Excel Spreadsheets and using pre-built retirement calculators.
Personally, I favor using Excel Spreadsheets because they are quick and highly customizable to my particular analytical needs. However, let’s look at each tool one by one.
Pre-Built Retirement Calculators
Pre-made calculators essentially do all of the things that a spreadsheet can do, except that the calculations take place automatically at the click of a button.
My favorite pre-built retirement calculator can be accessed using the link below. The reason I favor this one is because it allows you to enter information about your specific situation both before and after retirement.
However, one thing that I do not like about these calculators is that many do not take in to consideration the decrease in buying power of money over time (aka inflation).
Calculations Using Excel Spreadsheets
Once you master the art of making Time Value of Money spreadsheets, the sky is basically the limit with how detailed you can make it. However, for the sake of keeping things simple, I wanted to only discuss two basic applications of Excel spreadsheets that I use all of the time: compounding interest and discounted monetary quantities over time due to inflation effects (these are also known as calculating future value and present value for you finance geeks out there).
To demonstrate these two applications, let’s build a spreadsheet to calculate the amount of money you will have at retirement. An example of the finished spreadsheet can be found at the following link:
After checking out the example, just follow the steps below to create your own:
1) Open Excel
2) Create 5 columns (Columns A-E). In Row 1 of each column, type in the labels “Age,” “Current Amount,” “Annual Amount Contributed,” “Salary,” and “Percent Salary Contributed Each Year” from left to right for Columns A-E.
3) In the age column (Column A), type in your current age in cell A2. Using the addition function (=A2 Cell + 1), copy the code until you have ages listed from your current age until the retirement age of 65.
4) In the Current Amount column, Column B, type in the Dollar amount that you currently have saved for retirement in cell B2.
5) In the Salary Column (Column D), type in your current salary in cell D2. Next, you will type in the formula to let you grow your salary, assuming an annual raise of 5%. To do this, type “=D2 Cell*1.05” in cell D3. Next, copy this code down the entire column until your retirement age.
6) In Column E, type in the % of your salary you currently and/or plan on contributing to retirement for all of the years until age 65.
7) Complete Column C by copying the following formula down the entire row: “=D2 Cell * E2 Cell.” This will give you the amount that you plan to contribute each year, based on the % specified in Step 6.
8) Now for the most important step!
In cell B3, you will type in the Compound Interest formula. This sounds scary, but it really is very easy. So, in cell B3, type in the following, “=B2 Cell*(1+ rate of return) + C2 Cell.” You can choose the rate of return that best suits your investment portfolio. The average return of the stock market that I use is either 12.4% (aggressive) or 9% (moderate). Next, copy this formula down the entire column until you fill out the table. In words, what this formula is doing is growing your previous year’s balance by the interest rate and then adding the new money you contributed during the year.
9) You can then view what balance you will potentially have at retirement in the last cell in Column B. You have just completed the compounding interest/calculating future value portion of the exercise! Congrats!
10) Now, in order to get a more realistic picture of the buying power we will have at retirement, the amount at age 65 in Column B needs to be discounted by the rate of inflation, which can be assumed to be 3.2%
To do this, type in the following formula below the last calculation in Column B: “Column B Cell @ Age 65 / (1.032)^(65-Your Current Age).” When you do this, the amount should go down signficantly. That will then give you an idea in today’s Dollars how much you will have!
So, congratulations! You have just learned a very powerful financial tool that you can use to calculate anything that involves the Time Value of Money (house values, costs of living, opportunity costs, etc). The opportunities are endless!
I know this topic is a little dense, so please let me know if you have questions.
Keep on learning!
To receive updates on topics such as this one as soon as they are published, click on the link below to subscribe to My Money Blog: