Article ID: 291106 - View products that this article applies to.
This article was previously published under Q291106
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.
Calculating 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 add-in. 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 = initial principalYou 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
or to determine the effective interest rate as follows:
When you deal with a long-term loan, use the EFFECT function twice: first to determine the effect of intrayear compounding within a particular year, and then to extend that effect over the term of the loan. For a one-year calculation, you can use the simpler form:
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 semi-annually 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:
And the interest calculation in cells B14 and below would have the following form:
=PMT((1+NomRate/Frequency)^(Frequency/NumberOfPmts)-1, NumberOfPmts*Term, -Principal)
This difference results in slightly lower interest costs for the borrower in the Canadian situation, and illustrates the importance of knowing the financial environment in which you are using the worksheet functions.
=A14 * ((1+NomRate/Frequency)^(Frequency/NumberOfPmts)-1)
For 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:
(http://support.microsoft.com/kb/141695/EN-US/ )XL: How to Calculate Compound Interest
Article ID: 291106 - Last Review: September 25, 2006 - Revision: 2.0
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Contact us for more help