How to Calculate Future Value in Excel (FV Function)

One of the key driving factors we all have in our head while making investments is – to what value would it grow after a given number of years?

This is what we call the future value of investment. Just like money loses its value over time (if not invested), invested money grows in value over time as it earns. It’s all about the time value of money.

Interestingly, you can compute the future value of your investments in Excel by using the FV function 🎯

Continue reading with me through this tutorial to learn how can you do it in Microsoft Excel.

Grab the workbook here so you can tag along, and let’s set the scene with an example below.

What is Future Value

Just as the name says, the future value is the value to which your money will grow in the future.

A quick example will make it clear 🏦

Assume I deposit $500 in my savings account which yields a 10% annual interest for 5 years.

To what value will this $500 grow by the end of year 5?

For this, I need to compound the investment of $500 at the rate of 10% annually for 5 years.

Future value of $500 deposit

Each year, a predefined interest rate of 10% would be charged on the deposit of $500. Since this interest will not be withdrawn and will be a part of the money deposited in the bank, it will also bear interest the next year.

This way the principal and the interest are compounded for 5 years to reach the value of $805.26 💰

And this you know on Day 1 – before you even deposit the $500. This is what we call FV.

The formula for calculating FV is:

Click to copy

Pro Tip!

The formula of FV above is the formula for compounded FV. However, if you want to calculate simple FV where in each period, interest is only charged on the principal value and not on the interest itself, use the following formula.

FV = PV (1 + (interest rate number of periods))

How to calculate FV in Excel

All the calculations we did above in a long table, can be done in a split second in Excel using the FV function. Let’s learn how to do it in a variety of ways below.

For a single payment

Calculating the fair value for an investment that is a lump sum payment you make today is super simple.

Data remains the same as above. We make a single payment (the deposit of $500) today that will bear compound interest at the rate of 10% for the coming 5 years.

Step 1) Begin writing the FV function as below:

Click to copy
The FV function syntax

Pro Tip!

The FV function has the following arguments:

  • Rate: This is the constant interest rate per period. Can be annual rate, quarterly, monthly, or any rate. Just make sure the rate aligns with the period of compounding.
  • Nper: The total number of payment periods/interest compounding periods
  • Pmt: the constant payments to be made in each period. Should be supplied as a negative value.
  • [pv]: This is an optional argument. The present value of the investment. Should be supplied as a negative value. Becomes necessary when the pmt argument is omitted.
  • [type] This is an optional argument. Can be supplied as 0 and 1 where 0 (or omitted) means the payment is made at the end of each period and 1 means payments are made at the beginning of the period.

Step 2) For the rate argument, refer to Cell B3 which contains the interest rate.

Click to copy
fixed interest rate

Step 3) For the nper argument, refer to cell B4 which contains the number of periods for which the interest will compound.

Click to copy
The nper argument

Step 4) Next is the pmt argument that we will omit. This is because in this scenario we are not making any periodical payments. Only an initial investment on Day 1.

Click to copy
The Pmt argument

Step 5) Refer to Cell B2 for the PV argument. Since we have omitted the pmt argument, it is necessary to supply the present value (PV) argument now.

PV means the present value of the amount I am investing today which is $500.

Click to copy
The pv function argument

Note that the payment of $500 appears as a negative value in our dataset. I have written it with a negative sign since it is an outflow. For the FV function, ensure to write all payments (cash outflows) with a negative sign and all inflows with a positive sign.

Kasper Langmann, co-founder of Spreadsheeto

Step 6) We will leave the “type” argument omitted since there are no payments. Excel will set it to 0 by default.

Step 7) All set. Press Enter to have the future value of this investment calculated.

Results of the FV function

$805.26 – same as we calculated above.

Instead of making a full fledge table of interest compounded on your investment to calculate its future value – easily down the process to a single-line formula with the FV function 😎

For periodic payments

The above example explains a simple investment where you deposit money for once in the beginning (the present value), and there are no periodic payments.

Now, let’s put together an example, where you make do not make an initial deposit + periodic payments too.

Going with the same savings bank account above, I deposit $500 today and $100 each year for the coming 4 years 4️⃣

Periodic payments

However, are these payments made at the end of each year or beginning? This is a very important question to ask since it is not only going to change the arguments of the FV function but also the results.

Check the next sections out to learn why.

Payments made at period-end (accrual basis)

For this section, we are assuming that you:

  • Deposited $500 (present value) today (beginning of Year 1)
  • All other periodic payments are made at each successive year-end

To calculate the future value of this investment:

Step 1) Write the FV function as follows.

Click to copy
Excel FV function
  • Rate is referred (Cell B4) to as the first argument.
  • No of periods (Cell B5) referred to as the second argument.
  • Cell B3 is referred to as the pmt argument. Note that $100 is written as a negative number since it is a payment (cash outflow).
  • Cell B2 (with -$500) is referred to as the PV argument.
  • As the payments are made at each year’s end, the type argument must be set to 0 or omitted.

