Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
How to calculate compound interest for an intra-year period in Excel
Article ID: 213907 - View products that this article applies to.
This article was previously published under Q213907
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:
(http://support.microsoft.com/kb/141695/EN-US/ )XL: How to Calculate Compound Interest
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
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?
Intra-Year Number of compounding compounding rate periods per year ----------------------------------------------- Semiannual 2 Quarterly 4 Monthly 12 Weekly 52 Daily 360 or 365(actual)
=100+(100*EFFECT(EFFECT(.08,2)*3,3))The example returns $126.53.
=100*(1+.08/2)^(2*3)The example returns $126.53.
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:
=EFFECT(EFFECT(.075,4)*2,2)The example returns 16.022 percent.
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.
Article ID: 213907 - Last Review: September 18, 2011 - Revision: 5.0