Excel formula: Compare effect of compounding periods

Generic formula

=FV(rate,nper,pmt,pv)

Explanation

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.

How this formula works

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

Calculate compound interest

To calculate compound interest in Excel, you can use the FV function . This example assumes that $1000 is invested for 10 years at an annual interest rate of 5%, compounded monthly. In the example shown, the formula in C10 is: = FV ( C6 / C8 , C7 *...

Calculate simple interest

To calculate simple interest in Excel (i.e. interest that is not compounded), you can use a formula that multiples principal, rate, and term. This example assumes that $1000 is invested for 10 years at an annual interest rate of 5%. Simple interest...

Annual compound interest schedule

To calculate annual compound interest, you can use a formula based on the starting balance and annual interest rate. In the example shown, the formula in C6 is: = C5 + ( C5 * rate ) Note: "rate" is the named range F6. How this formula works If you...

Effective annual interest rate

To calculate the effective annual interest rate, when the nominal rate and compounding periods are given, you can use the EFFECT function. In the example shown, the formula in D5, copied down, is: = EFFECT ( rate , C5 ) where "rate" is the named...

Related functions

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.