Time Value of Money in Google Sheets
Google Sheets has five built-in functions that handle every time value of money question: FV, PV, NPER, RATE and PMT. Write =FV(0.07/12, 240, -500, -10000, 0) and Sheets returns about $300,851 — the value of $10,000 plus $500 a month at 7 percent over 20 years, compounded monthly. This guide gives you copy-ready formulas, explains the sign convention that trips people up, and walks through a full worked example.
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 of money 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:
| Function | Solves for | Typical question |
|---|---|---|
FV | Future value | What will my savings be worth in 20 years? |
PV | Present value | What is $100,000 in 20 years worth today? |
NPER | Number of periods | How long until I reach $100,000? |
RATE | Interest rate | What return do I need to hit my goal? |
PMT | Payment | How much must I save each month? |
They take the same arguments in the same order as their Excel equivalents, so formulas copy across between the two without changes.
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:
| Goal | Formula | Result |
|---|---|---|
| How long to reach $100,000 saving $500/month at 7%? | =NPER(0.07/12, -500, 0, 100000) | About 133 months (11.1 years) |
| What monthly payment reaches $1,000,000 in 30 years at 7%? | =PMT(0.07/12, 360, 0, 1000000) | About $820 per month |
| What annual rate turns $10,000 into $40,387 in 20 years? | =RATE(240, 0, -10000, 40387)*12 | About 7% |
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.
A worked example you can copy
Set up four input cells and one formula, and you have a working calculator:
| Cell | Input | Value |
|---|---|---|
| B1 | Starting balance | 10000 |
| B2 | Monthly contribution | 500 |
| B3 | Annual rate | 0.07 |
| B4 | Years | 20 |
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.
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
typeargument 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.
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.
Disclaimer: This guide is for general educational purposes only and is not financial advice. The examples use assumed rates of return to illustrate the functions; they are projections, not guarantees, and actual results vary with markets, inflation, taxes and fees. Consider speaking with a qualified financial professional before making decisions about your own money.