The FVSCHEDULE function in Google Sheets is useful if you want to find out the future value of an investment with varying interest rates.
Categorized under financial functions in Google Sheets, the FVSCHEDULE function returns the future value of the principal amount based on a series of compounding interest rates. Compounding rates are based on both the principal amount and the accumulated interest from the previous period.
Table of Contents
The rules for using FVSCHEDULE function in Google Sheets are as follows:
- Any non-numeric arguments will return #VALUE! error.
- Interest rates that are passed directly as arguments in the function should be enclosed with braces ‘{}’ to let the Google Sheets treat them as an array of values.
- The function accepts blank cells, but treats them 0.
- Interest rates should be in percentage format (i.e. 0.4 or 40%).
Let’s take an example.
Janine was looking for a company that she likes to invest her money in. She was able to find the XYZ company. The company provides a table with potential returns year on year for five years. Please take a look at the table below:
In 2021, the principal amount or the initial money she invested in the company is $10,000, while the varying rates year on year are 6%, 5.%, 7%, 8%, and 7.5%. This means that in 2026, her invested amount will become $13,919.
Using the FVSCHEDULE function in Google Sheets, Janine could project that she would earn $3,919 in 5 years, on top of her initial investment.
Let’s have another sample!
Let’s say the same company that Janine invested in provides another table with a rate schedule month on month. She wants to check how much she’d earn in 6 months. See the table below:
Janine utilized the FVSCHEDULE function in Google Sheets and it shows how much her principal amount could become in 6 months.
Watch out for a more advanced tutorial and examples on how you can use the FVSCHEDULE function in the coming weeks. Be sure to subscribe to be notified.
Awesome! Let’s begin getting to know more about our FVSCHEDULE function in Google Sheets.
The Anatomy of the FVSCHEDULE Function
So the syntax (the way we write) the FVSCHEDULE function is as follows:
=FVSCHEDULE(principal, schedule)
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do a computation or use a function.
- FVSCHEDULE() this is our FVSCHEDULE function. It will take arguments and compute for the future value of an investment.
- principal is the present value of an investment. It could be the exact value or reference to the amount.
- schedule is an array of values that provides the schedule of interest rates to be applied to the principal. If you type in directly the array of values, open and close braces ‘{}’ should enclose it.
A Real Example of Using FVSCHEDULE Function
Take a look at our investment evaluation example below to see how the FVSCHEDULE function is used in Google Sheets.
We already know that the calculated future value of the principal amount $10,000, in five years, is $13,301 (rounded off).
What we don’t know is how this amount was calculated. Let’s go and analyze!
The FVSCHEDULE function takes two arguments. One is the principal amount or the present value, while the other one is a group of values in percentage format. In the example above, $10,000 is the first argument or the amount of money to compound against the second argument, which is a set of percentage values grouped as one.
The FVSCHEDULE function multiplies the $10,000 to the first percentage value, which is 5%. The product, which is $600, will be added to the principal amount, which will now become the new principal amount. The function will now proceed to the next computation.
It will multiply the new principal amount to the next percentage value, which is 3.7%. The product, which is $392.2, will be added to the new principal amount, which is $10,600.
This pattern will continue until it reaches the last percentage value. In the end, it will return the last computed amount, which, in this case, is $13,301.
Like any other function, the FVSCHEDULE function takes direct values as arguments, instead of cell reference or ranges. However, be careful as this function, unlike most other ones, takes an array of values differently. Take a look at our sample below:
The function returns a #N/A error. By hovering over it, Google Sheet will give you a hint as to why it returns such error.
It says that the FVSCHEDULE function only expects 2 arguments, but it detected 7 arguments instead.
Why is this so?
Well, that is because we passed the rate schedule in a way that we told Google Sheet that they are different arguments by separating them with a comma ‘,’.
To be able to only present them as one, we need to enclose the rate schedule by braces ‘{}’.
This way, our FVSCHEDULE function can only see two arguments; 10,000 and everything between the open and close braces ‘{}’.
Also, don’t forget that, since we are passing percentage values, each value should have the percentage sign ‘%’. Otherwise, the function would yield an incorrect amount.
Alternatively, you can convert the number into equivalent percentages such as 0.05, 0.037, 0.068, 0.074 and 0.065.
You may make a copy of the spreadsheet using the link I have attached below.
How to Use FVSCHEDULE Function in Google Sheets
- Click on any cell to make it the active cell. For this guide, I will be selecting C9, where I want to show my result.
- Next, type the equal sign ‘=‘ to begin the function and then followed by the name of the function, which is our ‘fvschedule‘ (or ‘FVSCHEDULE‘, not case sensitive like our other functions).
- Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.
- Now the exciting part! Let’s give our function its first argument, which is the principal amount. You may pass the value directly by typing 10000.
- To let the Google sheet know that we’re done typing our first argument, we should now type in the delimiter or the character that separates each argument on a function. In this case, type comma ‘,’.
- Type in our second argument, which is the rate schedule. Note that we need to enclose the percentage values with braces ‘{}”. So, type in the open brace ‘{‘ followed by the first percentage value, which is 5.00%.
- Separated by a delimiter, the rest of the percentage values should follow.
- Finally, type the close brace ‘}’ and hit your Enter or Tab key. The cell C9 will now show you the result or the return value of the FVSCHEDULE function.
- Notice that, in our example above, we type in our values directly into our function. Alternatively, we can simply use the cell address of our principal amount and the cell range of our rate schedule.
- In cell C17, follow steps 1-5. Only this time, we will use the cell address where the amount 10000 is located, which is C2.
- As for the second argument, we don’t need to enclose it with braces ‘{}’, since we will be using the cell range to reference the rate schedule. After the comma, type in C4:C8. Alternatively, you can click cell C4 and drag it until C8.
- Finally, hit your Enter or Tab key.
That’s pretty much it. You can now use the FVSCHEDULE function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.
