Excel function for compound interest: FV explained
A practical, Excel-focused guide to using the FV function for compound interest calculations, covering lump-sum versus periodic contributions, sign conventions, and real-world worksheet design.
The Excel function for compound interest is FV, which computes the future value given rate, periods, payment, and present value. For a single lump-sum investment, use =FV(rate, nper, 0, -PV). You can also model periodic contributions with the same function by setting pmt to a nonzero value and adjusting PV accordingly. This approach works in any standard Excel version.
Understanding FV and compound interest in Excel
Compound interest can be modeled in Excel using the FV function, which returns the future value of an investment given a periodic rate, number of periods, periodic payment, and present value. FV assumes contributions occur at the end of each period by default, though the function can be adapted for beginning-of-period payments. This section shows how to set up a straightforward lump-sum scenario and then extend it to include regular contributions. The goal is to provide a reusable mental model you can slot into any retirement, savings, or loan calculator sheet.
=FV(0.05, 10, 0, -1000)This formula computes the value after 10 periods with a 5% per-period rate and an initial investment of 1000. The cash outflow is represented by a negative PV to align the sign convention with Excel’s cash flow rules. For a scenario with no periodic payments, FV effectively grows the PV by the rate over nper periods.
=FV(0.07, 20, 0, -5000)- Parameters: rate, nper, pmt, pv. - pmt defaults to 0 for lump-sum cases. - PV should be negative if it’s an amount you invest (cash outflow).
Lump-sum vs. periodic contributions
FV can also incorporate regular contributions by specifying a nonzero pmt argument. This is where compound interest modeling gets closer to real life, since savers usually contribute monthly or annually. The sign of pmt indicates a payment into the account (negative). You’ll typically also supply a present value (pv) that reflects the current balance.
=FV(0.06, 12, -250, -10000)This example assumes a monthly contribution of 250 over 12 periods (one year) with a starting balance of 10,000. The result is the account value after the periods, accounting for both growth and contributions. If you instead want to model end-of-period contributions with a monthly rate, adjust rate to rate_per_period = annual_rate/12 and nper = years*12.
=FV(0.06/12, 12*5, -150, -20000)- Note how the signs ensure positive future value while payments are negative cash flows. - Alternative: if you want contributions at the beginning of each period, set the type argument via PMT handling inside your model by adjusting pmt sign and using additional calculations.
Steps
Estimated time: 15-25 minutes
- 1
Define inputs
Decide the rate, number of periods, initial present value, and any periodic payment. Use a single lump-sum scenario first to validate basics, then add cash flows. Ensure signs reflect cash inflows vs. outflows.
Tip: Use negative PV for money you invest (outflow) to keep FV positive. - 2
Write the FV formula
Enter the FV function with your chosen inputs. Start simple with pmt = 0 for lump-sum growth, then add pmt to model regular contributions.
Tip: Keep rate as decimal (e.g., 0.05) unless you’re using a percent-syntax accepted by Excel. - 3
Test end-of-period contributions
Modify pmt and pv to reflect ongoing contributions and verify that the sign conventions remain consistent. Compare results to manual calculations.
Tip: If your result seems off, check whether pmt should be negative and pv should be negative for consistency. - 4
Experiment with compounding frequency
Adapt rate and nper to match different compounding schedules (monthly, quarterly, etc.). This is the key to accurate compound-interest modeling.
Tip: Always recalculate rate and nper when changing the frequency to avoid misinterpretation. - 5
Validate with an alternative formula
Cross-check FV results using the closed-form formula A = P(1+r)^n for single-contribution scenarios to ensure consistency.
Tip: A quick sanity check helps catch sign or input mistakes.
Prerequisites
Required
- Excel 2016 or laterRequired
- Understanding of rate, nper, pv, and pmt conceptsRequired
- Basic formula editing and relative/absolute referencesRequired
Optional
- Optional sample workbook with a simple cash-flow sheetOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Copy formulaCopy selected cell containing FV or related formula | Ctrl+C |
People Also Ask
What is FV in Excel and what does it compute?
FV is the Future Value function. It computes the balance of an investment after a specified number of periods, given a periodic interest rate, optional periodic payments, and a present value. It’s commonly used to model savings growth and retirement funding.
FV calculates how much money you’ll have in the future based on rate, time, and payments.
How do I model monthly contributions with FV?
Set the rate to the per-period rate (for monthly contributions, annual rate divided by 12), set nper to total months, and use a negative PMT to reflect regular payments. PV should reflect your starting balance (negative if invested).
Use a negative PMT for monthly deposits and adjust rate and periods to match your schedule.
Why do I need a negative PV or PMT in FV formulas?
Excel treats cash inflows as positive and outflows as negative. To keep the final future value consistent with this convention, investors’ starting balances (PV) and periodic payments (PMT) are typically entered as negative values.
The sign conventions help Excel show cash flows clearly in the model.
Can FV handle different compounding frequencies beyond annual?
Yes. Use rate_per_period = annual_rate / m and nper = years * m, where m is the number of compounding periods per year. This yields accurate results for monthly, quarterly, or daily compounding.
Adjust the rate and periods to match how often interest compounds.
What are common mistakes when using FV for compound interest?
Common mistakes include wrong sign usage, mismatched rate and period calculations, and forgetting to adjust pmt when switching from lump-sum to continuous contributions. Always verify with a simple scenario first.
Double-check inputs and signs to avoid subtle mistakes.
Is FV appropriate for loan repayment schedules?
FV can model loan growth or payoff in a theoretical sense, but for standard loan amortization you typically use PMT with a negative PV and correct sign conventions, or use dedicated amortization functions.
FV is best for savings growth, not primary for amortized loans.
The Essentials
- Use FV for compound-interest growth with optional periodic contributions
- Keep PV negative for investments; PMT negative indicates payments
- Adjust rate and nper when changing compounding frequency
- Cross-check with the closed-form formula for simple cases
- Document all inputs for reproducible models