If you are planning for retirement, trying to figure out how much a savings account will grow over a decade, or analyzing the long-term cost of an investment, you don’t need to build a massive, 120-row manual calculation table.
Microsoft Excel has a dedicated financial formula called the FV (Future Value) function. It instantly calculates exactly how much money an investment will be worth in the future, based on a constant interest rate and a strict schedule of regular payments.
Understanding the Syntax
The FV function requires a few specific arguments to work correctly.
Syntax: =FV(rate, nper, pmt, [pv], [type])
- rate: The interest rate per period. (If the bank pays 5% annually, but compounds monthly, you must divide 5% by 12).
- nper: The total number of payment periods. (If you save for 10 years, and make monthly deposits, the NPER is 120).
- pmt: The amount of money you deposit each period. Because cash is leaving your wallet to go into the account, this must be a negative number.
- [pv] (Optional): The “Present Value.” How much money is already in the account right now. This must also be a negative number. If left blank, Excel assumes it is zero.
- [type] (Optional): A 0 or a 1. A 0 means you make your deposits at the end of the month. A 1 means you make deposits at the beginning of the month. If left blank, Excel assumes 0.
Example: Calculating a Savings Account
Let’s look at a realistic scenario. You just opened a new savings account. It has exactly $0 in it right now.
You plan to deposit $200 every single month for the next 5 years. The bank guarantees an annual interest rate of 4% (which is compounded monthly).
How much money will you have in the account at the end of the 5 years?
The Formula:
=FV(4%/12, 5*12, -200)
How the math breaks down:
- Rate (4%/12): Because you are making monthly deposits, the interest rate must be calculated monthly. We take the 4% annual rate and divide it by 12.
- NPER (5*12): You are saving for 5 years, but making 12 deposits a year. That equals 60 total periods.
- PMT (-200): You are losing $200 from your checking account every month to fund this investment, so it is negative.
When you hit Enter, Excel instantly calculates the compounding interest. Over 5 years, your $12,000 in raw deposits will grow to exactly $13,259.80.
Important Formatting Rules
The most common mistake people make when using the FV function (and all other Excel financial functions like PMT or PV) is forgetting the negative sign on the payment.
Excel views these formulas as a strict cash flow model. If you don’t put a minus sign in front of the pmt argument (e.g., you type 200 instead of -200), Excel thinks the bank is paying you $200 a month, and the final FV result will come out as a massive negative number.