Use Excel’s FV function to calculate an investment’s future value. For example, if B2 contains an annual interest rate, B3 the number of years, B4 a monthly contribution, and B5 the starting balance, enter =FV(B2/12,B3*12,-B4,-B5,0); this assumes monthly compounding and payments made at the end of each month.
What FV Means in Excel
FV stands for future value: the value that money invested now, regular payments, or both may reach after earning interest over a specified number of periods. Excel’s FV function is useful for estimating a savings balance, a regular deposit plan, or the accumulated value of another cash-flow arrangement with a consistent interest rate.
The result is a mathematical estimate based on the inputs, not a promise about what an account will earn. Actual account returns may vary, and fees, taxes, changing rates, and withdrawal rules are not automatically included. For financial decisions, check the real terms with the financial institution or a qualified professional.
FV is designed for payments of equal size that occur at regular intervals, alongside a rate that stays constant from period to period. If the amount or timing of payments changes, or the interest rate varies, a single FV calculation may not describe the situation accurately.
How the FV Arguments Work
Excel’s FV function takes the interest rate per period, the total number of periods, the periodic payment, the present value, and an optional timing setting. In that order, those inputs tell Excel how quickly money grows, how long it grows, what is added regularly, what is already invested, and whether each payment is made at the beginning or end of a period.
- Rate: The interest rate for one payment period. For monthly payments and a nominal annual rate, divide the annual rate by 12.
- Nper: The total number of payment periods. For ten years of monthly deposits, use 10 × 12, or 120 periods.
- Pmt: The fixed payment made in every period. Enter zero if there are no regular payments.
- Pv: The present value, or starting amount. Enter zero if there is no initial balance.
- Type: Use 0 for payments at the end of each period or 1 for payments at the beginning. If omitted, Excel uses 0.
The rate and number of periods must use the same time unit as the payments. A monthly payment needs a monthly rate and a count of months; mixing an annual rate with a monthly period count can produce a dramatically incorrect estimate.
A Simple Lump-Sum Example
Suppose $10,000 is deposited once and earns a fixed 4% per year for five years, with no later contributions. For this direct-entry example, the inputs are a 4% rate, five annual periods, no periodic payment, and a present value of -$10,000. Enter =FV(4%,5,0,-10000). The result is about $12,166.53, before any fees or taxes.
The negative starting value reflects cash moving out of the saver’s perspective at the beginning: the saver puts in $10,000, then receives a positive future balance. The calculation compounds interest annually. It does not assume monthly compounding, because the example has one annual period per year.
This example also shows why the FV result may have the opposite sign from the amount entered for the starting balance. Excel represents cash flows as money moving in or out, rather than treating every input as a positive balance. The sign convention is important when checking a result.
Example With Monthly Contributions
Now suppose someone contributes $200 at the end of every month for ten years, earns a constant nominal annual rate of 5%, and starts with no balance. With the annual rate in B2, years in B3, monthly contribution in B4, and starting balance in B5, set B2 to 5%, B3 to 10, B4 to 200, and B5 to 0. The formula =FV(B2/12,B3*12,-B4,-B5,0) returns approximately $31,056.38.
The person contributes $24,000 in total over 120 months. The difference between that amount and the estimated ending value comes from interest earned over time, including interest on earlier deposits. Each later deposit has fewer months to grow than an earlier one, which is why the result is not the same as investing the full $24,000 on the first day.
The example assumes that 5% is a nominal annual rate divided into 12 equal monthly rates, and that each deposit is made after the month’s interest is credited. A bank or investment account can calculate interest differently, so use the provider’s stated rate and compounding method when matching an actual product.
Set Up an FV Worksheet
A small input table makes the calculation easier to inspect and update. Put labels in column A and values in column B; for example, use rows for annual rate, years, monthly contribution, and starting balance. Format the rate as a percentage and the money inputs and result as currency. Number formatting changes how values look, not the underlying FV calculation.
Using the B2:B5 layout described above, the formula divides the annual rate by 12 and multiplies the number of years by 12. The minus signs before the contribution and initial balance express those deposits as money paid into the account from the saver’s point of view. The final zero explicitly chooses end-of-month payments, although that zero could be omitted because it is the default.
Keep the assumptions visible near the result. A label such as “estimated balance after 10 years” is clearer than simply calling the output “FV,” and a note about the rate convention helps prevent someone from mistaking a nominal annual rate for an effective annual yield. If a result seems surprising, checking each input’s unit is often more useful than changing the formula at random.
Payment Timing: Beginning or End of Period
Most recurring savings deposits are modeled as end-of-period payments, which is why type 0 is the default. With a monthly schedule, that means each deposit is added at the end of its month and begins earning interest in the following month. An annuity due uses type 1: each payment arrives at the beginning of the period and earns one additional period of interest.
To compare the two timings using the same worksheet—annual rate in B2, years in B3, monthly contribution in B4, and starting balance in B5—change only the final argument to 1: =FV(B2/12,B3*12,-B4,-B5,1). With the earlier example’s values, the beginning-of-month schedule gives approximately $31,185.78 rather than $31,056.38. That difference reflects each of the 120 contributions earning one extra month of interest.
Choose the timing that matches the real cash-flow schedule, not the one that produces a more appealing number. If deposits are made on varying dates, neither timing option exactly represents every deposit date.
Rates, Compounding, and Assumptions
Dividing an annual nominal rate by 12 is appropriate when the rate is quoted as a nominal annual rate compounded monthly. It is not the same as converting an effective annual rate to a monthly rate. If the quoted figure is an effective annual rate, the equivalent monthly rate is the twelfth root of one plus that annual rate, minus one. Identify which rate convention applies before entering the rate.
FV assumes the same rate in every period and, when payments are included, the same payment amount at regular intervals. For instance, a plan that increases contributions each year, skips some months, or earns a changing return does not satisfy those assumptions. You can model separate periods or cash flows with a more detailed worksheet, but one basic FV formula cannot capture all of those changes by itself.
Also consider what the result leaves out. FV does not automatically subtract account fees, adjust for inflation, include taxes, or account for limits and conditions in a savings product. If you want a rough inflation-adjusted comparison, compare the projected future balance with prices expected at that future date; do not interpret the nominal FV amount as having today’s purchasing power.
Common FV Errors and How to Check Them
- Using an annual rate with monthly periods: Divide a nominal annual rate by 12 when the modeled periods are months. Also multiply years by 12 for monthly payments.
- Entering the payment as positive: If the starting balance and payments are both treated as positive cash flows, Excel may return a negative FV. Use signs consistently from one point of view.
- Forgetting the initial balance: Set pv to zero only when the account really starts empty. An existing balance can materially affect the projection.
- Choosing the wrong type: Check whether deposits happen at the start or end of a period. This affects every regular payment.
- Expecting a changing rate or payment: A single FV result uses one fixed rate and one fixed payment amount. It is not a forecast that adapts automatically to real-world changes.
For a quick reasonableness check, compare the projected balance with total contributions. When the rate is positive and the cash flows and signs are set correctly, a long-term savings balance will generally exceed the amount contributed. If the result is negative or unexpectedly small, inspect the rate units, signs, period count, and timing before trusting it.
When to Use FV Instead of Another Method
FV is a good fit when the question is, “What could this fixed set of deposits grow to?” It works for regular savings contributions and for a lump sum with no repeating payments. If you instead know a target balance and want to estimate the required periodic deposit, Excel’s PMT function is designed for a related payment calculation. If you want to find the interest rate needed to reach a goal, RATE may be relevant, provided the cash flows fit its assumptions.
These functions answer different questions, so they are not interchangeable. A loan payment estimate, for example, uses cash flows from a borrower’s perspective and depends on the loan’s terms and payment schedule. For an actual loan or account, verify the rate, compounding, payment dates, fees, and other conditions with the lender or a qualified professional; a spreadsheet estimate is not financial, tax, investment, or legal advice.
Frequently Asked Questions
What is the FV formula in Excel?
Use the FV function with a rate per period, number of periods, periodic payment, present value, and optionally a payment-timing setting. Match the rate and period count to the payment schedule, and use consistent cash-flow signs.
Why does Excel show a negative future value?
Excel uses cash-flow signs. If your contributions and starting balance are entered as positive amounts, the result may be negative to balance the cash-flow direction. Review the perspective you are using and make deposits outflows and the eventual balance an inflow.
Should I use type 0 or type 1?
Use 0 when payments happen at the end of each period, and 1 when they happen at the beginning. If you leave this optional argument out, Excel assumes end-of-period payments.
Can FV calculate monthly savings from an annual interest rate?
Yes. For a nominal annual rate with monthly compounding, use the annual rate divided by 12 and set the number of periods to the number of years multiplied by 12. Confirm the account’s actual rate convention before relying on the estimate.
Does FV include fees or inflation?
No. FV calculates the result implied by the rate and cash flows you provide. Fees and inflation need separate treatment, and taxes or product-specific terms are not automatically included.
Related
- Excel Border Shortcuts: Apply, Change, and Remove Borders
- Bluebeam to Excel: Export Markups and Quantities
- Bluebeam Quantity Link to Excel: Setup and Limits
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add an Attachment to a Word Mail MergeTo add a different attachment to each Word mail-merge email, put each recipient’s file path in your data…
- Create Rules in Outlook on the Web: A Step-by-Step GuideTo create a rule in Outlook on the web, open Settings, go to Mail > Rules, select Add…
- Excel Accounting Number Format: Show Negative Numbers in RedYes. To show negative values in red while keeping an accounting-style currency display in Excel for Microsoft 365…
- Excel Historical Sales Analysis: Find Trends and PatternsTo analyze historical sales in Excel, organize transactions by date, summarize sales by month or another consistent period,…
- Excel Find and Replace for Single QuotesTo find or replace a single quote in Excel for Microsoft 365 on Windows, press Ctrl+H, type the…
- Replace Words in Excel: Find and Replace GuideTo replace a word in Excel, press Ctrl+H on Windows, enter the existing word in Find what and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.