Microsoft Office Tutorials and References

In Depth Information

Figure 11-9:
Using a single formula to calculate compound interest.

Cell B9 contains the following formula that calculates the periodic interest rate. This value is the interest rate

used for each compounding period.

=B5*(1/B6)

The formula in cell B10 uses the FV function to calculate the value of the investment at the end of the term. The

formula is

=FV(B9,B6*B7,,-B4)

The first argument for the FV function is the periodic interest rate, which is calculated in cell B9. The second

argument represents the total number of compounding periods. The third argument (pmt) is omitted, and the

fourth argument is the original investment amount (expressed as a negative value).

The total interest is calculated with a simple formula in cell B11:

=B10-B4

Another formula, in cell B13, calculates the annual yield on the investment:

=(B11/B4)/B7