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.

Table of Contents

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.

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:

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:

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.

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

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.

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.

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.

$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️⃣

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.

• 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.

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.

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 💸

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

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

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

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

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.

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.

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.

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.

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.

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.

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)

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.

Step 6) Press Enter to see the future value.

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.

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:

• How to Calculate Net Present Value NPV in Excel (NPV Formula Explained)
• How to Calculate WACC in Excel (Step-by-Step)
• How to Use the IRR Function in Excel: Step-by-Step (2024)