Step 2) Hit Enter to get the future value.

Future value of an investment

Quick and easy? Let me now show you the math that runs behind it.

Step 1) In the first year, the bank would give you 10% interest on your deposit of $500.

Click to copy
FV function for Periodic payments

Step 2) At the year’s end, you make the periodic payment of $100. However, since it was deposited at the end of the year (31 December 2023), interest will start accumulating on it from the next year (01 January 2023).

Step 3) The deposit + interest + the periodic payment becomes your closing balance 💸

FV function for Periodic payments

Step 4) Last year’s closing balance will be the next year’s opening balance.

Closing balance becomes opening

Step 5) Again 10% interest would be charged on the opening balance for this year and the cycle continues.

next year's cycle

Step 6) Fast-forward the same to 5 years to see the investment grow to what value.

Five years cycle rate of return

The investment grows to $1415.77 by the end of Year 5 (the period) – the same as the FV function computed above 📈

future value calculations

This explains the compounding that runs behind the FV function where payments are made at year-end.

Payments made at period-start (advance basis)

In this section, we will see how the FV function works when payments are made at period-start.

To do so, I will just pick a column from the table above and reposition it as follows.

Interest column after periodic payments

Under the scenario where periodic payments are made at the beginning of the year, interest is received on the opening balance + the periodic payment.

You deposited $500 in your account on 01 January 20X1 and also made a periodic payment of $100 on the same day. Both these amounts ($500 + $100) sit in your account for the whole year; hence interest accumulates on them both and not only on the opening balance.

Doing the same, the investment value grows to $1476.82 by the end of Year 5.

The same calculation can be done using the FV function, too. And that too in under 1 minute.

Step 1) Write the FV function as below.

Click to copy

Everything is the same as our FV function for the above section, except for the type argument “1”.

The type argument “1” tells Excel that the periodic payments are made at the start of the year and not at year-end.

The FV function computes the Future Value for this investment as below.

Future value formula

The FV function returns $1476.82. Precise and accurate as we calculated it manually.

This is how you can use the FV function to quickly calculate the future value for a variety of investments, quickly and accurately 🚀

Different compounding periods

To compute future value, two key inputs are the rate and the number of periods.

Most investments would offer an annual compounding rate – but for some of them, this might not hold.

Compounding means the process by which interest is credited to the existing amount of principal and interest. Depending upon the terms you agree for an investment, the compounding frequency could be annual, bi-annual, quarterly, monthly, or even weekly.

Kasper Langmann, co-founder of Spreadsheeto

It is very important to know the frequency of compounding for an investment since this is going to change the whole math and results 📆

Here’s an example.

We deposit $500 into a bank account that bears a 10% annual interest rate. However, the compounding period varies under different packages.

Different compounding packages

To calculate the future value for each of these packages:

Step 1) Begin writing the FV function.

Step 2) Refer to the rate of interest as the first argument and divide it by the number of periods.

Click to copy
Rate function divided by number of periods

Since the compounding under Package A occurs weekly, I have divided the rate by the number of weeks in a year 🧐

Step 3) Refer to the number of periods as the second argument (the product of the number of years and compounding periods per year)

Click to copy
Number of years and week product

The investment stays in the bank account for 5 years but, since compounding is done weekly, we will multiply the number of years with the number of weeks (52) in each year.

Step 4) No periodic payments so omit the pmt argument.

Step 5) Refer to the initial deposit of $500 as the pv.

Click to copy
The FV formula

Step 6) Press Enter to see the future value.

Excel formulas weekly compounding

If compounded weekly, the investment of $500 grows to $824 by the end of the period.

Step 7) Drag and drop the same formula to the whole list to see how the future value for each package varies only based on different compounding periods.

Future value function

Ranges from $805 to $824.

This is why it is important to know the compounding frequency of your investments and to account for it accurately.

Pro Tip!

To correctly account for the compounding period.

Step 1) See the rate of your interest. For example, is it 10% annual interest, bi-annual interest, monthly interest, or what?

Step 2) See the compounding frequency. The compounding frequency must match your interest rate interval. If it doesn’t, you need to adjust it as follows:

For example, if your investment offers a 10% annual interest rate compounded semi-annually, you must:

  • Divide your annual interest rate by 2 (because the rate is annual but compounded semi-annually)
  • Multiply your number of periods by 2 (because interest compounds twice during the year).

This is how you adjust the rate and nper if your interest rate interval and compounding frequency do not align.

Conclusion

The above tutorial discusses all the details of the FV function of Excel and how can you calculate future value in Excel using it.

Future value is a key financial planning metric of corporate finance that is used to evaluate investments and make key decisions.

The concept of future value makes a lot more sense when you study other related financial functions too. Read out my Excel tutorials on some of these including: