Time Value of Money in Google Sheets

Time value of money in one minute

A dollar today is worth more than a dollar later, because today's dollar can be invested and start earning. That single idea — the time value of money — is what every function below is built on.

Two questions cover most of it. Future value asks what money you have now (or add over time) will be worth later. Present value asks what money promised in the future is worth today. For the concept itself, see the future value formula; this page is about getting the answers out of Google Sheets.

The five Google Sheets functions

Each function solves for one unknown. Give it the others and it fills in the blank:

They take the same arguments in the same order as their Excel equivalents, so formulas copy across between the two without changes. What Sheets does differently is name them. Its documentation calls the second argument number_of_periods rather than nper, the third payment_amount rather than pmt, and the timing switch end_or_beginning rather than type:

The longer names are genuinely more readable in the autocomplete tooltip, and they answer a question the Excel abbreviations do not: end_or_beginning makes it obvious that the last argument is about payment timing. Note that RATE still leads with the period count rather than the rate, exactly as in Excel, because the rate is what it is solving for.

FV and PV: the core calculations

FV projects money forward. The syntax is:

The key discipline is matching the rate to the period. This site compounds monthly, so a 7 percent annual rate is entered as 0.07/12 and 20 years becomes 240 months. To value $10,000 already invested plus $500 added every month:

PV runs the same logic backwards, discounting a future amount to today's money:

In other words, $100,000 arriving 20 years from now is worth only about $24,760 today at a 7 percent return — a good illustration of why waiting has a cost.

NPER, RATE and PMT

The other three functions each solve for a different unknown, and they are where a spreadsheet really earns its keep:

Note the *12 on RATE: the function returns a rate per period, so multiplying a monthly result by 12 converts it back to an annual figure.

The sign convention

This is the single most common source of confusion, and it works exactly as it does in Excel. Google Sheets treats these functions as cash flows:

• Money you pay out is negative — deposits, contributions and the starting balance you commit.
• Money you receive is positive — the future value that comes back to you.

That is why the contribution appears as -500 and the starting balance as -10000 in the FV formula above. Enter them as positives and Sheets simply returns a negative answer — the math is unchanged, only the sign flips.

Locale, separators and the arguments Sheets renames

The one thing that genuinely breaks copied formulas in Google Sheets has nothing to do with finance. Sheets picks its argument separator from the spreadsheet's locale. In a locale that uses a full stop for decimals — United States, United Kingdom — arguments are separated by commas, and =FV(0.07/12, 240, -500, -10000) works as written. In a locale that uses a comma for decimals — Germany, France, Brazil, Spain and much of Europe and Latin America — the comma is already taken, so Sheets expects semicolons instead:

Paste the comma version into a comma-decimal sheet and you get a formula parse error, not a wrong number, which at least fails loudly. The confusing case is the reverse: a decimal typed as 0,07 in a full-stop locale is read as text or as two arguments, and the error message points at the function rather than the number.

Two practical consequences:

• Check the locale before blaming the formula. It lives under File → Settings → General, and it is a per-file setting, not a per-account one — so a sheet someone shared with you can use a different separator from the one you just created. The same panel controls the currency symbol and the default date format.
• Point at cells rather than typing decimals. The cell-reference model below sidesteps the decimal question entirely: =FV(B3/12, B4*12, -B2, -B1) contains no decimal point at all, so it survives being copied between sheets in different locales. Only the separators need swapping.

This is also the reason percentage formatting is worth using in Sheets: type 7% into the rate cell and Sheets stores 0.07 regardless of locale, with no decimal separator involved.

A worked example you can copy

Set up four input cells and one formula, and you have a working calculator:

Then in B5, enter:

The result is about $300,851. You contributed $130,000 in total — the $10,000 start plus $120,000 of deposits — so roughly $170,851 of the balance is compound growth. Change any input cell and the answer updates instantly, which is what makes a spreadsheet worth building. The monthly compound interest formula with contributions shows the same math written out by hand.

One formula for the whole schedule

A single FV result tells you the destination but not the path. In Sheets you do not need to drag a formula down twenty rows to see the path — SEQUENCE generates the period numbers and ARRAYFORMULA applies FV to all of them from one cell:

Using the same inputs as the example above — $10,000 to start, $500 a month, 7 percent — that returns twenty year-end balances in one column:

Laid out this way the compounding is visible rather than asserted. Growth overtakes the amount paid in during year 17 — $115,820 of growth against $112,000 of deposits — and the last five years add $114,000 of balance on only $30,000 of new deposits. That crossover is the argument for starting early, and it is hard to feel from a single ending number.

Two Sheets-specific notes on this: ARRAYFORMULA has no Excel equivalent because modern Excel spills array results automatically, so this exact wrapper is one of the few formulas on this page that does not copy across unchanged. And if you want the chart, select the output column and use Insert → Chart — the range updates itself when you change an input cell, because the array is generated rather than typed.

Sheets vs an online calculator

Both give identical answers, so it comes down to what you are doing:

• Use Google Sheets when you want to keep and reuse a model, compare many scenarios side by side, chart the growth, or share a live file with someone else.
• Use an online calculator when you want a quick answer without worrying about syntax or signs.

The time value of money calculator returns the same figures immediately, and the future value calculator handles the lump-sum-plus-contributions case shown above. If you prefer Microsoft's tool, the Excel version of this guide covers the identical functions.

Assumptions and limits

• Monthly compounding. The examples divide the annual rate by 12 and count periods in months, matching this site's calculators.
• End-of-period payments. The type argument is 0, the default; use 1 for payments made at the beginning of each period.
• A constant rate and payment. These functions assume both stay fixed for the whole term; real returns and contributions vary.
• Nominal figures. Results are before inflation, tax and fees, all of which reduce real growth.

Function reference

Google's own documentation gives the canonical signature and argument names for each function:

• FV, PV, NPER, RATE and PMT — the Google Docs Editors Help entries for the five financial functions used here.
• Change a spreadsheet's locale, time zone, recalculation and language — the setting that determines whether your formulas take commas or semicolons.

How the figures on this page were checked. Every balance was recomputed independently from the compound-interest and annuity formulas at full precision and rounded only for display, then compared against the function output. Rates are nominal annual rates divided by 12, with periods counted in months. Last verified 8 September 2026.

Frequently asked questions

The bottom line

Five functions cover every time value of money question in Google Sheets. Keep the rate and the periods in the same unit, enter money you pay out as a negative, and FV, PV, NPER, RATE and PMT will handle the rest — $10,000 plus $500 a month at 7 percent reaching about $300,851 over 20 years.

If you would rather skip the spreadsheet entirely, the time value of money calculator gives the same answers in one click.