Summary

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*C8,0,-C5)

Generic formula

=FV(rate,nper,pmt,pv)

Explanation 

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 rate) we use the annual rate / periods, or C6/C8.

To get the number of periods (nper) we use term * periods, or C7 * C8.

There is no periodic payment, so we use zero.

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 solution goes like this this:

=FV(C6/C8,C7*C8,0,-C5)
=FV(0.05/12,10*12,0,-1000)
=FV(0.00417,120,0,-1000)
=1647 
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.