MS Excel: FVSCHEDULE function to calculate future value

The FVSCHEDULE function in Excel calculates the future value of an investment or loan after applying a series of interest rates over time. Unlike the standard FV function, which uses a constant interest rate, the FVSCHEDULE function allows for varying interest rates during different periods, making it ideal for complex financial models.

This function is particularly useful when working with variable-rate loans, investments with changing interest rates, or any situation where interest rates fluctuate over time.

Syntax

FVSCHEDULE(principal, schedule)

Argument	Description
principal	The initial amount of money invested or loaned. This is the starting value of the investment or loan.
schedule	A range of cells or an array that contains the interest rates applied at each period. These interest rates are expressed as decimals (e.g., 10% is 0.1).

Key Points

• The FVSCHEDULE function applies a series of interest rates over successive periods to the principal amount, compounding the principal at each step.

• schedule must be an array or a range of cells with the interest rates for each period.

• The function is useful in situations where the interest rate varies over time, such as variable-rate loans or investments with changing interest rates.

Practical Example

Scenario 1: Variable Interest Rate Investment

You invest $1,000, and the interest rates vary over 3 years:

• Year 1: 5%

• Year 2: 6%

• Year 3: 7%

You want to calculate the future value of this investment after these rates have been applied.

Formula:

=FVSCHEDULE(1000, {0.05, 0.06, 0.07})

Explanation:

Here is how the principal and the interest rates are applied over the three years.

Excel Table:

Year	Interest Rate	Value (Principal after each year)
Year 1	5% (0.05)	$1,000 × (1 + 0.05) = $1,050
Year 2	6% (0.06)	$1,050 × (1 + 0.06) = $1,113
Year 3	7% (0.07)	$1,113 × (1 + 0.07) = $1,191.15

In Excel, this is calculated using the FVSCHEDULE function:

=FVSCHEDULE(1000, {0.05, 0.06, 0.07})

Parameter	Value
Principal (initial investment)	$1,000
Year 1 Interest Rate	5% (0.05)
Year 2 Interest Rate	6% (0.06)
Year 3 Interest Rate	7% (0.07)
Future Value	$1,191.15

Explanation:After applying the interest rates for each year, the future value of the investment is $1,191.15.

Scenario 2: Investment with Different Rates Over Four Years

You invest $5,000 with the following interest rates:

• Year 1: 3%

• Year 2: 4%

• Year 3: 5%

• Year 4: 6%

Formula:

=FVSCHEDULE(5000, {0.03, 0.04, 0.05, 0.06})

Explanation:

Here is how the principal and the interest rates are applied over the four years.

Excel Table:

Year	Interest Rate	Value (Principal after each year)
Year 1	3% (0.03)	$5,000 × (1 + 0.03) = $5,150
Year 2	4% (0.04)	$5,150 × (1 + 0.04) = $5,356
Year 3	5% (0.05)	$5,356 × (1 + 0.05) = $5,623
Year 4	6% (0.06)	$5,623 × (1 + 0.06) = $5,958.48

In Excel, this is calculated using the FVSCHEDULE function:

=FVSCHEDULE(5000, {0.03, 0.04, 0.05, 0.06})

Parameter	Value
Principal (initial investment)	$5,000
Year 1 Interest Rate	3% (0.03)
Year 2 Interest Rate	4% (0.04)
Year 3 Interest Rate	5% (0.05)
Year 4 Interest Rate	6% (0.06)
Future Value	$5,958.48

Explanation:After applying the interest rates for each year, the future value of the investment is $5,958.48.

Example with Cell References

Suppose the interest rates are stored in cells A2 to A5:

• A2: 0.03 (3%)

• A3: 0.04 (4%)

• A4: 0.05 (5%)

• A5: 0.06 (6%)

The formula would be:

=FVSCHEDULE(5000, A2:A5)

Parameter	Value
Principal (initial investment)	$5,000
Year 1 Interest Rate	3% (0.03)
Year 2 Interest Rate	4% (0.04)
Year 3 Interest Rate	5% (0.05)
Year 4 Interest Rate	6% (0.06)
Future Value	$5,958.48

Summary

Item	Value
Initial Investment	$5,000
Year 1 Interest Rate	3% (0.03)
Year 2 Interest Rate	4% (0.04)
Year 3 Interest Rate	5% (0.05)
Year 4 Interest Rate	6% (0.06)
Future Value	$5,958.48

Important Notes

• schedule must be entered as a range or an array of interest rates for each period.

• The interest rates in schedule should be expressed as decimal values. For example, a 5% rate is entered as 0.05.

• The FVSCHEDULE function is ideal for scenarios with changing interest rates, such as variable-rate loans, savings accounts, or investments in bonds with step-up interest rates.

• The principal argument is the initial amount and can be negative if you are calculating the future value of a loan or investment that requires a payout.

When to Use FVSCHEDULE?

To calculate the future value of an investment or loan where interest rates change over time.

In scenarios with variable-rate loans where the interest rate fluctuates over different periods.

For investment analysis where different interest rates apply for different periods.

To model future value scenarios where the interest rate is not constant, such as bonds or step-up interest rate accounts.

Conclusion

The FVSCHEDULE function in Excel is a powerful tool for calculating the future value of an investment or loan when the interest rate changes over time. By applying a series of interest rates, this function provides an accurate way to forecast the growth of an investment or the amount owed on a loan. Whether you are working with variable-rate loans, savings accounts, or investments with changing rates, the FVSCHEDULE function allows for a flexible and precise calculation.