SUMMARY
Calculating the future value of a dollar amount, commonly called the
compounded value, requires applying 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 contains
information about intrayear calculations for compound interest.
For additional information about annual compounding, please see the
following article in the Microsoft Knowledge Base:
141695 XL: How to Calculate Compound Interest
MORE INFORMATION
Intrayear compound interest is interest that is compounded more often than
once per year. Financial institutions may calculate interest on the basis
of semiannual, quarterly, monthly, weekly, or even daily time periods.
Microsoft Excel 98 Macintosh Edition 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 by using the EFFECT
worksheet function is the following:
=P+(P*EFFECT(EFFECT(k,m)*n,n))
The general equation to calculate compound interest is the following:
For this equation, the following is true:
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 sample data that is listed in the following table.
IntraYear 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% compounded semiannually. If the money is
left in the account for 3 years, how much will the $100 be worth?
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))
The example returns $126.53.
Example Using the General Equation:
The following example uses the general equation:
The example returns $126.53.
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:
To use the general equation to return the compounded interest rate, use the
following equation:
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)
The example returns 16.022%.
Example Using the General Equation:
For example, the following equation returns the interest rate:
The example returns 16.022%.
REFERENCES
For more information about compound interest, click Contents And Index
on the Help menu (or on the Balloon Help menu if you are using a version of
the Macintosh operating system earlier than 8.0), click the Index button in
MS Excel Help, type the following text
and then click Show Topics. Select the "EFFECT" topic, and
click Go To. If you are unable to find the information you need, ask the
Office Assistant.