Education Savings Calculator in Google Sheets (Step-by-Step)

Photo by Rubaitul Azad on Unsplash

Why build this in Google Sheets?

Tuition, accommodation, and living costs tend to rise faster than general inflation. Without a structured plan, it is easy to underestimate how much you need to save and how early you need to start. Building your own calculator in Google Sheets means you can see every assumption, change any figure, and publish a clean web form for a family member or client to use — all without specialist software.

This guide produces a calculator with seven inputs: current annual education cost, years until enrolment, education inflation rate, expected annual investment return, years of study, existing savings, and monthly contribution currency. The published web form will display four results: projected first-year cost, total projected cost, future value of existing savings, and required monthly contribution.

For background on how compound interest and present-value concepts underpin education planning, the Consumer Financial Protection Bureau savings planner provides plain-language guidance that complements the formulas below.

Key terms explained

• Education inflation: The annual percentage rate at which education costs increase, typically higher than general consumer price inflation.
• Future value (FV): The worth of a sum of money at a specified date in the future, after compound growth at an assumed rate.
• Present value (PV): The current worth of a future sum, discounted at an assumed rate.
• Compound interest: Interest calculated on the initial principal and also on all interest accumulated so far.
• Monthly contribution: The fixed amount added to savings each month to reach a future target.
• PMT function: A spreadsheet function that calculates the regular payment needed to reach a future value given a rate, number of periods, and present value.
• Nominal annual rate: The stated yearly interest or return rate before compounding frequency is applied.
• Education cost gap: The shortfall between total projected education cost and the future value of your existing savings.

What this model covers and what it excludes

This calculator covers tuition and living costs modelled as a single annual figure that grows at a constant rate. It excludes tax-advantaged account rules (such as 529 plans or ISAs), scholarship adjustments, mid-course cost changes, and currency-exchange effects. The investment return assumption is pre-tax and before fees; actual returns will differ. Treat all results as estimates that require verification with a qualified financial adviser before you make real savings decisions.

Prerequisites

• A Google account.
• The GSheetPress GS2Web add-on installed from the Google Workspace Marketplace. After installation, an Extensions → GS2Web SaaS menu will appear in Sheets.
• A Calculator entitlement active on your GSheetPress account. Log in at gsheetpress.com and confirm your plan includes the Calculator feature. You can review feature availability on the pricing page.
• A dedicated sample workbook — do not use a file that contains personal financial data, because the publish step makes the spreadsheet accessible to anyone who holds the link.

Create the calculator template

1. Open your dedicated sample Google Sheet.
2. Rename the active tab EducationCalc (right-click the tab → Rename).
3. Open Extensions → GS2Web SaaS → Calculator → New Calculator.
4. In the dialog that appears, enter the name Education Savings Calculator.
5. Set the number of inputs to 7.
6. Leave the graph option unchecked unless you want a chart on the published form.
7. Click Create. The add-on writes headers into row 1 and placeholder rows for inputs 2 through 8, a button in row 9, a merged Result Fields separator in row 10, result headings in row 11, and result data starting from row 12.

Row 1 columns are generated automatically: A — Labels, B — Input Type, C — Required, D — Data, E — Default, F — ToolTips (some older templates spell this ToopTips), G — Inputs, H — Rules. Do not rename or reorder these columns.

Configure each input row

Work through each of the seven input rows below. For every row: click the specified cell, type the value shown, and press Enter. Leave any cell marked blank empty.

Row 2 — Current annual education cost

• A2 — Label: Current Annual Cost (£)
• B2 — Input Type: Number
• C2 — Required: Check the checkbox (tick it)
• D2 — Data: blank
• E2 — Default: 15000
• F2 — ToolTips: Enter today's total annual education cost including tuition and living expenses
• H2 — Rules: blank

Row 3 — Years until enrolment

• A3 — Label: Years Until Enrolment
• B3 — Input Type: Number
• C3 — Required: Check the checkbox
• D3 — Data: blank
• E3 — Default: 10
• F3 — ToolTips: How many years until the student begins their course
• H3 — Rules: blank

Row 4 — Annual education inflation rate (%)

• A4 — Label: Education Inflation Rate (%)
• B4 — Input Type: Number
• C4 — Required: Check the checkbox
• D4 — Data: blank
• E4 — Default: 4
• F4 — ToolTips: Expected annual percentage increase in education costs. Enter 4 for 4%, not 0.04
• H4 — Rules: blank

