Summary

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.

Generic formula

=FV(rate,nper,pmt,pv)

Explanation 

The FV function calculates compound interest and returns 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, and the present value:

  • Present value (pv) is the named range G4
  • Rate is provided 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 ($).
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.