How to Calculate Future Value in Excel with Different Payments

The future value is a fundamental metric in economic planning that defines the future value of a present asset. Typically, the FV is calculated using an expected growth rate. When money is deposited in a savings account with a fixed interest rate, calculating the future value is simple. Usually, the FV function is used to calculate the future value in Excel. In this article, we will see 5 ideal examples to calculate the future value in Excel with different payments.

Introduction to Excel FV Function

The FV function computes the future value of an investment. That means FV calculates how much a present amount of money will be worth at a future date.

Syntax

FV(rate, nper, pmt, [pv], [type])

Arguments

rate (Required): The interest rate per period.

nper (Required): The total number of payment periods in a lifetime annuity.

pmt (Optional): The fixed amount paid each period. We should write it as a negative number. if it is not specified then it is assumed to be 0, and the pv argument must be provided.

[pv] (Optional): The investment’s present value. We should use a negative number to represent it. If it is not specified then it defaults to 0, and the pmt argument must be specified.

[type] (Optional): The digits 0 or 1 indicate when payments are due. If the type is not specified, it is considered to be 0.

How to Calculate Future Value in Excel with Different Payments: 5 Ideal Examples

Now we will see 5 beautiful examples with explanations to calculate future values in Excel using the FV function. Here, we have used 5 types of payments which need calculation of future value. So without further delay, let’s get started.

1. Calculate Future Value of Periodic Payments in Excel

Amounts paid at regular intervals (such as weekly, monthly, or yearly) for a period of more than one year are referred to as periodic payments. When we invest money over a sequence of regular savings, usually we are given an annual interest rate and an investment term which is defined in years. But we need to make the payments weekly, monthly, quarterly, or semiannually.

For this, we have to divide the annual rate by the number of periods per year to convert it to a periodic rate. Again, we need to multiply the term in years by the number of periods per year to get the total number of periods. Now, let’s assume, we have a dataset (B4:C9) in Excel. Here, we can see that the investment is $200 per month for 4 years at a 5% annual interest rate. We can calculate the future value of this periodic payment by following the steps below.

Steps:

• First, select the cell (C9) where you want to keep the future value.
• Secondly, to calculate the future value of the given data type the formula:

Here, C5 refers to the Annual interest rate, C8 refers to the Periods per year, C6 denotes No. of years and C7 represents the Periodic payment respectively.

• Finally, press Enter to get the desired Future Value.

Read More: How to Apply Future Value of an Annuity Formula in Excel

2. Count Future Value of Single Payment

If we choose to invest money in a single payment then the future value of the investment will depend on the present value (pv) instead of the periodic payment (pmt). Assuming, we have a dataset in Excel (B4:C8) where the Annual interest rate, No. of years and Investment of a single payment is given. Now, we need to calculate the Future Value of the payment.

Steps:

• In the beginning, we need to select the cell (C8) where we want to keep the Future Value.
• Next, to calculate the future value of the given single payment, type the formula:

Here, cells C5, C6 and C7 denote the values of Annual interest rate, No. of years and Investment respectively.

• After pressing Enter, we will be able to see the Future Value of the single payment.

Read More: How to Calculate Present Value of Future Cash Flows in Excel

3. Irregular Cash Flow Future Value Calculation in Excel

Suppose, we have a dataset (B4:D12) in Excel where we can see some Periods, a Required Return and some irregular Cash Flows. Let’s say, we need to calculate the Future Value of these uneven cash flows rather than the present value. To do so, at first we will calculate the future value of the cash flows individually. After that, we will sum up the individual cash flows to get the Total Future Value. The steps are below:

Steps:

• In the first place, select cell D8.
• Now, to gain the future value of the cash flow in cell C8 type the formula:

Here, C4 and B11 denote the Required Return and the final Period whereas B8 and C8 represent the specific Period and Cash Flow value. In this formula, we have used the dollar sign ($) to lock the respective cells. So it will not shift when you copy the formula.

• When we press Enter, we will get the future value of the respective cash flow.
• Similarly, to get all the future values of the cash flows, we need to drag the cursor (+ sign) until we reach the final cash flow.

• In turn, we have got all the future values of the individual cash flows.

• Now, it’s time to sum up the values of the individual cash flows to acquire the total future value. For doing this, we are going to use the SUM function. Here, type the formula in cell D12:

Here, D8:D11 represents the individual future values of the cash flows.

• In the end, press Enter and get the total future value.

Read More: How to Calculate Future Value of Uneven Cash Flows in Excel

4. Get Future Value for Various Compounding Periods

Let’s assume, we have a dataset (B4:D9) in Excel that contains various compounding periods. There is also a table that contains the Annual Interest Rate, No. of years and amount of Investment. Now, to calculate the future value of the compounding periods we need to follow the steps below.

Steps:

• Firstly, select cell D5.
• After that, to get the weekly compounded future value type the formula:

In this formula, we have used dollar signs ($) for the C11, C12 and C13 cells because we need to keep them unchanged after copying the formula for the cells below.

• Then, to get the future value, press the Enter key.

• In the same manner, we will be able to calculate the future value for the other compounding periods by dragging the cursor (+ sign placed at the bottom right corner of the cell) to the cell of the final compounding period.

• Hence, we have calculated the future values of all types of compounding periods.

Read More: How to Calculate Future Value of Growing Annuity in Excel

5. Create Future Value Calculator for Both Periodic & Single Payment

If we wish to create an FV calculator that can handle both periodic and single payments, we’ll need to use the Excel FV function in its entirety. To begin, just like the screenshot below, assign cells for all arguments, along with the optional ones.

Then, define the arguments in this manner:

Now, for calculating the desired future value, we need to combine the arguments.

• Next, we need to select cell C11 and type the formula:

• Lastly, by pressing the Enter key, the desired future value will be visible the same as in the picture below.

Read More: How to Calculate Future Value with Inflation in Excel

Download Practice Workbook

Download the practice workbook from here.

Conclusion

I hope the above methods will be helpful for you to calculate future value in excel with different payments. Download the practice workbook and give it a try. Let us know your feedback in the comment section.

Related Articles

• How to Calculate Present Value of Uneven Cash Flows in Excel
• How to Calculate Present Value of Lump Sum in Excel
• How to Make a Time Value of Money Calculator in Excel
• Calculate NPV for Monthly Cash Flows with Formula in Excel
• How to Apply Present Value of Annuity Formula in Excel
• How to Calculate Present Value in Excel with Different Payments

<< Go Back to Time Value Of Money In Excel | Excel for Finance | Learn Excel