Future Investment Value with Contributions in Google Sheets

Photo by KOBU Agency on Unsplash

What this guide builds

By the end of this walkthrough you will have a Google Sheets workbook that answers questions such as: "If I invest £10,000 today, add £200 every month, earn 6% a year after fees, and inflation runs at 2.5%, how much will I have in 20 years in today's money?" You will also publish it as a public web calculator using the GSheetPress add-on so anyone can enter their own numbers without touching your formulas.

The model covers a single asset class with a constant assumed annual return, a flat annual management fee, and equal regular contributions. It does not model tax wrappers (ISAs, 401 k), variable fee schedules, dividend reinvestment at the fund level, or currency conversion. All return assumptions are illustrative only and do not constitute financial advice; verify rates with a qualified adviser or your product's key information document.

Key terms explained

• Compound growth: Earning returns not just on your original money but also on the returns already accumulated, so balances accelerate over time.
• Nominal value: The raw cash figure at a future date, before adjusting for the purchasing-power effect of inflation.
• Real (inflation-adjusted) value: The nominal figure deflated back to today's purchasing power, showing what those future pounds or dollars are worth in today's terms.
• Annual management fee (AMF): A percentage charge deducted each year from the fund balance, expressed here as a percentage of the fund value.
• Net annual return: The gross investment return minus the annual management fee — the rate actually credited to your balance.
• Regular contribution: A fixed amount added at regular intervals; here you choose monthly or annual frequency.
• Contribution timing: Whether each contribution lands at the start of a period (beginning-of-period, also called annuity due) or at the end (end-of-period, ordinary annuity). Beginning-of-period contributions earn one extra period of growth.
• FV function: Google Sheets' built-in Future Value function that calculates a final balance given a rate, number of periods, regular payment, and opening balance.
• Scenario analysis: Running the same model with different assumed return rates (e.g. 4%, 6%, 8%) to illustrate the range of possible outcomes.
• Inflation rate: The assumed annual percentage rise in the general price level, used to deflate nominal future values.

Prerequisites

• A Google account with access to Google Sheets.
• The GSheetPress add-on installed in your Google account (install from the Google Workspace Marketplace; open any Sheet, choose Extensions → Add-ons → Get add-ons, then search GSheetPress GS2Web SaaS and install).
• Your GSheetPress account email must match the Google account running the add-on. Log in at gsheetpress.com/admin/signup.html to confirm or create your account.
• A Calculator entitlement on your GSheetPress plan. Check your entitlements on the GSheetPress pricing page.
• A dedicated sample workbook — create a new blank Google Sheet named something like Investment Calculator – Sample. Do not use a workbook containing private financial data.

Step 1 — Build the formula model in a helper sheet

Before creating the web calculator you need a working formula layer. This keeps your logic testable and makes it easy to reference results later.

1.1 Create the Inputs sheet

1. In your sample workbook, rename Sheet1 to Inputs by double-clicking the tab.
2. Add a second sheet and name it Model.
3. Add a third sheet and name it Scenarios.

1.2 Set up named input cells on the Inputs sheet

Enter these labels in column A and the default values shown in column B. You will reference these cells by address throughout.

1.3 Add core derived values below the inputs

In column A enter the label; in column B enter the formula. These intermediate values are referenced by the Model sheet.

Column B holds the computed value for each label in column A. The formula in B10 converts an annual rate to its monthly equivalent using the standard compounding relationship rather than a simple division, which is more accurate for monthly compounding. See Google's FV function documentation for the underlying mathematics.

1.4 Calculate the nominal future value with FV()

Click cell B13 on the Inputs sheet. Type the label Nominal FV (£) in A13, then click B13, type the following formula, and press Enter:

=FV(B10, B11, -B2, -B1, B7)

Parameter explanation: rate = monthly net rate (B10); nper = total months (B11); pmt = monthly contribution entered as a negative because it is a cash outflow (−B2); pv = opening balance also as a negative outflow (−B1); type = 1 for beginning-of-period or 0 for end-of-period (B7). The result is a positive future cash value.

1.5 Calculate the real (inflation-adjusted) future value

