Back to TVM
Calculator

 

Example 18 - College or Retirement Planning


This example applies to our online demo Time Value of Money Calculator. The C-Value! program for Windows works in a similar way and has a few more features. Note, our online demo TVM calculator is limited to calculations using interest rates between 2.0% and 8.9%

A savings plan for college tuition or retirement with a regular increase in the quarterly deposits and an increase in the annual withdrawals based on an assumed inflation rate.

For greater detail about how values are entered into the TVM calculator, please see Example 1 - conventional mortgage or loan.

Background: The general point of this example is to illustrate how to calculate the deposit amount required for a series of deposits that, at the end, will result in a balance that will be large enough to cover a series of anticipated withdrawals. The amount being deposited is being increased to account for a steady increase in savings and the amount being withdrawn is being increased at a rate equal to the expected inflation rate. The inflation adjustment as well as the increase in the savings are entirely optional. Furthermore, the savings (deposit) amounts, the increase in the savings, the withdrawal amount and the withdrawal adjustment can be at irregular intervals. However, to keep the example somewhat simpler, we opted to make all deposits, withdrawals and adjustments at regular intervals.

Assume we want to plan for a college education. Presently, one year's tuition costs $22,500.00. The student will be starting college classes twelve years from now. In order to solve this problem accurately, we need to make two different calculations. First, we need to assume some rate of inflation so that we can calculate what the anticipated tuition will be twelve years in the future. After that, we need to calculate how much needs to be saved on a periodic basis to reach this goal. We will also assume that the amount we can save will increase by 5% per annum.

To create a savings schedule followed by a series of withdrawals, follow these steps:

  1. Click the [New] button to clear any previous entries.
  2. Set "Rounding" to "Open Balance" by clicking on the "Rounding" button.
Rounding options
  1. (This example is not as daunting as it may first appear. We think we go into a lot of detail in order to show you just how flexible the TVM Calculator can be.)
  1. Click {Setup}. Select {Set calculation options...} to open the "Calculation Options" window. Select "Compute/Amortization Methods" tab.
  1. For the "Compute Method" select the "Normal" option.
  2. For the "Year Length" select 360.
  3. Click on the [OK] button to close the Window.

The first calculation requires us to calculate the anticipated annual tuition cost. For this example we are going to assume an 8% annual inflation rate.

  1. Set the compounding option to "Annually".
  2. Enter 8% for the "Nominal Annual Rate".
  3. Click on the first row of the cash flow grid and select "Deposit" for the first event type.
  1. Set the date to July 1, 2012 (mm/dd/yyyy)
  2. Enter $22,500 for the "Amount".
  3. Set the "# Periods" of periods to 1.
  1. Click on the second row of the cash flow grid and select "Withdrawal" for the first event type.
  1. Set the date to July 1, 2024
  2. Set the "Amount" to "Unknown"
  3. Enter 1 for the "# Periods" of periods.
  1. Click on "Calc" to solve for the unknown future value.

The anticipated cost of one year's college tuition will be $56,658.83 in twelve years. (Yikees!)

Inflation adjusted college tuition

The second calculation...

  1. The next step is to calculate how much we will need to save each quarter to meet this obligation. So, make a note of this result, $56,658.83 and click on the [New] button.
  2. Now, set compounding to "Daily".
  3. Enter 4.500% for the "Nominal Annual Rate".
  4. Create the first event as a "Deposit".
  1. Set the "Date" to July 1, 2012.
  2. For the "Amount", enter "Unknown".
  3. Enter 48 for the "# Periods".
  4. Select "Quarterly" for the "Period"
  5. The "End Date" is calculated and will be April 1, 2024.
  6. To increase the rate of savings by 5% a year:
    1. Click in the right most column of the first row under "Special Series..." to open the "Special Series" window.
    2. Select the "Percent Step" tab.
    3. Click on the "Activate Percent Step series for the currently highlighted event" checkbox.
    4. For "Starting amount", type a "U" to display the word "Unknown"
    5. Since we want to increase the savings rate by 5 percent a year and since we are making quarterly deposits we will enter 1.25% (the user will not enter the percent sign). Increasing each deposit by 1.25% will equal about 5% per year.
    6. Set "Number made before change" to 1
    7. Click [OK] to close the window and to activate the special series. You should now see a "Percent Step" in the "Special Series..." column

If you are following along, your screen will look like this

Setting up investment plan.
  1. Create the 2nd event. It will be a "Withdrawal".
  1. Enter the "Date" July 1, 2024.
  2. Enter the "Amount" $28,325.00. This is about one-half the result of the calculation from Step 8 above or in other words, the estimated amount for one semester's tuition assuming two semesters per year. Since the calculation is approximate because we are estimating an inflation rate, the amount entered is rounded.
  3. Enter 8 for "# Periods" (8 semesters)
  4. Set the "Frequency" to "Semiannually". (2 semesters per year)
  5. To continue to increase the cost of tuition by 8% per year:
    1. Click in the right most column of the first row under "Special Series..." to open the "C-Value! Special Series" window.
    2. Select the "Percent Step" tab.
    3. Click on the "Activate Percent Step series for the currently highlighted event" checkbox. This should put a check in this box.
    4. The "Starting amount" edit box will show $28,325.00.
    5. Since we want to assume an 8 percent tuition inflation rate a year and since we are making semiannual withdrawals, we will enter 4%. Increasing each tuition payment by 4% will equal approximately 8% per year.
    6. Set "Number made before change" to 1
    7. Click [OK] to close the window and to activate the special series. You should now see a second "Percent Step" in the "Special Series..." column

The screen will now look like this:

College financial planning calculation setup.
  1. Calculate the "Unknown". The result is $2,824.71. In other words, by starting with a periodic investment of $2,824, and investing every quarter that amount, increased by 1.25% (or 5.0% annuallY), while assuming a 4.5% return on your investment and keeping it up for 12 years, you'll be able to pay for a college education that starts out costing $28,325 a semester. You'll be able to make semiannual withdrawals and pay for 8 semesters that increase by 4.0% a semester (or 8% a year). (Please pick yourself up off the floor.)
  2. College financial planning calculation solved.
  1. If you want to see a detailed cash flow schedule showing just what the monthly deposits earn in interest as well as what your investments continue to make, even while paying for colledge, click on the "Schedule" tab above the input area and click on [Print Preview]. Notice that the amount of the deposits total $184,251.28 and that the savings earned $76,742.06 in interest. The withdrawals total $260,992.95.
College planning financial cash flow schedule.
  1. To visualize your cash flows, click on the "Charts" tab. Three different charts are automatically created. No additional work on your part is required. Pass your mouse over any point or bar on the chart to create a pop-up with details.
College planning financial charts.

Back to the online Time Value of Money Calculator.

Home    Site Help    Site Map     © Pine Grove Software, LLC, All Rights Reserved.    Privacy Policy    Contact Us     Store    Blog