How to use the FV formula in Google Sheets

If you’re looking to calculate the future value of your investments, then the FV formula in Google Sheets is the function you need.

The FV formula can calculate the future value of your investments for periodic payments with fixed interest rates. For example, if you add a monthly figure to your business savings account, you can forecast your total value at a later date by using the FV formula, which adds the interest earned in each period. To work out that growth by hand instead, use the compound interest formula in Google Sheets.

The FV formula does the whole calculation in one cell, so you don’t have to work out the interest period by period. Let’s explore in more detail what the FV formula is and how you can use it effectively in Google Sheets.

What is the FV formula in Google Sheets?

The FV formula stands for ‘Future Value’. It’s usually used to calculate the future value of an annuity investment, a series of equal payments at a fixed interest rate. FV adds each period’s interest to the balance, so that interest earns interest too.

In order to use the FV formula, you need to have periodic payments (these can be annual, quarterly, weekly, etc.), and a fixed interest rate. You can also use the FV formula to calculate the future value of a lump sum.

Let’s take a look at the syntax of the FV formula and its parameters.

Syntax

The FV formula uses the following syntax to calculate your future value:

• rate: the fixed amount of interest over time.
  Interest rates are always annual. If you want to calculate the interest rate for more frequent payments (quarterly or monthly), make sure to divide the rate accordingly, i.e. for a monthly interest rate, divide by 12.
• number_of_periods: the number of periodic payments that you are going to make. Multiply the payments per year (52 for weekly, 12 for monthly, 4 for quarterly) by the number of years: monthly payments over 3 years make 12*3 = 36 periods.
• payment_amount: the constant amount of money that you pay for each period.
• [present_value]: [OPTIONAL – 0 by default] the current value of the investment.
• [end_or_beginning]: [OPTIONAL – 0 by default] a value of 0 indicates that you are making the payments at the end of each period. A value of 1 specifies that we are making payments at the beginning of each payment period.

The important thing is to work with the same units for the interest rate and the number of periods. For example, for a 2-year investment with an annual interest rate of 5% and monthly payments, you would divide the interest rate by 12 (5%/12) and use 24 periods (12*2).

How to use the FV formula in Google Sheets

Now that you understand what the FV formula is and how it works, let’s go ahead and apply it to some real-life examples.

1. Use the FV formula to calculate the future value of an investment

Let’s say I want to add $5,000 at the beginning of every month into my business savings account over the course of a year. The annual interest rate of my account is 3%.

The screenshot below shows the values of each aspect that we need to substitute into the FV formula.

Please note: As I am paying monthly, the annual interest rate needs to be divided by 12. As I am paying monthly over one year, the number of payments is 12.

To calculate the future value of my total investment after a year, I’d input the following formula:

2. Use the FV formula to calculate the future value of a lump-sum payment

You can also calculate the future value of a one-time investment payment over a period of time.

Let’s say I have inherited $120,000 that I want to keep in a savings account over the next 10 years. The annual interest rate is 1%.

To calculate the future value of this lump-sum, I’d input the following formula:

As you can see, I will end up with $132,554.66 at the end of 10 years!

3. Use the FV formula to calculate loan payback

You can also use the FV formula to calculate whether you’ll be able to pay off a loan in a certain amount of time. You can do this by calculating whether an investment of a certain amount over a certain period of time would leave you with a positive balance. If the value is positive, you will successfully pay back the loan in time. If the final value is negative, you will need to increase the value of your payments or the number of payment periods.

Let’s say I need to pay back a business loan of $100,000. I want to pay it back over 3 years, paying back $9,000 at the end of each quarter. The annual interest rate of the loan is 5%.

To calculate whether I can pay off the loan in 3 years, I’d input the following formula:

Build an investment calculator with the FV function

You can type the values straight into the FV formula, but a small table works better when you want to compare scenarios. Each argument gets its own cell, and the future value updates as soon as you change one of them.

Set up a table to build a calculator

When you type =FV( in an empty cell, Sheets shows a help box with the arguments in the order the formula expects them. Use that order for the table: the argument names go in A1 to A5, Future Value goes in A6, and the values go in column B.

The formula goes in B6 and reads each argument from its cell, so you can change any value without touching the formula. The example below fills in the table for a monthly investment.

Calculate the future value of a monthly investment

Imagine you’re thinking about making monthly $300 payments into a savings account with an annual interest rate of 3%, over the next 2 years.

1. Since the interest rate is yearly, but I’ll be paying monthly, I need to divide the rate by 12: 3% / 12 = 0.25%.

2. The investment is over 2 years, and payments are monthly, so the number of periods is 24.

3. The amount should be negative, as this is money I will be paying out.

4. The present value is “0”, and payments are made at the beginning of each month (“1”).

5. In B6, enter the formula with cell references, =FV(B1,B2,B3,B4,B5), and press “Enter”.

6. That’s it. After 2 years, my monthly investment is worth $7,429.37.

Now change one input. Set B5 to 0, for payments at the end of each month, and the future value drops to $7,410.85: each payment earns one month less interest. To work backward from a target amount to the monthly payment you’d need, use the PMT formula.

FV function in Google Sheets

And there you have it! The FV formula is the most accurate and efficient function to use when calculating the future value of any investment or to help calculate whether you can pay back a loan. It’s important to remember that, in order for the FV formula to work, you must have payments that are at even intervals. Furthermore, you must also adapt the interest rate to the payment frequency.

If you’re interested in using more finance-related formulas in Google Sheets, why not take a look at How to use the IRR formula in Google Sheets? Two related formulas solve for the other parts of the same calculation: the PV formula works out what a future amount is worth today, and the RATE formula finds the interest rate behind a series of payments.