In A14 enter the label Real FV (today's £). Click B14 and enter:

=B13 / (1 + B5/100)^B6

This deflates the nominal figure by applying the inflation rate over the full investment term, giving you the equivalent purchasing power in today's money.

1.6 Calculate total contributions and total growth

Step 2 — Build a year-by-year Model sheet

A row-by-row table makes it easy to inspect balances at every year-end and to verify the final figure matches B13 on the Inputs sheet.

2.1 Set up column headers on the Model sheet

Click the Model tab. In row 1 enter these headers:

2.2 Enter year 1 formulas (row 2)

The model uses annual steps. Monthly contributions are aggregated to an annual total so the table stays readable; the FV calculation in Step 1 remains the authoritative end-balance figure.

2.3 Enter subsequent year formulas (row 3, then fill down)

Click A3 and enter =A2+1. Click B3 and enter =E2. Click C3 and enter =Inputs!B2*12. Click D3 and enter =(B3 + IF(Inputs!$B$7=1,C3,0)) * Inputs!$B$9. Click E3 and enter =B3 + C3 + D3. Click F3 and enter =E3 / (1 + Inputs!$B$5/100)^A3.

Now select A3:F3 and drag the fill handle down to the row matching your term. For a 20-year term, fill to row 21 (years 1 through 20). The closing balance in E21 should be within a few pounds of the FV result in Inputs!B13; any small difference arises from the beginning-of-period approximation in the annual table versus the precise monthly FV formula — the FV formula is the authoritative figure.

Step 3 — Build the Scenarios sheet

Running three return assumptions side by side shows how sensitive your outcome is to the assumed rate.

3.1 Set up the Scenarios sheet

Click the Scenarios tab. Enter these headers and formulas:

In rows 2, 3, and 4 enter the scenario names and return rates, then use nested FV formulas that substitute each scenario rate while keeping all other inputs from the Inputs sheet constant.

Step 4 — Worked example and result checks

Use the following inputs to verify your formulas before building the web calculator. These numbers are chosen to be easy to cross-check by hand.

With these inputs the net annual return is 5.25% and the monthly net rate is approximately 0.4270%. Expected outputs:

Cross-check: total invested = £10,000 + (£200 × 240 months) = £58,000. Nominal growth of roughly £64,400 on £58,000 invested represents about 111% growth over 20 years at a net 5.25% — plausible for compound growth over two decades.

Alternative scenario check: Change the gross return to 4% and the management fee to 0.75%, giving a net return of 3.25%. The nominal FV should drop to approximately £86,500 and the real FV to roughly £52,500. This illustrates the significant sensitivity of long-term outcomes to assumed return rates and is precisely why scenario analysis is included.

For more on building financial models in Google Sheets you can explore the GSheetPress calculator features page and the table display options for presenting results on your site.

Frequently asked questions

Why does my Model sheet closing balance differ slightly from the FV formula result?

The year-by-year Model sheet aggregates 12 monthly contributions into one annual total and applies growth once per year. The FV formula in the Inputs sheet compounds monthly — it applies the monthly rate 240 times for a 20-year term. Monthly compounding is more accurate and is the figure you should rely on. The annual table is useful for inspecting year-by-year progress, not for the final authoritative balance.

How do I account for increasing contributions over time, for example rising with salary?

The FV function assumes a fixed payment. To model increasing contributions, extend the Model sheet with an extra column for a contribution growth rate and multiply each year's contribution by (1 + growth rate)^(year − 1). You would then sum the year-by-year closing balances rather than relying on the single FV formula. The Inputs-sheet FV cell can remain as a constant-contribution baseline for comparison.

Can I include the effect of tax on returns or withdrawals?

This model does not incorporate tax. Tax treatment depends on the type of account (general investment account, ISA, SIPP, 401 k), the country of residence, the investor's marginal rate, and the nature of the return (income versus capital gain). Adding a tax column to the Model sheet is possible once you have confirmed the applicable rules with a tax professional or HMRC or IRS guidance. Do not apply a blanket percentage without verifying the specific rules that apply to your situation.

Troubleshooting

Build the web calculator with GSheetPress

You now have a working formula model. The steps below convert it into a public web calculator so visitors can enter their own numbers without accessing your spreadsheet. Work entirely in the dedicated sample workbook.

Create the calculator

1. Open your sample workbook and click Extensions in the menu bar.
2. Choose GS2Web SaaS (or the name shown if you installed a different version of the add-on).
3. Choose Calculator, then New Calculator.
4. In the dialog that appears, enter a name such as Investment Growth Calculator.
5. Set the number of inputs to 7 (opening balance, monthly contribution, gross return, management fee, inflation rate, term, contribution timing). Do not count the Calculate button.
6. Choose whether to include a chart if prompted. A simple bar or line chart of scenario values is optional but useful. Click OK or Create to generate the template.

GSheetPress creates a new sheet tab (usually named Calculator) with pre-generated column headers in row 1: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules in columns A through H respectively. Do not rename or delete these headers.

Understanding the generated layout

For 7 inputs the layout is as follows:

• Rows 2–8: one input per row (your 7 inputs)
• Row 9: the Calculate button (generated automatically)
• Row 10: a merged Results separator row
• Row 11: result column headings
• Row 12 onward: individual result rows

Column E holds the form defaults (what the user sees before they type anything). Column G (Inputs) holds the values submitted by the user at runtime. All result formulas must reference column G cells or the Inputs sheet cells that the calculator tab populates — never store result formulas in column G itself because web submissions overwrite it.

To test formulas before publishing, copy your default values from E2:E8 and paste them into G2:G8 using Paste special → Values only so that your result formulas have something to calculate against.

Input row setup — row by row

Fill in each input row as described below. Leave any cell blank where indicated.

Row 2 — Opening balance

• A2 — Label: Opening balance (£)
• B2 — Input Type: Number
• C2 — Required: Tick the checkbox (required)
• D2 — Data: Leave blank
• E2 — Default: 10000
• F2 — ToolTips: The lump sum you invest on day one
• H2 — Rules: min:0

Row 3 — Monthly contribution

• A3 — Label: Monthly contribution (£)
• B3 — Input Type: Number
• C3 — Required: Tick the checkbox
• D3 — Data: Leave blank
• E3 — Default: 200
• F3 — ToolTips: Fixed amount added each month; enter 0 for no contributions
• H3 — Rules: min:0

Row 4 — Annual gross return

• A4 — Label: Annual gross return (%)
• B4 — Input Type: Number
• C4 — Required: Tick the checkbox
• D4 — Data: Leave blank
• E4 — Default: 6
• F4 — ToolTips: Expected yearly return before fees, e.g. enter 6 for 6%
• H4 — Rules: min:0;max:50

Row 5 — Annual management fee

• A5 — Label: Annual management fee (%)
• B5 — Input Type: Number
• C5 — Required: Tick the checkbox
• D5 — Data: Leave blank
• E5 — Default: 0.75
• F5 — ToolTips: Yearly fund charge as a percentage, e.g. enter 0.75 for 0.75%
• H5 — Rules: min:0;max:10

Row 6 — Annual inflation rate

• A6 — Label: Annual inflation rate (%)
• B6 — Input Type: Number
• C6 — Required: Tick the checkbox
• D6 — Data: Leave blank
• E6 — Default: 2.5
• F6 — ToolTips: Assumed yearly inflation for calculating today's purchasing power equivalent
• H6 — Rules: min:0;max:20

Row 7 — Investment term

• A7 — Label: Investment term (years)
• B7 — Input Type: Number
• C7 — Required: Tick the checkbox
• D7 — Data: Leave blank
• E7 — Default: 20
• F7 — ToolTips: How many whole years you plan to stay invested
• H7 — Rules: min:1;max:50

Row 8 — Contribution timing

• A8 — Label: Contribution timing (1=start, 0=end)
• B8 — Input Type: Number
• C8 — Required: Tick the checkbox
• D8 — Data: Leave blank
• E8 — Default: 1
• F8 — ToolTips: Enter 1 if contributions are made at the start of each month; enter 0 for end of month
• H8 — Rules: min:0;max:1

Row 9 — Calculate button (generated, do not delete)

• A9 — Label: Calculate (this is the button label; leave as generated or type Calculate)
• B9 — Input Type: Button
• D9 — Data: main
• E9 should read Reset (this is the reset button label; leave as generated). Do not confuse E9 Reset with A9 Calculate.
• All other cells in row 9: leave as generated.

Result rows setup

Row 10 is the merged Results separator — leave it as generated. Row 11 holds result column headings — leave as generated. Enter result labels in column A from row 12 onward and enter the corresponding formulas in column B.

All formulas below reference the Calculator sheet's own G column (G2 through G8) which receive the submitted values at runtime. At design time, copy E2:E8 and paste values-only into G2:G8 so the formulas return numbers rather than errors.

The net annual rate is used repeatedly, so define it as a helper in column B of a spare area, or inline it. Below it is inlined to keep the calculator self-contained.

To enter each formula: click the target cell, type the = sign followed by the rest of the formula as shown, then press Enter. Do not include a leading apostrophe or wrap the formula in quotes.

The IF in B18 avoids a division-by-zero error if someone enters zero for both opening balance and monthly contribution. For any other cell that could divide by zero, use a similar IF guard rather than a blanket IFERROR that hides meaningful errors.

You can also link to the GSheetPress support pages if you need help configuring result display formats such as currency symbols or decimal places.

Publish and test your web calculator

1. Before publishing you must share the workbook so GSheetPress can read it. Click the Share button (top right), choose General access, set it to Anyone with the link, and set the role to Editor. Click Done. Important: this setting means anyone who obtains the spreadsheet URL can edit the workbook directly. Use only your dedicated sample workbook — never a file containing private data.
2. Click the Calculator tab (your calculator sheet, not the Inputs sheet) to make it the active tab.
3. Open Extensions → GS2Web SaaS → Calculator → Publish Calculator.
4. Wait for the confirmation message that the calculator has been published.
5. In the add-on panel choose Open Calculator Link. A sheet listing your published calculators will open.
6. Find the row where the Sheet Name column matches your calculator tab name. Use the Link in column C (the public calculator URL) — do not use the Edit Link in column B, which opens the spreadsheet itself.
7. If the row is not visible, click Get Links to refresh the list.
8. Open the public link in a new browser tab. You should see the web form with your seven input fields and the Calculate button.
9. Enter the worked-example values: opening balance 10000, monthly contribution 200, gross return 6, management fee 0.75, inflation 2.5, term 20, timing 1. Click Calculate.
10. Verify that the results match the expected values from Step 4: nominal FV approximately £122,400, real FV approximately £74,400, total invested £58,000.
11. If results match, your calculator is live. If they do not, return to the Calculator sheet, check that G2:G8 have been populated with values (paste defaults from E column), and confirm each formula references the correct G cell row.
12. To republish after any formula changes, repeat steps 3 and 4 using Publish Calculator on the same tab. Republishing retains the existing public link.
13. To embed the calculator on a website, copy the Iframe code from column D of the links sheet and paste it into an HTML block in your site's page editor.