XL: How to Calculate Compound Interest for an IntraYear Period

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c

SUMMARY

The future value of a dollar amount, commonly called the compounded value, involves the application of compound interest to a present value amount. The result is a future dollar amount. Three types of compounding include: annual, intrayear, and annuity compounding. This article discusses intrayear calculations for compound interest.

For additional information about annual compounding, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q141695
   TITLE     : XL: How to Calculate Compound Interest

MORE INFORMATION

Calculating Future Value of Intrayear Compounded Interest

Intrayear compound interest is interest that is compounded more often than once a year. Financial institutions may calculate interest on bases of semiannual, quarterly, monthly, weekly, or even daily time periods.

Microsoft Excel includes the EFFECT function in the Analysis ToolPak add-in. The EFFECT function returns the compounded interest rate based on the annual interest rate and the number of compounding periods per year.

The formula to calculate intrayear compound interest using the EFFECT worksheet function is the following:

   =P+(P*EFFECT(EFFECT(k,m)*n,n))

   =P*(1+(k/m))^(m*n)

  P = initial principal
  k = annual interest rate paid
  m = number of times per period (typically years) the interest is
      compounded
  n = number of periods (typically years) or term of the loan

Examples

The examples in this section use the EFFECT function, the general equation, and the following sample data.

 IntraYear                 Number of compounding
 compounding rate          periods per year
 -----------------------------------------------

 Semiannual                  2
 Quarterly                   4
 Monthly                    12
 Weekly                     52
 Daily                     360 or 365(actual)

Example Using the EFFECT Worksheet Function:

Because of semiannual compounding, you must repeat the EFFECT function twice to calculate the semiannual compounding periods. In the following example, the result of the nested function is multiplied by 3 in order to spread out (annualize) the compounded rate of over the term of the investment:

   =100+(100*EFFECT(EFFECT(.08,2)*3,3))

Example Using the General Equation:

The following example uses the general equation:

   =100*(1+.08/2)^(2*3)

Calculating Interest Rates for Intrayear Compounding

You can find the compounded interest rate given an annual interest rate and a dollar amount.

The EFFECT worksheet function uses the following formula:

   =EFFECT(EFFECT(k,m)*n,n)

   =(1+(k/m))^(m*n)-1

Examples

Example Using the EFFECT Worksheet Function:

An investment of $100 pays 7.50% compounded quarterly. The money is left in the account for 2 years For example, the following formula returns the compounded interest rate:

   =EFFECT(EFFECT(.075,4)*2,2)

Example Using the General Equation:

For example, the following equation returns the interest rate:

   =(1+(.075/4))^(4*2)-1

REFERENCES

For more information about compound interest, click the Index tab in Microsoft Excel 97 Help, type the following text

   compound interest

For more information about the EFFECT worksheet function in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

   effect

   effect