Work backward from a goal to find the monthly deposit you need. Here is the formula, an example and the Excel version.
Updated November 2026
Start from a goal
The best way to plan college savings is to pick a target and work backward. Costs vary widely by school and program and rise over time, so choose a figure you can defend, perhaps from a school’s published cost of attendance, and revisit it every year. The college savings calculator shows your projected balance and the deposit needed to reach the goal.
The formula
Here i is the monthly return (annual rate / 12) and N is the number of months.
Worked example
You have $5,000 saved, add $250 a month and expect 6 percent a year for 12 years. With i = 0.005 and N = 144 the balance reaches $62,791.29. Against a $120,000 goal that is 52.3 percent, leaving a gap of $57,208.71.
How much to save each month
Solve the formula for the deposit: Deposit = (Goal – Savings x (1 + i)^N) x i / ((1 + i)^N – 1). For the example the deposit needed is $522.23 a month. In Excel use the PMT function with a negative present value: =PMT(0.06/12,144,-5000,120000) returns $522.23. The sign convention matters: the balance is money you already hold (negative) and the goal is the future value (positive).
Test the return assumption
| Annual return | Balance after 12 years |
|---|---|
| 4% | $54,182.79 |
| 6% | $62,791.29 |
| 8% | $73,144.04 |
Returns are not guaranteed. Use a conservative figure and check again as the date gets closer, when many families move toward lower-risk investments.
Other levers
- Start early. Time does more than deposit size.
- Raise deposits each year, for example after a raise.
- Include gifts from family if you expect them.
- Look at tax-advantaged education accounts. Rules and limits vary, so confirm them with a qualified professional.
Use the future value calculator for general savings goals and the compound interest calculator for a lump sum.
These are illustrations to help you plan, not financial advice.
Frequently asked questions
How much should I save for college each month?
Work backward from your goal. In the example, $522.23 a month reaches $120,000 in 12 years at 6 percent.
How do I calculate it in Excel?
=PMT(rate/12,months,-current savings,goal) returns the monthly deposit.
What return should I assume?
A conservative one. Test several, since returns vary and are not guaranteed.
Do I need to account for inflation?
Costs tend to rise, so raise your goal or deposits over time.