You are currently offline, waiting for your internet to reconnect

XL: How to Calculate Compound Interest

This article was previously published under Q141695
For a Microsoft Excel 98 version of this article, see 191017.
Compound interest is the amount that a dollar invested now will be worthin a given number of periods at a given compounded interest rate perperiod.

Although Microsoft Excel does not include a function for determiningcompound interest, you can use the following formula for this calculation
where PV is present value, R is the interest rate, and N is the number ofinvestment periods.
Suppose you have $1,000.00 in an investment account. The account pays 8percent interest and this interest is compounded annually. How much willthe investment be worth at the end of three years?There are two ways to find the amount:
  • Use a Fixed Formula
  • Create a Function Macro to Determine Compound Interest

Use a Fixed Formula

The following formula typed into a cell on a worksheet, returns thecorrect value of $1,259.71:
However, all of the information is 'hard-coded' into the formula and you must manually change the formula any time the figures change.

Create a Function Macro to Determine Compound Interest

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
A custom function is more flexible because none of the actual raw data is 'hard-coded' into the function; the user just types the data for the calculation instead of the actual calculation. To create this custom function, follow these steps:
  1. Start Microsoft Excel.
  2. Press ALT+F11 to start the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. Type the following code in the new module:
    Function Yearly_Rate(PV As Double, R As Double, N As Double) As Double     Yearly_Rate = PV*(1+R)^N    'Performs computation End Function					
To use the custom function, follow these steps:
  1. Type the following values in your worksheet:
           Cell     Value       --------------       A1     1000.00       A2         .08       A3        3.00						
    These values represent the following:

    • A1: Present value of the investment
    • A2: Interest rate
    • A3: Number of investment periods
  2. In any blank cell, type the following formula
    where A1, A2, and A3 are the cells that contain the present value, interest rate, and number of investment periods respectively.
The cell in which you typed the formula displays $1,259.71. This is the amount your original investment of $1000.00 is worth after three investment periods at 8 percent compound interest.
"Cost Accounting-A Managerial Approach," Charles T. Horgren, Prentice-Hall,Inc., Fourth Edition, pages 906-907
XL2002 XL2000 8.00 97 XL97 XL7 XL2007 XL2010

Article ID: 141695 - Last Review: 02/15/2012 23:31:00 - Revision: 6.0

Microsoft Excel 2000 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft Excel 95 Standard Edition

  • kbhowto kbinfo KB141695