Exceljet

Quick, clean, and to the point

Compare effect of compounding periods

Excel formula: Compare effect of compounding periods
Generic formula 
=FV(rate,nper,pmt,pv)
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. 

Explanation 

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 ($).

Author 
Dave Bruns
Related formulas 
Excel formula: Calculate compound interest
Calculate compound interest
The FV function can calculate compound interest and return the future value of an investment. To configure the function, we need to provide a rate, the number of periods, the periodic payment, the present value. To get the rate (which is the period...
Excel formula: Calculate simple interest
Calculate simple interest
The general formula for simple interest is: interest = principal * rate * term So, using cell references, we have: = C5 * C7 * C6 = 1000 * 10 * 0.05 = 500
Excel formula: Annual compound interest schedule
Annual compound interest schedule
If you have an annual interest rate, and a starting balance you can calculate interest with: = balance * rate and the ending balance with: = balance + ( balance * rate ) So, for each period in the example, we use this formula copied down the table...
Excel formula: Effective annual interest rate
Effective annual interest rate
The Effective Annual Rate (EAR) is the interest rate after factoring in compounding. In other words, the EAR is the rate actually earned due to the effect of compounding more frequently than once a year (annually). The EFFECT function calculates the...
Related functions 
Excel FV function
Excel FV Function
The Excel FV function is a financial function that returns the future value of an investment. You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.