Row 5 — Expected annual investment return (%)

• A5 — Label: Annual Investment Return (%)
• B5 — Input Type: Number
• C5 — Required: Check the checkbox
• D5 — Data: blank
• E5 — Default: 6
• F5 — ToolTips: Assumed annual return on your savings, before tax and fees. Enter 6 for 6%, not 0.06
• H5 — Rules: blank

Row 6 — Years of study

• A6 — Label: Years of Study
• B6 — Input Type: Number
• C6 — Required: Check the checkbox
• D6 — Data: blank
• E6 — Default: 4
• F6 — ToolTips: Number of years the student will be enrolled, e.g. 3 or 4 for an undergraduate degree
• H6 — Rules: blank

Row 7 — Existing savings

• A7 — Label: Existing Savings (£)
• B7 — Input Type: Number
• C7 — Required: Check the checkbox
• D7 — Data: blank
• E7 — Default: 0
• F7 — ToolTips: Amount already saved toward education costs today
• H7 — Rules: blank

Row 8 — Currency symbol (optional display)

• A8 — Label: Currency Symbol
• B8 — Input Type: Text
• C8 — Required: Leave the checkbox unchecked
• D8 — Data: blank
• E8 — Default: £
• F8 — ToolTips: Symbol shown beside results, e.g. £, $, or €
• H8 — Rules: blank

Row 9 — Button (generated, do not delete)

The add-on places the Calculate button in row 9. Confirm:

• A9 — Label: Calculate (leave as generated)
• B9 — Input Type: Button
• D9 — Data: main
• E9: If the add-on wrote Reset here, leave it; it controls the reset button label, not the Calculate label.

Rows 10 and 11 — Result separator and headings

Row 10 is a merged Result Fields separator written by the add-on; leave it untouched. Row 11 is for result column headings. Enter these values:

• A11: Result Label
• B11: Value

Add helper cells and result formulas

All input values entered on the web form are written by the add-on into column G at runtime (G2 through G8). Column E holds the defaults that pre-fill the form. To test your formulas before publishing, copy E2:E8, then paste into G2 using Paste special → Values only, so G contains numbers your formulas can read without overwriting anything important.

Result rows begin at row 12. Enter each label in column A and each formula in column B.

Projected first-year cost (B12)

This uses compound growth: current cost multiplied by (1 + inflation rate) raised to the power of years until enrolment.

1. Click cell A12 and type: Projected First-Year Cost
2. Click cell B12 and type the formula below, then press Enter:

=G2*(1+G4/100)^G3

G2 is the current annual cost, G4/100 converts the percentage to a decimal, and G3 is years until enrolment.

Total projected education cost (B13)

This sums the projected cost for each year of study, with each successive year inflating further. The formula uses a geometric series shortcut.

1. Click cell A13 and type: Total Projected Cost
2. Click cell B13 and enter:

=IF(G4=0, G2*G6, G2*(1+G4/100)^G3 * ((1+G4/100)^G6 - 1) / (G4/100))

When the inflation rate is zero the formula simplifies to cost multiplied by years. Otherwise it uses the geometric series to sum annually growing costs across all study years.

Future value of existing savings (B14)

Your existing savings grow at the investment return rate over the years until enrolment plus the years of study.

1. Click cell A14 and type: Future Value of Existing Savings
2. Click cell B14 and enter:

=G7*(1+G5/100)^(G3+G6)

Education cost gap (B15)

The gap is total projected cost minus the future value of existing savings. If the gap is negative, existing savings already cover the full cost.

1. Click cell A15 and type: Savings Gap
2. Click cell B15 and enter:

=MAX(0, B13 - B14)

Using MAX(0,...) prevents a negative gap from producing a misleading negative monthly contribution. If the gap is zero the monthly contribution will also be zero, which is a valid and informative result.

Required monthly contribution (B16)

This uses the PMT function to find the fixed monthly payment needed to accumulate the savings gap. Payments are made monthly, so the annual rate is divided by 12 and the total period in months is years-until-enrolment multiplied by 12.

1. Click cell A16 and type: Required Monthly Contribution
2. Click cell B16 and enter:

=IF(B15=0, 0, IF(G5=0, B15/(G3*12), ABS(PMT(G5/100/12, G3*12, 0, B15))))

Explanation of each part:

