The FV function in Google Sheets is all about finding the future value of a periodic investment at a fixed interest rate. By using this function, you can also find the future return of a lump-sum payment.
The fact is that many of us are not well conversed in manual financial calculations. So better depend on a Spreadsheet application for such purpose. Financial functions are built-in to Google Sheets, Excel, etc.
Since Google Sheets is cloud-based and free to use, it’s a good idea to learn some of the financial functions it. It will really help you to stay financially disciplined. In this tutorial, I am covering how to use the FV financial function in Google Sheets.
The Syntax of the FV Function in Google Sheets
The purpose of the FV function is already mentioned in the first para. Now let me go to the detail of the FV function syntax and usage.
FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])
Arguments:
Rate: It’s the interest rate per period. If you refer the interest rate in a cell (assume Cell C4), then you can enter it in different ways.
For example, if the annual interest rate of your periodic or lump-sum investment is 5%, you can either enter it like 5% or 0.05 in cell C4.
Further, you can also enter it as the number 5, then in the formula use it with the function UNARY_PERCENT as UNARY_PERCENT(C4)
.
Number of Periods (Nper): The total number of payment to be made.
Payment Amount (Pmt): The payment to be made in each period. In case of lump_sum payment, you must include the Pv argument.
Present Value (Pv): it’s optional and 0 by default. If Pv is omitted, you must include the Pmt argument.
Type: Its’ 0 by default. Type indicates when payments are due (at the beginning or end).
Please enter the Pmt and Pv as negative values as both are cash pay out.
Formula Examples to the FV Function in Google Docs Sheets
Future Value (FV) in Monthly Payment Frequency
To create a formula that calculates the FV of an investment, keep ready with the following inputs, i.e. the interest rate, the total number of periods, and the payment amount.
A monthly investment of $2,000 for 3 years @5% annual interest will be $77,506.67 at the end of the term/period.
=FV(5%/12,3*12,-2000)
Future Value (FV) in Quarterly Payment Frequency
If you are not getting a monthly salary it may not be possible to proceed with a monthly investment plan. The better option will be quarterly payments.
See how much you will get at the end of the term with a quarterly periodic payment $6,000 (3*$2,000). Here again, I am considering the annual interest rate of 5%.
=FV(5%/4,12,-6000)
Result: $77,162.17
How to Use the FV Function in Google Sheets in Yearly Payments
If the series of payments are annual, then leave the interest as it’s. I mean no need to divide it by 12 (monthly) or 4 (quarterly).
=FV(5%,3,-24000)
Result: $75,660.00
FV Function in Google Sheets to Calculate FV of Lump-Sum Payments
No doubt the FV function in Google Sheets is for calculating the future value of periodic payments. But as mentioned at the beginning and as well as in the formula syntax section, we can use it in lump-sum payment too.
See how to find the future value of a lump-sum payment (one-time payment) using the FV formula in Sheets.
Interest Rate: 5%
Periods: 36 (3 years)
Present Value (Pv): $72,000.00
Here is the formula and the output follows.
=FV(5%/12,36,0,-72000,0)
Please go thru’ the future value of different series of payments above. It’s as follows.
Monthly: $77,506.67
Quarterly: $77,162.17
Yearly: $75,660.00
Lump-sum: $83,626.00
From this what you can understand?
If you make a total investment of 72,000.00 (lump_sum or periodic) for a period of 3 years @5% interest rate, the final value will be as above.
The value will be definitely different and you can choose the best periodic payment method from this.
This way you can make use of the FV function in Google Sheets. Enjoy!