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. In an empty cell, type in “=FV(“ to see Sheets’ help textbox, showing you the order in which to input the variables.
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. Type the interest rate into the corresponding cell. Since interest is compounded annually, there’s no need to adjust the rate.
- 2. Type in the number of periods. In this example, we have 15 payments in total - one per year.
- 3. The payment amount is “0”, as it is a lump sum payment.
- 4. The present value is negative, as we are initially paying out that amount.
- 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.
- 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”.
- 7. That’s it! You have the future value of the 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. Since the interest rate is yearly, but I’ll be paying monthly, I need to divide the rate by 12.
- 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. Once the formula has all the parameters, close the parenthesis and press “Enter”.
- 6. That’s it. I have the future value of my 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. The annual interest rate is 5%, but I will be paying monthly, so I need to divide by 12.
- 2. The number of periods is 48, as I will pay monthly over 4 years.
- 3. The payment amount needs to be negative because it is money I will be paying out.
- 4. The present value is positive because it is a loan, and payments are made at the end of each period.
- 5. In the formula, select the cells containing the parameters. Remember to close the parenthesis and press “Enter”.
- 6. That’s it. Since the value is positive, it means I can pay back my loan in 4 years.
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.