Article ID: 291106  View products that this article applies to. This article was previously published under Q291106 On This PageSUMMARY
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.
MORE INFORMATIONCalculating Future Value with Intrayear Compounded InterestIntrayear compound interest is interest that is compounded more often than once a year. Financial institutions may calculate interest based on semiannual, quarterly, monthly, weekly, or even daily compounding. For example, mortgage payments are calculated differently in different jurisdictions, particularly in the United States and Canada, with the result that effective interest rates are not the same for loans or mortgages that are described as having the same nominal rate.Microsoft Excel includes the EFFECT function in the Analysis ToolPak addin. The EFFECT function returns the effective compounded interest rate based on the nominal annual interest rate and the number of compounding periods per year. This is the same as the simple interest that would be paid on the loan as a lump sum. The NOMINAL function performs the inverse calculation; it returns the nominal interest rate based on the effective rate and the number of compounding periods. The general equation to calculate the future value of a loan or mortgage is as follows =P*(1+(k/m))^(m*n)
P = initial principal
You can use the EFFECT worksheet function to obtain the same result as follows
k = nominal annual interest rate m = number of times per year the interest is compounded n = number of years or term of the loan =P+(P*EFFECT(EFFECT(k,m)*n,n)) =EFFECT(EFFECT(k,m)*n,n) =EFFECT(k,m) ExamplesTo calculate future value and effective interest by using the EFFECT function, and compare the result with the general calculation, follow these steps:
Calculation of Loan or Mortgage PaymentsLoans and mortgages are usually paid down or amortized over a certain period, typically 15 to 30 years, with a fixed number of equal payments. The schedule of payments is usually presented in an amortization table, which shows the principal owing, the interest accrued, the payment required, and the balance owing after payment, for each period of the amortization. You can use the PMT worksheet function to determine the payment that is required to amortize a loan over a given period.To construct an amortization table for a loan that uses the worksheet data above and assumes 12 equal monthly payments, follow these steps:
To see the correspondence between the EFFECT and NOMINAL worksheet functions, follow these steps:
Different JurisdictionsThe above example calculates payments by using the PMT worksheet function, as it is commonly applied in the United States. However, other jurisdictions may calculate mortgage payments differently.For example, in Canada, the nominal interest rate would be applied semiannually to the example above, although it would still be described as a loan at 10.00%. In this case, you would type the value 2 in cell B3 (Frequency) to describe the two compounding periods annually in the nominal rate. Also, the PMT function in cell B11 would have the following arguments: =PMT((1+NomRate/Frequency)^(Frequency/NumberOfPmts)1, NumberOfPmts*Term, Principal) =A14 * ((1+NomRate/Frequency)^(Frequency/NumberOfPmts)1) REFERENCESFor more information about EFFECT and other functions involved in compound interest calculations, click Microsoft Excel Help on the
Help menu, type compound interest in the Office Assistant or
the Answer Wizard, and then click Search to view the topics
returned. For additional information about annual compounding, click the article number below to view the article in the Microsoft Knowledge Base: 141695
(http://support.microsoft.com/kb/141695/ENUS/
)
XL: How to Calculate Compound Interest
PropertiesArticle ID: 291106  Last Review: September 25, 2006  Revision: 2.0
