Time Value of Money in Excel

The time value of money in one minute

The time value of money is the principle that a sum today is worth more than the same sum later, because money you hold now can be invested and earn a return. Every calculation moves an amount either forward in time (its future value) or backward (its present value) using a rate and a number of periods. Excel bundles all of this into a handful of functions, so you never have to type the algebra by hand. If you would rather not open a spreadsheet at all, the time value of money calculator does the same conversions in your browser.

The five Excel functions you need

These five functions cover almost every time-value question. They work the same way in Excel and Google Sheets. Each solves for one unknown when you supply the others:

In every one, rate is the interest rate per period, nper is the number of periods, and type is 0 for end-of-period cash flows (the default) or 1 for the beginning. The arguments in square brackets are optional.

Note the argument order, because it is not the same in all five. FV, PV, NPER and PMT all start with rate; RATE starts with nper, since the rate is the thing it is solving for. Two of them also take their third argument from opposite ends of the timeline: PMT's third argument is pv, while FV's fourth is. Getting the order wrong rarely produces an error — it produces a plausible-looking number, which is worse.

Underneath, all five solve the same equation for a different unknown. Microsoft documents it as:

That single line explains most of the behaviour that looks strange: why the terms have to sum to zero (hence the signs), why type multiplies only the payment term, and why RATE has to iterate rather than rearrange — there is no closed-form solution for rate when a payment is involved.

FV and PV: the core calculations

The two functions you will use most are FV and PV. To find what $10,000 becomes at 7 percent, compounded monthly, over 20 years, convert the rate and periods to monthly terms and enter the deposit as a negative number:

The 0.07/12 is the monthly rate, 240 is 20 years in months, 0 is the recurring payment (none here), and -10000 is the starting amount entered as a cash outflow. To go the other way and find what a future $10,000 is worth today, use PV:

The result is negative because it represents the amount you would pay today to receive $10,000 in 20 years. In plain terms, a future $10,000 is worth about $2,476 in today's money at that rate.

NPER, RATE and PMT: solving for time, rate and payment

Because the functions are algebraically linked, you can rearrange the question and let Excel solve for whichever piece is missing.

How long until $10,000 grows to $40,387? NPER returns the number of periods:

What rate is needed? RATE returns the rate per period, so multiply by 12 for the annual figure:

What payment reaches a goal? To build $100,000 in 20 years at 7 percent, PMT returns the deposit per period:

The payment is negative because it is money you put in. So contributing about $192 a month gets you to $100,000.

Building it once with cell references

Typing 0.07/12 inside a formula works, but it buries the assumptions where nobody can see or change them. The version worth keeping puts every input in its own labelled cell and points the function at the cells instead. Put labels in column A and values in column B:

Rows 6 and 7 are the whole point: the two conversions that cause most errors now happen once, visibly, in cells you can check. Change B3 from 12 to 4 and the whole model switches to quarterly compounding without touching a formula.

Two Excel specifics worth knowing when you extend this:

• Lock the shared inputs before you copy across. Write the formula as =FV($B$6,$B$7,$B$5,$B$4) if you plan to drag it into columns C, D and E to compare scenarios. Without the dollar signs, Excel shifts every reference one column right and each copy silently reads empty cells — which Excel treats as zero, so you get $0 rather than an error.
• Enter the rate as a decimal or as a percentage, not as the number 7. If B1 is formatted as a percentage, typing 7 stores 0.07 and displays 7%. If it is formatted as a plain number, typing 7 stores 7 — a 700 percent monthly rate. Checking that B6 reads roughly 0.0058 takes two seconds and catches this immediately.

Changing the compounding frequency

Excel's functions have no compounding-frequency argument. Frequency is expressed entirely through rate and nper: divide the annual rate by the number of compounds per year, and multiply the years by the same number. Here is $10,000 left for 20 years at a 7 percent annual rate, at five frequencies:

Two things are worth taking from this. First, the direction: more frequent compounding always produces more, because interest starts earning interest sooner. Second, the size: going from annual to monthly adds $1,690 over 20 years — 4.4 percent more than annual compounding produces — while going from monthly all the way to daily adds only $159 more. The first step matters; the last one is close to a rounding difference.

