Don’t forget to share this post

Whether you’re thinking about investing some of your savings or taking out a loan, you need to know the future value of those earnings or that debt. Google's Future Value function can help you with this decision. The formula calculates the future value by taking into account a number of factors, including the fixed interest rate, the frequency with which interest is compounded, and the number and the number of payments.

In this article, you will learn about Google's Future Value function and how to use it for different purposes. First, you will learn to use the formula to calculate the final amount of an annuity investment with periodic payments and a fixed interest rate. Second, you will learn to use it to get approximate results for different compounding frequencies. Finally, you will learn to use it to determine whether you’ll be able to pay back a loan in a specific amount of time.

What Is FV in Google Sheets?

Sheets’ FV formula can be used to calculate the future value of an annuity investment, as long as there are periodic payments and a fixed interest rate. However, it’s possible to tweak it for different payment frequencies. With the FV formula, you can calculate the future value of a lump-sum or monthly investment or quickly work out whether you can pay back a loan in time.

Before you learn how to use it and examples of how to tweak it for different purposes, let’s see how it’s structured.

How Do You Calculate FV?

Google’s Future Value formula has five parameters, three of which are required.

=FV(rate*, number_of_periods*, payment_amount*, [present_value], [end_or_beginning])
  • rate is a required parameter that defines the annual interest rate.
  • number_of_periods is also a required parameter that defines the number of payments to be made. If the payments are monthly or quarterly, you’ll have to divide the interest rate by 12 or 4, respectively.
  • payment_amount refers to the amount per period to be paid.
  • present_value is optional and represents the current value of the annuity. The default value is 0.
  • end_or_beginning is optional and indicates whether payments are due at the end (0) or at the beginning (1) of the period. The default value is 0.

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 interest compounded monthly, you would have to divide the interest rate by 12, and the number of payments would be 24.

How Do You Use FV in Google Sheets?

You can use the FV formula by typing in the parameters directly. This is how you can quickly set up a table to build a simple calculator.

Set up a table to build a calculator