• If the savings gap is zero, no contribution is needed: return 0.
• If the return rate is zero, divide the gap by the number of months (no compounding).
• Otherwise, PMT returns a negative value by convention (it represents a payment outflow), so ABS() converts it to a positive number for display.
• The PMT arguments are: rate per period (G5/100/12), number of periods (G3*12), present value (0, because you are building toward a future target from zero additional savings), future value (B15, the gap to close).

Display currency symbol alongside results (optional)

If you want to show the currency symbol from G8 beside each monetary result, you can wrap the number in a TEXT formula. For example, replace B12 with:

=G8&TEXT(G2*(1+G4/100)^G3,"#,##0.00")

Apply the same pattern to B13, B14, B15, and B16. Note that this converts numbers to text, which is fine for display but means you cannot use those cells in further arithmetic. If you prefer arithmetic flexibility, leave B12:B16 as plain numbers and let the web form handle display.

Worked example

Use these inputs to verify your formulas before publishing. Copy E2:E8 to G2:G8 using Paste special → Values only.

Expected results:

The projected first-year cost of £22,203 comes from 15,000 × (1.04)^10. The total of roughly £95,281 sums costs across four inflating years starting at that figure. With no existing savings and a 6% annual return, saving approximately £583 per month for ten years reaches that target.

Alternative scenario — existing savings of £10,000

Change G7 to 10000, keeping all other defaults the same. The future value of existing savings becomes approximately 10,000 × (1.06)^14 = 22,101, the savings gap drops to about 73,180, and the required monthly contribution falls to approximately 448.

Frequently asked questions

What if my required monthly contribution comes out negative?

A negative result from the PMT function means your existing savings, grown at the assumed return, already exceed the total projected cost. The formula wraps PMT in ABS() and applies an outer MAX(0,...) on the savings gap, so the result displayed will be zero — meaning no additional monthly savings are required under those assumptions. Check your inputs; a very high return rate or large existing savings relative to a short study period can produce this outcome legitimately.

Can I change the currency from pounds to dollars or euros?

Yes. Enter your preferred symbol in the Currency Symbol input (row 8 default, G8 at runtime). The model itself is currency-neutral; it performs arithmetic on plain numbers. The symbol is only used if you apply the TEXT display wrapper described in the optional step above. All cost and savings inputs must be in the same currency for the results to be meaningful.

How do I account for scholarships or part-time income?

The simplest approach is to subtract expected scholarship or income amounts from the Current Annual Cost input before entering it. This model does not have a dedicated scholarship field. If scholarships are uncertain, run two scenarios: one with no scholarship and one with the expected amount deducted, to understand the range of monthly contributions you might need.

Troubleshooting

For further help, visit the GSheetPress support page. You can also explore how other calculator types are structured on the calculator details page.

Publish and test your web calculator

Before publishing, make your spreadsheet accessible to the add-on's sharing mechanism. This step is required; without it the published calculator cannot read or write data.

1. In your sample workbook, click Share (top right).
2. Under General access, change the setting to Anyone with the link.
3. Set the role to Editor.
4. Click Done.

Important: Setting access to Editor means anyone who obtains the spreadsheet URL can modify the file directly. This is why you must use a dedicated sample workbook, not a file containing real personal or financial data.

5. Return to the EducationCalc tab (the tab you configured).
6. Open Extensions → GS2Web SaaS → Calculator → Publish Calculator.
7. Wait for the confirmation message. The add-on will indicate the calculator has been published successfully.
8. Open Extensions → GS2Web SaaS → Calculator → Calculator Link. This opens a sheet listing your published calculators.
9. Find the row whose Sheet Name column matches EducationCalc.
10. Open the URL in column C (Link, the public URL). Do not use column B (Edit Link), which opens the backend editor.
11. If the row is missing or the link is stale, click Get Links to refresh the list.
12. On the public calculator page, enter the worked-example values: 15000, 10, 4, 6, 4, 0, £.
13. Click Calculate and confirm the results match the expected values in the worked example table above (approximately £22,203 first-year cost, £583 monthly contribution).
14. Optionally, copy the iframe code from column D and paste it into an HTML block on your website to embed the calculator. You can find guidance on embedding and sharing options on the table details page and the shop details page.
15. If you republish the same calculator later (after changing formulas or labels), the add-on retains the existing public link; you do not need to update any embedded iframes.

Your education savings calculator is now live. Test it with several different input combinations — varying the years, inflation rate, and existing savings — to confirm all result cells respond correctly before sharing the public link.