Skip to main content
Support
Sign in
Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

## 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, view the following article:

### 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 for versions older than 2003. The Analysis ToolPak is already loaded. 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

### Examples

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

Intra-Year compounding rate

Number of compounding 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?

#### 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.

#### Using the General Equation

The following example uses the general equation:

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

The example returns \$126.53.

#### 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

### Examples

#### 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.

#### Use the General Equation

The following equation returns the interest rate:

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

## References

For more information about compound interest, click Microsoft Excel Help on the Help menu, type effect in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

### Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

## Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

×