If you want to know the future value of an investment that you make regular payments on that has a fixed interest rate, this kind of calculation can be done very easily in Google Sheets.
There is a built-in function called the FV function that does exactly this.
In this tutorial, I will show you how to use the FV function in Google Sheets.
FV Function Syntax
The FV function will calculate the future value of an annuity investment that has a constant periodic payment and constant interest rate.
The syntax of this function is:
FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])
- rate – this is the interest rate for the investment
- number_of_periods – the number of payments to be made
- payment_amount – the amount that is paid each period
- present_value – the current value of the investment. This is an optional argument that will be set to zero by default
- end_or_beginning – this argument controls whether payments are made at the beginning or end of each period. This will be set to zero by default
- 0 – payments are due at the end of each period
- 1 – payments are due at the beginning of each period
One important thing to note when using this function is that you need to make sure the numbers you are using for rate, number_of_periods, and payment_amount are consistent with each other.
Here are a few examples to clarify what I mean by this:
- If you have a 12-month loan that is paid quarterly, then the annual interest rate would need to be divided by 4, and the number of payments would be 4
- If you have a 24-month loan that is paid quarterly, the annual interest rate would be divided by 4, and the number of payments would be 8
- If you have a 12-month loan with payments made monthly, the annual interest rate would be divided by 12, and the number of payments made will also be 12
Using the FV Function
Now that we have gone over the basic syntax of the FV function, let’s take a look at how to actually use it in our spreadsheet.
In this example, I will show calculating the future value for an annuity investment with a 4% annual interest rate, with a monthly payment of $500, which is paid for 2 years. Payments will be made at the end of each period.
Here is my starting data:
Here are the steps to using this function:
1. First, select the cell where you want to calculate the function. In that cell, type “FV”, and then press Tab on your keyboard to begin entering the formula. The first argument of the function is the interest rate. Remember that you need the interest rate to be consistent with the number of periods. In this example, the interest rate is annual, and it is paid monthly, so for the first argument, I enter B1/12.
2. The next argument of the function is the number of periods. In this example, I have this in cell B2, so that is what I place in the formula
3. Next is the payment amount. In this example this is cell B3, so that is what is placed in the formula
4. Lastly, it is the present value of the annuity, which is in cell B4 in this example. After this argument, I place my closing parenthesis. Since payments are at the end of each period, I can leave off the optional fifth parameter, because it will be set to end by default.
5. When you are finished with the formula, press Enter and you will see the calculated future value
In the example above, I walked through an example where payments are made at the end of each period. Let’s take a look at how things change when payments are made at the beginning.
Here is a comparison of both formulas:
You can see in the example above, the only difference is that in the last argument, I have placed a 1 to indicate payments at the beginning of each period. You can see by making this change, the calculated value is slightly different than in the first method.
Hopefully, by now you understand how to use this formula. The best way to learn is by doing, so I always recommend you try setting this up in your own spreadsheet.
Making this kind of calculation without using this function would require a much more complicated equation. Learning to use this formula should certainly speed up your productivity calculating this!