Setting up a simple calculator in Google Sheets is very easy and will only take one step.

  1. 1. In an empty cell, type in “=FV(“ to see Sheets’ help textbox, showing you the order in which to input the variables.
How to Use the Future Value FV Formula in Google Sheets Set Up Calculator
How to Use the Future Value (FV) Formula in Google Sheets - Set Up Calculator

Alternatively, you can set up a table to build a simple calculator using cell references. Now, let’s see how to use it to calculate future values in different conditions.

Calculate the Value of a Lump-Sum Investment

Let’s say I’ve decided to invest a lump sum of $50 000 in a savings account over the next 15 years, with an annual interest rate of 2%.

  1. 1. Type the interest rate into the corresponding cell. Since interest is compounded annually, there’s no need to adjust the rate.
How to Use the Future Value FV Formula in Google Sheets Add Interest Rate
How to Use the Future Value (FV) Formula in Google Sheets - Add Interest Rate
  1. 2. Type in the number of periods. In this example, we have 15 payments in total - one per year.
How to Use the Future Value FV Formula in Google Sheets Add Number of Periods
How to Use the Future Value (FV) Formula in Google Sheets - Add Number of Periods
  1. 3. The payment amount is “0”, as it is a lump sum payment.
How to Use the Future Value FV Formula in Google Sheets Add Payment Amount
How to Use the Future Value (FV) Formula in Google Sheets - Add Payment Amount
  1. 4. The present value is negative, as we are initially paying out that amount.
How to Use the Future Value FV Formula in Google Sheets Add Present Value
How to Use the Future Value (FV) Formula in Google Sheets - Add Present Value
  1. 5. Interest is compounded at the end of each period, so I have used a value of “0”, but I could also skip that argument.
How to Use the Future Value FV Formula in Google Sheets End or Beginning
How to Use the Future Value (FV) Formula in Google Sheets - End or Beginning
  1. 6. Type the formula into the corresponding cell. The help textbox will guide you through the order in which to add the parameters. Remember to close the parenthesis and press “Enter”.
How to Use the Future Value FV Formula in Google Sheets Add Future Value Formula
How to Use the Future Value (FV) Formula in Google Sheets - Add Future Value Formula
  1. 7. That’s it! You have the future value of the investment.
How to Use the Future Value FV Formula in Google Sheets Future Value of Lump sum Investment
How to Use the Future Value (FV) Formula in Google Sheets - Future Value of Lump-sum Investment

Calculate the Value of a Monthly Investment

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

  1. 1. Since the interest rate is yearly, but I’ll be paying monthly, I need to divide the rate by 12.
How to Use the Future Value FV Formula in Google Sheets Divide Rate
How to Use the Future Value (FV) Formula in Google Sheets - Divide Rate
  1. 2. The investment is over 2 years, and payments are monthly, so the number of periods is 24.
How to Use the Future Value FV Formula in Google Sheets Number of Monthly Payments
How to Use the Future Value (FV) Formula in Google Sheets - Number of Monthly Payments
  1. 3. The amount should be negative, as this is money I will be paying out.
How to Use the Future Value FV Formula in Google Sheets Negative Payment Amount
How to Use the Future Value (FV) Formula in Google Sheets - Negative Payment Amount
  1. 4. The present value is “0”, and payments are made at the beginning of each month (“1”).
How to Use the Future Value FV Formula in Google Sheets Present Value of Monthly Investment
How to Use the Future Value (FV) Formula in Google Sheets - Present Value of Monthly Investment
  1. 5. Once the formula has all the parameters, close the parenthesis and press “Enter”.
How to Use the Future Value FV Formula in Google Sheets Add Cell References
How to Use the Future Value (FV) Formula in Google Sheets - Add Cell References
  1. 6. That’s it. I have the future value of my monthly investment.
How to Use the Future Value FV Formula in Google Sheets Future Value of Monthly Investment
How to Use the Future Value (FV) Formula in Google Sheets - Future Value of Monthly Investment

Calculate Loan Payback

The Future Value formula can also be used to determine whether you’ll be able to pay back a loan within a given time period. Let’s say I need to take out a loan of $50 000 at a 5% interest rate. I want to know if I can pay it back in 4 years by making monthly payments of $1200. 

  1. 1. The annual interest rate is 5%, but I will be paying monthly, so I need to divide by 12.
How to Use the Future Value FV Formula in Google Sheets Loan Interest
How to Use the Future Value (FV) Formula in Google Sheets - Loan Interest
  1. 2. The number of periods is 48, as I will pay monthly over 4 years.
How to Use the Future Value FV Formula in Google Sheets Loan Payment Periods
How to Use the Future Value (FV) Formula in Google Sheets - Loan Payment Periods
  1. 3. The payment amount needs to be negative because it is money I will be paying out.
How to Use the Future Value FV Formula in Google Sheets Loan Payment Amount
How to Use the Future Value (FV) Formula in Google Sheets - Loan Payment Amount
  1. 4. The present value is positive because it is a loan, and payments are made at the end of each period.
How to Use the Future Value FV Formula in Google Sheets Loan Present Value
How to Use the Future Value (FV) Formula in Google Sheets - Loan Present Value
  1. 5. In the formula, select the cells containing the parameters. Remember to close the parenthesis and press “Enter”.
How to Use the Future Value FV Formula in Google Sheets Loan Formula
How to Use the Future Value (FV) Formula in Google Sheets - Loan Formula
  1. 6. That’s it. Since the value is positive, it means I can pay back my loan in 4 years.
How to Use the Future Value FV Formula in Google Sheets Loan Payback
How to Use the Future Value (FV) Formula in Google Sheets - Loan Payback

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

As you have seen, Google’s FV formula can be used in a variety of ways with a few simple tweaks. Whether you want to calculate the future value of an investment or pay back a loan within a specified time, Google Sheets’ FV formula can help. You can set up a simple calculator to play with different values for the variables to help you make a decision.

You now know how to use Google Sheets’ Future Value function to calculate the future value of a lump-sum investment and a monthly investment, as well as how to calculate whether you can pay back a loan in time.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Sep 1 2022, Updated Sep 23 2022