MS Excel: FV function for future value calculation

The FV (Future Value) function in Excel is one of the most widely used financial functions. It helps you calculate the future value of an investment or loan based on periodic, constant payments and a fixed interest rate. The function is crucial for financial planning, helping to determine how much an investment will grow over time or how much you will owe in the future.

The FV function is often used in savings plans, retirement funds, loan calculations, and other financial applications to predict future values of investments, taking into account regular payments, interest rates, and time periods.

Syntax

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

Argument	Description
rate	The interest rate per period. For example, if the annual interest rate is 6% and payments are made monthly, the rate would be 6%/12 (0.005).
nper	The total number of payment periods in the investment or loan. For instance, for a 10-year loan with monthly payments, nper would be 10*12 (120 periods).
pmt	The payment made each period. This value cannot change over the life of the investment or loan. For example, if you deposit $200 monthly, pmt would be -200 (negative because it's an outgoing payment).
pv (Optional)	The present value, or the initial investment or loan amount. If omitted, Excel assumes it to be 0. For a loan, this value would be entered as a negative number (e.g., -5000 for a $5,000 loan).
type (Optional)	A logical value that specifies when payments are due:

• 0 (default) means payments are due at the end of each period.

• 1 means payments are due at the beginning of each period. |

  
  

Key Points

• The FV function helps calculate how much an investment will grow (or how much will be owed) over time based on regular payments and a fixed interest rate.

• pmt is always a negative number (because it’s an outgoing payment).

• The FV function can be used for savings plans, loans, and annuities.

• pv (present value) is optional, and if you are starting from a zero balance, it can be left blank.

Practical Example

Scenario 1: Saving for Retirement

Suppose you plan to invest $500 every month for 20 years in an account that offers an annual interest rate of 6%, compounded monthly. You want to calculate the future value of your savings.

Formula:

=FV(6%/12, 20*12, -500)

Explanation:

• rate: The interest rate per period is 6%/12 or 0.005 because the interest is compounded monthly.

• nper: You are investing for 20 years with monthly payments, so nper is 20*12 = 240 periods.

• pmt: You are depositing $500 every month, so pmt is -500 (negative because it’s an outgoing payment).

Result: $218,733.70

Explanation:At the end of 20 years, your $500 monthly investment will grow to $218,733.70 due to the power of compounding at 6% annually, compounded monthly.

Scenario 2: Loan Repayment

Now, let’s calculate the future value of a loan that you take out. You borrow $10,000 for a car with an annual interest rate of 5%, to be paid back in monthly installments over 5 years. The monthly payment is $200.

Formula:

=FV(5%/12, 5*12, -200, 10000)

Explanation:

• rate: The interest rate per period is 5%/12 or 0.004167 because the payments are monthly.

• nper: The loan term is 5 years, so nper is 5*12 = 60 periods.

• pmt: The monthly payment is -200 (negative because it’s an outgoing payment).

• pv: The present value of the loan is 10000 (the initial loan amount).

Result: $0

Explanation:At the end of 5 years, after making monthly payments of $200, the loan balance will be $0, which means the loan is fully paid off.

Another Example: Investment with One-Time Initial Deposit

Suppose you make a one-time deposit of $5,000 into an account that earns 4% interest annually, compounded quarterly. You want to find out the future value after 10 years.

Formula:

=FV(4%/4, 10*4, 0, -5000)

Explanation:

• rate: The interest rate per period is 4%/4 or 0.01 because interest is compounded quarterly.

• nper: The investment period is 10 years with quarterly compounding, so nper is 10*4 = 40 periods.

• pmt: There are no additional payments being made, so pmt is 0.

• pv: The present value (initial deposit) is -5000 (negative because it’s an outgoing payment).

Result: $7,359.60

Explanation:After 10 years, your $5,000 initial deposit will grow to $7,359.60 due to the interest earned at a rate of 4%, compounded quarterly.

Summary

Item	Value
Monthly Payment	$500
Interest Rate	6% annually
Compounding Periods	Monthly (12 times/year)
Investment Period	20 years
Future Value	$218,733.70

Important Notes

• FV is a powerful function for calculating how much an investment will grow over time, taking into account regular contributions and interest.

• Always use a negative value for pmt and pv when payments are being made out of pocket (i.e., outgoing cash flows).

• The FV function can be applied to a variety of situations including retirement planning, loan amortization, and investment forecasting.

• nper is the total number of payment periods. If you are calculating yearly payments, nper will be the number of years; if calculating monthly payments, nper will be the number of months.

  
  

When to Use FV?

• For calculating the future value of an investment or loan based on regular contributions or payments.

• In savings plans and retirement accounts to forecast how your investments will grow over time.

• For calculating how much you will owe or have accumulated at the end of a loan period or investment term.

• In financial planning for both personal and business purposes to predict future growth.

Conclusion

The FV function in Excel is an essential tool for anyone involved in financial planning, whether for savings, investment, or loan repayment. By considering regular payments and compounding interest, it provides a clear picture of how your money will grow or how much you will owe in the future.