Microsoft KB Archive/213907

= How to calculate compound interest for an intra-year period in Excel 2000 =

Article ID: 213907

Article Last Modified on 9/9/2004

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213907





For a Microsoft Excel 97 and earlier version of this article, see 162634.

IN THIS TASK
SUMMARY Calculate Future Value of Intra-Year Compound Interest Examples
 * Use the EFFECT Worksheet Function
 * Use the General Equation

Calculate Interest Rates for Intra-Year Compounding Examples
 * Use the EFFECT Worksheet Function
 * Use the General Equation

REFERENCES



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 are annual, intra-year, and annuity compounding. This article discusses intra-year calculations for compound interest.

For additional information about annual compounding, click the article number below to view the article in the Microsoft Knowledge Base:

141695 XL: How to Calculate Compound Interest

back to the top

Calculating Future Value of Intra-Year Compound Interest
Intra-year compound interest is interest that is compounded more frequently 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 intra-year compound interest with the EFFECT worksheet function is as follows:

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

The general equation to calculate compound interest is as follows

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

where the following is true:

P = initial principal

k = annual interest rate paid

m = number of times per period (typically months) the interest is compounded

n = number of periods (typically years) or term of the loan

back to the top

Examples
The examples in this section use the EFFECT function, the general equation, and the following sample data:   Intra-Year                 Number of compounding compounding rate         periods per year ---  Semiannual                  2 Quarterly                  4 Monthly                   12 Weekly                    52 Daily                    360 or 365(actual) An investment of $100 pays 8.00 percent compounded semiannually. If the money is left in the account for three years, how much will the $100 be worth?

back to the top

Use 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 to spread out (annualize) the compounded rate of over the term of the investment:

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

The example returns $126.53.

back to the top

Using the General Equation
The following example uses the general equation:

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

The example returns $126.53.

back to the top

Calculate Interest Rates for Intra-Year 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)

To use the general equation to return the compounded interest rate, use the following equation:

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

back to the top

Use the EFFECT Worksheet Function
An investment of $100 pays 7.50 percent compounded quarterly. The money is left in the account for two years, for example. The following formula returns the compounded interest rate:

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

The example returns 16.022 percent.

back to the top

Use the General Equation
The following equation returns the interest rate:

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

back to the top

