XL97: How to Use the EFFECT and NOMINAL Functions to Calculate Loan or Mortgage Interest with Intrayear Compounding (291106)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q291106

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 include: annual, intrayear, and annuity compounding. This article discusses intrayear calculations for compound interest.

MORE INFORMATION

Calculating Future Value with Intrayear Compounded Interest

Intrayear 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*(1+(k/m))^(m*n)
where

P = initial principal
k = nominal annual interest rate
m = number of times per year the interest is compounded
n = number of years or term of the loan

You can use the EFFECT worksheet function to obtain the same result as follows 
   =P+(P*EFFECT(EFFECT(k,m)*n,n))
or to determine the effective interest rate as follows: 
   =EFFECT(EFFECT(k,m)*n,n)
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: 
   =EFFECT(k,m)

Examples

To calculate future value and effective interest by using the EFFECT function, and compare the result with the general calculation, follow these steps:
  1. Start Microsoft Excel and open a new worksheet.
  2. Type the following data in cells A1:B4, and format the cells as shown:
       A1: Principal       B1: $1000.00 
   A2: NomRate         B2:   10.00%
   A3: Frequency       B3:     1
   A4: Term            B4:     1
  3. Create names for the cells B1:B4 that use the labels in A1:A4. To do this, select each cell (B1:B4) separately, point to Name on the Insert menu, and then click Define.
  4. Type the following data in cells A6 and B6:
       A6: Future Value    B6: =Principal*(1+(NomRate/Frequency))^(Frequency*Term)
  5. Type the following data in cells A8 and B8:
       A8: Future Value    B8: =Principal*(1 + EFFECT(EFFECT(NomRate,Frequency)*Term,Term))
  6. Type the following data in cells A9 and B9:
       A9: Effective Rate  B9: =EFFECT(EFFECT(NomRate,Frequency)*Term,Term)
  7. Format cells B6 and B8 as Currency, and cell B9 as Percentage with two decimal places.
  8. Change the Term (cell B4) to 2 years to observe the effect of annual compounding.
  9. Change the Frequency (cell B3) to 2 per year to observe the effect of intrayear compounding.

Calculation of Loan or Mortgage Payments

Loans 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:
  1. Type the value 1 in cells B3 and B4.
  2. Type the following data in cells A5 and B5:
       A5: NumberOfPmts   B5: 12
  3. Select cell B5. On the Insert menu, point to Name, and then click Define. Click OK.
  4. Type the following data in cells A11 and B11 
       A11: Payment       B11: =PMT(NomRate/NumberOfPmts, NumberOfPmts*Term, -Principal)
    and notice the payment of $87.92.

    NOTE: The minus (-) sign is assigned to Principal to indicate that the Payment and Principal flow in opposite directions.
  5. Select cell B11. On the Insert menu, point to Name, and then click Define. Click OK.
  6. Type the following data in cells A13:D13:
       A13: Principal     B13: Interest     C13: Payment     D13: Balance
  7. Type the following data in cells A14 and B14:
       A14: =Principal    B14: =A14 * NomRate/NumberOfPmts
  8. Type the following data in cells C14 and D14:
       C14: =Payment      D14: =A14+B14-C14
  9. Format cells A14:D14 as Currency.
  10. In cell A15, type =D14.
  11. Select cells B14:D14, click the fill handle, and fill down one row.
  12. Select cells A15:D15, click the fill handle, and fill down 10 rows.

    Notice that the final balance is $0.00.
  13. Select cell C27 and double-click the AutoSum button to calculate the total amount paid in interest.
To see the difference between nominal and effective interest rate, suppose that no payments were actually made, and fill cells C14:C25 with zeros. When you do this, notice that the accumulated interest becomes $104.71, corresponding to an effective interest rate of 10.47% compared to the 10.00% nominal rate used in the calculation. The interest is in fact compounded monthly, so that the overall effect is one of a higher rate.

To see the correspondence between the EFFECT and NOMINAL worksheet functions, follow these steps:
  1. Type the following data in cells A29 and B29:
       A29: Effective    B29: =EFFECT(NomRate,NumberOfPmts)
  2. Select cell B29. On the Insert menu, point to Name, and then click Define. Click OK.
  3. Type the following data in cells A30 and B30:
       A30: Nominal      B30: =NOMINAL(Effective,NumberOfPmts)
  4. Format cells A30 and B30 as Percentage with two decimal places.

Different Jurisdictions

The 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: 
    =PMT((1+NomRate/Frequency)^(Frequency/NumberOfPmts)-1, NumberOfPmts*Term, -Principal)
And the interest calculation in cells B14 and below would have the following form: 
    =A14 * ((1+NomRate/Frequency)^(Frequency/NumberOfPmts)-1)
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.

REFERENCES

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:

141695 XL: How to Calculate Compound Interest

Modification Type:MajorLast Reviewed:9/25/2006
Keywords:kbhowto KB291106
©2004 Microsoft Corporation. All rights reserved.