The trap is that both arguments must move together. Divide the rate by 12 but leave nper at 20 and Excel answers a different question — 20 months of growth, or $11,234 — without complaining. This is why the cell-reference model above computes both from the same compounds per year input.

The sign convention that trips everyone up

The single most common Excel mistake is not the formula — it is the signs. Excel treats every cash flow from your point of view:

• Money leaving your pocket is negative — deposits, investments and payments.
• Money coming to you is positive — the future value you collect or the present value you receive.

If you enter your starting deposit as a positive number, the answer comes back negative, which looks wrong but is just the mirror image. The fix is simple: enter what you invest as a negative number, and the result reads as a positive balance. The other classic error is mixing time units — always pair a monthly rate with a month count, or an annual rate with a year count.

A worked example you can copy

Here are six ready-to-paste formulas, all using 7 percent compounded monthly. They match the figures this site's calculators produce, so you can check your sheet against them.

Five mistakes that break the answer

Excel rarely tells you a time-value formula is wrong. Four of the five errors below return a number, not an error message, which is exactly what makes them dangerous. Every result here was produced from the equation above, so you can recognise the shape of each failure:

How to spot each one:

• Scientific notation is always a rate error. A result like 5.56E+220 means Excel compounded 700 percent per period 240 times. If the answer is in scientific notation, check the rate cell first.
• An implausibly large but readable number means mismatched units. $112 billion is 7 percent applied 240 times instead of 20. The reverse error — a monthly rate with 20 periods — is the more insidious one, because $11,234 looks perfectly reasonable.
• A negative future value means the signs disagree, not that the formula is wrong. Here the $500 payment was entered as money coming in while the $10,000 was money going out, so Excel netted them. Both should be negative.
• RATE and NPER return per-period figures. RATE gives a monthly rate when you fed it monthly inputs; multiply by 12 for the annual equivalent. NPER gives months; divide by 12 for years.
• #NUM! is a real answer. RATE and NPER solve iteratively, and if every cash flow you give them has the same sign there is no rate or period count that satisfies the equation, so Excel returns #NUM! rather than guessing. It is telling you the scenario is impossible, not that Excel is confused. RATE also accepts an optional guess argument (default 10 percent) for the rare case where a genuine solution exists but the default starting point does not converge on it.

Excel vs an online calculator

Both approaches use identical math, so pick the one that fits the task:

• Use a spreadsheet when you want to build a reusable model, chart the growth, or run many scenarios side by side.
• Use an online calculator when you want a quick answer without setting up cells — the time value of money calculator for present and future value, the future value of money calculator for a single sum, or the future value of annuity calculator for recurring payments.

Many people do both: sketch the idea in a calculator, then build it out in a sheet once the numbers look right.

Assumptions and limits

• A constant rate. The functions apply one fixed rate per period; real returns vary, so the output is an estimate.
• Matched periods. Rate and nper must share a time unit — monthly rate with months, annual rate with years.
• Nominal figures. Results are before inflation and tax, both of which reduce real growth.
• Sign convention. Cash paid out is negative and cash received is positive; getting this wrong flips the answer.

Function reference

Microsoft's own documentation is the authority on argument order and edge cases, and each page lists the full signature plus the errors the function can return:

• FV function — FV(rate,nper,pmt,[pv],[type])
• PV function — PV(rate, nper, pmt, [fv], [type])
• NPER function — NPER(rate,pmt,pv,[fv],[type])
• RATE function — RATE(nper, pmt, pv, [fv], [type], [guess])
• PMT function — PMT(rate, nper, pv, [fv], [type])

How the figures on this page were checked. Every result was computed independently from the equation Microsoft documents for these functions, at full precision, and rounded only for display. Where a formula returns a per-period figure, the annual conversion is shown explicitly rather than implied. Last verified 8 September 2026.

Frequently asked questions

The bottom line

Excel and Google Sheets turn the time value of money into five functions — FV, PV, NPER, RATE and PMT — that each solve for one unknown. Keep your rate and periods on the same clock, enter money you pay in as a negative number, and you can answer almost any what-will-it-be-worth or what-do-I-need question in a single cell.

When you want the answer without building a sheet, the time value of money calculator does the same conversions instantly, and the future value formula guide covers the algebra underneath.