O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  

Buy the book!
Online Investing Hacks
By Bonnie Biafore
June 2004
More Info

Balance Risk and Return for College Costs
Building a college nest egg can take some time, which means that your investment strategy must change depending on where your child is on the road to college
[Discuss (0) | Link to this hack]

With college tuition soaring , parents who want to pay for their kids' education need as much help as they can get. Tax-advantaged education accounts and are a huge help, but by far the best thing for college savings is time. By starting to save for college early, you can invest in stocks and earn returns that keep up with or exceed college tuition increases. However, stocks are risky for the short term , so you must move your savings into safer investments as your child gets closer to college age.

Get a Head Start on College Costs

If you're an overachiever, you can start setting money aside for college before your bundle of joy is even a glimmer in your eye. For many parents, the birth of a child is a wake-up call (even several a night for many months). As the costs of raising an infant begin to roll in, you might forget about the college costs looming decades in the future. However, these early years are great for getting ahead of the college curve. Start investing money regularly in mutual funds that own stocks. Because of the number of years you'll have to save and the higher returns that stocks or stock-based mutual funds provide, your monthly contribution can be more reasonable, as demonstrated in . With 12 years of investing at a 10 percent return, you need about $350 a month to reach $100,000. If you don't start saving until five years before school and use safe investments paying 4 percent return, you'll need $1500 a month to reach the same goal.

Figure 1. The earlier you start to save, the less you have to contribute each month to reach your goal

Stock-based mutual funds are the easiest investments for this kind of savings plan. You can set up an automatic investment program, so that the mutual fund deducts your monthly contribution automatically from your checking account. When it's time to sell some of your stock investments, you simply sell the appropriate number of shares of the fund. You won't have to consider which stocks to sell or how to rebalance the stock portfolio for diversification.


To make the most of compounding, be sure to reinvest the dividends that your stocks or mutual funds distribute.

The Middle Years

The adolescent years bring a host of problems, both psychologically and financially. You're on your own interfacing with your teenager. Fortunately, the financial transformation of college savings will seem easier by comparison. Because you don't want to lose money in the stocks in your college savings when there's little time for them to recover, the transition to safer investments begins when your child is 12 years old. The conventional advice is to start depositing new savings into safer investments, such as long-term certificates of deposit or zero coupon treasury bonds (also known as zero-coupon treasuries) that mature when your child heads off to college. However, if you make automatic monthly deposits to a stock fund, you could choose to continue that, and sell a chunk of your stocks or mutual funds to meet your target percentage for CDs or bonds. However, if a recession appears imminent, switch your monthly deposits to a secure savings option.


A zero coupon bond does not pay interest during its lifetime. Instead, investors buy zero coupon bonds at a deep discount from their face value, which is the lump sum the investor receives when the bond matures. In addition to higher rates than bonds that pay interest regularly, zero coupon bonds usually carry long terms such as eight or more years, so you can purchase zero coupons that mature exactly when you need money for tuition.

By the time your child is 14, about half of your college nest egg should be in safer investments. Therefore, between ages 12 and 14, you must gradually reduce the percentage of funds invested in stocks from 100 percent to 50 percent. Realistically, you continue to reduce the percentage of money in stocks until all your college savings are safe by the time your child heads off to college.

Using CDs, traditional bonds, or zero coupon bonds for your secure savings also requires some planning. You want sufficient holdings to mature each year. In addition, if you decide to add your monthly contributions to safe investments, you could end up with numerous CDs for each contribution. Small bond purchases could kill you with minimum commissions . Here's one approach to keep things simple:

  1. When you transfer money from stocks to safe investments, buy a fixed-income investment that matures in time for freshman year.

  2. Continue using stock transfer dollars to buy fixed-income investments that mature for freshman year up to the amount you forecast for freshman year expenses.

  3. When freshman year is covered, start buying fixed-income investments that mature for sophomore year until sophomore expenses are covered. Continue this approach to cover junior and senior years.

  4. When you want the monthly contribution to go into safe savings, deposit the monthly contribution in a money market account, money market fund, or savings account with a decent interest rate.

  5. Twice a year, use the money in the money market to purchase fixed-income investments that mature for the college year that isn't yet covered.

Hacking the Hack

Unfortunately, the money you invest in stocks grows faster than the money in safe investments, and the monthly contributions further unbalance your plan. Trying to figure out how much money to transfer between stocks and safe havens each year might give tax preparation a run for its money as something you'd rather not do. That's why it's a good idea to create an Excel spreadsheet to help you plan your college savings program.

The spreadsheet in assumes a steady monthly contribution from the time your child is born until she graduates from college. $400 a month adds up to well over $200,000 when you save for 22 years. So, sending your child to a good, private school requires nothing more than saving the money you would spend on a cappuccino on your way to work and eating lunch at a restaurant each workday.

Figure 2. Calculate the money you should invest in stocks and fixed-income investments to reach your goal

The spreadsheet uses the Future Value (FV) function to calculate how much your savings grow to over time. shows the FV function calculating the result of saving $400 a month for the first 12 years. This example starts with a zero balance, so the present value parameter is zero.

Here's how the FV function works:

Future Value = FV(rate,periods,payment,balance,type)

The interest rate per period. Because the return rate in cell B2 is an annual rate, the rate parameter in this example divides cell B2 by 12 to obtain a monthly rate.


The number of periods you plan to save. Because the function in cell C6 calculates 12 years of return (the number of years of saving in cell B3 minus the years before college in cell B6), the number of periods equals 12 multiplied by the number of years.


The monthly contribution.


Known as the present value, this is the amount of money you have at the beginning of the savings period, in this case, zero.


For a savings plan, enter 1 as the type parameter. This calculates the future value assuming that you contribute at the beginning of each period.

When your child reaches age 12, the example switches to yearly calculations. In this example, the monthly contribution goes into stocks, so the formula for calculating the future value takes into account both monthly contributions and the balance at the beginning of each year, as shown in .


In , the balance or present value is the amount of money in stocks the previous year (cell K8).

The spreadsheet performs several calculations to determine how much money you need to transfer to safe investments each year:

Safe Nest Egg

Use the FV function to calculate the value of your safe investments at the end of a year using the safe return in cell B3. Beginning the freshman year of college, the monthly contribution is made to safe investments, so it appears in the payment parameter in these cells.

Total Nest Egg

The total amount of savings you have at the beginning of the year.

Withdrawal from safe

The amount of money you must withdraw to pay for a school year. This value is zero until freshman year, and then increases by about 10 percent a year until graduation to take into account potential tuition and board increases.

Remaining Nest Egg

The savings that remain after withdrawing money for a school year.

Target stock %

The percentage of savings you want in stocks at the beginning of the year. This value drops each year until freshman year of college, at which point all savings should be safe.

Target safe %

The percentage of savings in safe investments at the beginning of each year.

Transfer to safe

The amount of money you should transfer to safe investments after any withdrawals to meet your target safe percentage. To calculate this value, subtract the amount of money in safe investments from the target safe dollars, and then add in the value of any withdrawals.

Target stock $

The dollars you want in stock investments to meet your target stock percentage.

Target safe $

The dollars you want in safe investments to meet your target safe percentage.


The returns you achieve don't always match your forecasts. Because the returns you supply for calculations are average annual returns, your year-to-year results almost never match your estimates. For that reason, when you get to the beginning of each year processed in your spreadsheet, replace the values for stocks and safe investments in columns C and D with the actual balances in your accounts.

O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.