Compare effect of compounding periods

=FV(rate,nper,pmt,pv)
To compare the effect of (non-annual) compounding periods on growth, you can set up a worksheet as shown, and calculate future value with the FV function. In the example shown, $1000 is invested with an annual interest rate of 5%, the formulas in column D calculate the future value of the $1000 assuming the compounding periods shown in column C. The formula in D5, copied down, is:
=FV(rate/C5,C5*term,0,-pv)
where pv (G4), rate (G5), and term (G6) are named ranges.
The FV function calculates compound interest and return the future value of an investment over a specified term. To configure the function, we need to provide a rate, the number of periods, the periodic payment, the present value:
- Present value (pv) is the named range G4
- Rate is provided as as annual rate/periods, or rate/C5
- Number of periods (nper) is given as periods * term, or C5*term
- There is no periodic payment, so we use zero (0)
By convention, the present value (pv) is input as a negative value, since the $1000 "leaves your wallet" and goes to the bank during the term.
The named ranges automatically behave like absolute references, so there is no need to use dollar signs ($).
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.