EXCEL FV FUNCTION TO CALCULATE FUTURE VALUES
The Excel FV (Future Value) function, is one of the top 15 useful financial functions in MS Excel. It is used to get the future value of an investment with periodic constant payments and a constant interest rate.
Return Value- Future value
In this example, we have calculated the future value of a loan. We have all the values defined. In this example the formula used in C9 is:
Annual interest= C5/C7
Pmt is defined as zero(0)
Figure 1– Example of Excel FV function to Calculate of Future Value
Given below are the arguments
rate – The interest rate per period (per month in our case)
nper – The number of periods for the payment (months)
pmt – Payment per period, it MUST be a negative number
pv – The current value of future payments. It MUST be entered as a negative number and will be assumed zero if excluded. [Optional argument]
type – The argument to define whether the payment will be made at the start or end of the period. [Optional argument]
- Consistent units for rate and nper should be used. In our example, the ten-year loan has monthly payments at 5% interest, so the rate is 5%/12, and nper is 10*12 that makes a total of 120payments.
If annual payments are made for the same loan, use 5% for rate and 10 for nper.
(5% = interest rate, 10 = total 10 payments on yearly basis).
- The payment value must be negative if pmt is for the payouts, while for the cash received it must be positive.