In Excel, use the FV function to calculate a value in the future after interest compounds: =FV(rate,nper,pmt,[pv],[type]). For example, an initial $1,000 invested for five years at 10% per year, with no additional payments, grows to about $1,610.51 using =FV(10%,5,0,-1000).
What Excel FV Calculates
FV means future value. The function estimates what a series of cash flows will be worth at the end of a specified number of periods, assuming a constant interest rate and, if applicable, equal payments made at regular intervals. It works for a one-time starting balance, recurring deposits, recurring loan payments, or combinations of those cash flows.
Compound interest means interest is added to a balance and can itself earn interest in later periods. For a single starting amount, the underlying calculation is the initial amount multiplied by one plus the rate, raised to the number of periods. FV applies that idea while also accounting for regular payments when you provide them. It is a projection based on the inputs, not a prediction that a real account will earn a fixed return.
The function is useful for savings estimates, classroom examples, and checking how payment timing or compounding frequency changes a result. It assumes the rate and payment pattern you enter stay consistent. It does not automatically include taxes, account fees, inflation, changing rates, or irregular deposits.
Understand the FV Arguments
The syntax is =FV(rate,nper,pmt,[pv],[type]). The first three arguments are required; the last two are optional. Each argument should describe the same time interval.
rateis the interest rate for one period, such as a month or a year.nperis the total number of periods. For monthly contributions over ten years, it is 120.pmtis the regular payment made each period. Enter zero if there are no recurring payments.pvis the present value, or starting balance. If omitted, Excel treats it as zero.typesets payment timing: 0 or omitted means payments occur at the end of each period; 1 means they occur at the beginning.
For example, with an annual rate, use a yearly payment amount and count years. With a monthly rate, use a monthly payment and count months. Mixing an annual rate with a monthly period count is a frequent source of unrealistic answers.
Calculate a Lump Sum in Excel
Suppose you want to estimate the future value of $1,000 after five years at a constant annual rate of 10%, with no further deposits. You can enter the formula directly in a blank cell, or put the inputs in cells so they are easy to change.
With the annual rate in B2 as 10%, the number of years in B3 as 5, and the starting investment in B4 as -1000, enter =FV(B2,B3,0,B4). The result is approximately $1,610.51. The negative starting value represents money paid into the investment; Excel returns the future value as a positive amount under its cash-flow sign convention.
The arithmetic is consistent with annual compounding: $1,000 multiplied by 1.10 five times is about $1,610.51. If you enter 1000 as a positive present value instead, Excel will return a negative future value. That does not mean the calculation is broken; it reflects the opposing signs of cash paid out and cash received.
Model Regular Deposits and Payment Timing
FV can include a fixed deposit made every period. Consider saving $100 at the end of each month for ten years at a nominal annual rate of 5%, compounded monthly. The monthly rate is 5% divided by 12, and there are 120 monthly periods.
With the annual rate in B2 as 5%, the saving period in B3 as 10 years, and the monthly deposit in B4 as 100, enter =FV(B2/12,B3*12,-B4,0,0). The result is approximately $15,528.22. The negative payment records each deposit as money contributed, and the future value is positive. This example assumes the rate remains constant and every deposit is made at the end of its month.
If the same $100 deposits are made at the beginning of each month, change the final argument to 1: =FV(B2/12,B3*12,-B4,0,1). It returns approximately $15,592.92. Beginning-of-month deposits earn interest for one extra month compared with end-of-month deposits, so the result is higher. In a real account, the institution’s deposit and interest-crediting rules determine the actual timing.
Match the Rate to the Compounding Period
Rate and period must use matching units. For annual compounding, a 5% annual rate and ten years can be entered as 5% and 10. For monthly compounding under a nominal annual rate of 5%, use 5% divided by 12 and ten times 12 periods. A nominal annual rate is not automatically the same as an effective annual rate: the effective rate includes the effect of compounding during the year.
If a quoted rate is effective annually but the model uses monthly periods, convert it to a monthly equivalent instead of simply dividing it by 12. With the effective annual rate in B2, the monthly rate is calculated as =(1+B2)^(1/12)-1. This conversion makes twelve monthly periods equivalent to the stated effective annual rate. Confirm whether a real product quotes a nominal or effective rate before using it.
Also distinguish a percentage from a whole number. A cell containing 5% is stored as 0.05, while entering 5 means 500%. If a result is dramatically larger than expected, check both the rate’s units and the number of periods before changing other inputs.
Enter and Check the Formula
In Microsoft Excel for Microsoft 365 on Windows, select a result cell, type the formula beginning with an equals sign, and press Enter. You can also select the Insert Function button beside the formula bar and search for FV. The formula works the same way in Excel for Mac and Excel for the web; the function name and argument order do not change. Some regional settings use semicolons rather than commas as separators, but the examples here use standard comma-separated Excel syntax.
For a transparent worksheet, keep assumptions in separate cells and label them clearly. Format rates as percentages and results as currency, while keeping the underlying inputs numeric. A result cell may show cents even though the model uses full precision; number formatting changes how a value appears, not the calculation itself. Avoid rounding intermediate rates or balances unless the real-world terms require it.
Check a formula by calculating a simple case independently. For a lump sum at zero interest, the future value should equal the starting amount in magnitude. For regular payments at zero interest, the future value should equal the total payments. If your result does not behave that way, inspect the sign of the cash flows, the period count, and whether the payment happens at the beginning or end of a period.
Common FV Errors and Misconceptions
- Using the wrong sign: Excel treats money paid in and money received as opposite cash-flow directions. To show a positive accumulated balance, enter the contribution or starting investment as negative.
- Mixing time units: A monthly payment requires a monthly rate and a count of months, not a yearly rate and a count of months.
- Confusing payment timing: The optional type argument is 0 for end-of-period payments and 1 for beginning-of-period payments. It does not select a compounding frequency.
- Assuming FV predicts actual returns: The result follows the fixed inputs. Variable interest, skipped contributions, and fees change what happens outside the model.
- Expecting inflation-adjusted dollars: FV returns a nominal amount based on the entered rate. It does not tell you what that amount will buy in the future.
FV is designed for a constant rate and regular, equal payments. If deposits vary by date or amount, a single FV formula may not represent the cash flows accurately; model those dated transactions individually or use a method suited to irregular cash flows. Likewise, do not treat a savings projection as a loan payoff schedule unless the rate, payment timing, and payment signs correctly represent the loan.
When to Use FV Instead of Another Function
Choose FV when the question is, what will this balance or payment stream be worth later? Use PV when the question runs in the other direction: what is a future amount or payment stream worth today? Both functions use periodic assumptions and cash-flow signs, but solve for different points in time.
For a simple fixed-rate savings plan, FV is usually more direct than manually building a long sequence of monthly balance formulas. A period-by-period schedule is more useful when the rate changes, deposits are irregular, or you need to inspect each month’s interest and balance. A schedule can also make the assumptions easier to audit, though it requires more rows and careful formulas.
For loans, FV can calculate a projected balance when payments and rates are represented consistently, but it is not a substitute for the lender’s official amortization schedule. Loan fees, payment dates, rate resets, and rounding conventions can affect the contractual balance. For any actual borrowing or savings product, check its terms with the lender or a qualified professional. These examples explain spreadsheet mechanics, not financial, tax, investment, or legal advice.
Frequently Asked Questions
How do I calculate compound interest in Excel with FV?
Use =FV(rate,nper,pmt,[pv],[type]). For one initial amount with no later deposits, set pmt to zero and enter the initial amount as a negative pv to display a positive future value.
Why does Excel FV return a negative number?
FV follows cash-flow signs. If the starting amount or payments are entered as positive values, Excel may return the future value as negative. Reverse the signs of the amounts paid in, while keeping the rate and timing unchanged, if you want the accumulated value displayed as positive.
Should I divide the annual interest rate by 12?
Divide a nominal annual rate by 12 when modeling monthly compounding under that quoted-rate convention, and use 12 periods per year. If the rate is an effective annual rate, convert it to an equivalent monthly rate instead; check the rate definition provided by the account or lender.
What does type 1 mean in the FV formula?
Type 1 means each regular payment is made at the beginning of a period. Type 0, or an omitted type argument, means it is made at the end. Beginning payments generally accumulate more because each earns interest for an additional period.
Does Excel FV include inflation, fees, or taxes?
No. FV calculates from the rate, period count, payments, starting balance, and payment timing supplied. It does not adjust for inflation or automatically subtract account fees or taxes, so those effects need separate assumptions if they are relevant to a projection.
Related
- Excel: Sum Filtered Rows with SUBTOTAL
- Excel Sum Duplicates: How to Total Repeated Values
- Excel: Sum a Filtered Column with SUBTOTAL
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel WORKDAY Function Examples for Real DeadlinesExcel’s WORKDAY function calculates a date a specified number of working days before or after a starting date,…
- Excel XLOOKUP Formula Examples for Everyday LookupsExcel’s XLOOKUP function finds a value in one range and returns a related value from another. For example,…
- Structured References in Excel Formulas: A Practical GuideStructured references are Excel table references that use a table’s name and column headings instead of ordinary cell…
- Excel FLOOR Function: How to Round Decimal Values DownTo round a decimal down in Excel, use FLOOR.MATH with the increment you want. With a value of…
- Excel Formula Bar Disappeared? How to Show It AgainIf the Excel formula bar disappeared, turn it back on from View > Show > Formula Bar in…
- Do Excel Merged Cells Need to Be the Same Size to Sort?Yes. When you sort a range in Excel, any merged cells included in that range must have the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.