For example, if you deposit an amount of $500.00 for 5 years at the rate of interest provided at 5%, then we will calculate the future value that it will receive at the end of the 5th year in the following manner.
At the beginning of the year (1st year), the opening balance will be nil, $0.
Now, let us assume that the amount deposited in the account is $500.00.
Here,
- Opening Balance = OB
- Deposited Amount = DA
- Rate of Interest = R
- Interest Amount = I
- Closing Balance = CB
So, the interest in 1st year at the 5% will be
(OB + DA)*R
= (0+500)*0.05 equals to $25.00
So, the closing balance of the 1st year will be
(OB+DA+I)
= (0.00+500.00+25.00) equals to $525.00
The deposited amount in column D remains the same throughout the 5 years. But, at the end of the 5th year, the value that will have each year will be added with interest. So, let us calculate this manually first, then we will be using the FV Excel function to get the desired result figured out automatically, thus saving time and effort.
In column C, we have the opening balance each year. In the first year, we have to start opening a balance with a nil account; the amount will be 0$.
In column E, we have the interest payment for each year. For example, an interest rate is 5% in cell C1. So, the interest payment in the first year will be the sum of the opening balance and deposited balance times the interest value.
So, in the first year, we received the interest value amount of $25.00. Then, finally, the closing balance in column F will be calculated as the sum of all the balances that the sum of the opening balance, deposited amount, and the interest amount.
So, $525.00 will be the opening balance for the next year, the second year.
Again, we are receiving a deposit of $500.00 in the second year. Similarly, the interest is calculated in the same manner.
So, at the end of the 5th year, calculating it the same way, we get the final future value amount, which is $2900.96.
Now, this can be directly calculated using the FV function in Excel. When,
- rate = 5%
- nper = 5 years
- pmt = deposited amount each year ($500.00)
- pv = 0 (at the start of Year 1)
- type = 0 and 1 ( 0 means payment received at the end of the period, 1 payment received at the beginning of the period)
So, according to the FV formula, it will calculate the FV in Excel as:
=fv(rate,nper,pmt,[pv],[type])
Here, the type is 1 because we receive the payment at the start of each period. The fv value calculated using the future value function is within the red parenthesis that denotes the negative value. It is usually negative because, in the end, the bank pays out the amount. Thus, it signifies the outflow and inflow of the amount.
