You are currently offline, waiting for your internet to reconnect

This article was previously published under Q291106

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.

Microsoft Excel includes the EFFECT function in the Analysis ToolPakadd-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 followsk = 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))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)

- Start Microsoft Excel and open a new worksheet.
- 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

- 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.
- Type the following data in cells A6 and B6:
A6: Future Value B6: =Principal*(1+(NomRate/Frequency))^(Frequency*Term)

- Type the following data in cells A8 and B8:
A8: Future Value B8: =Principal*(1 + EFFECT(EFFECT(NomRate,Frequency)*Term,Term))

- Type the following data in cells A9 and B9:
A9: Effective Rate B9: =EFFECT(EFFECT(NomRate,Frequency)*Term,Term)

- Format cells B6 and B8 as Currency, and cell B9 as Percentage with two decimal places.
- Change the Term (cell B4) to 2 years to observe the effect of annual compounding.
- Change the Frequency (cell B3) to 2 per year to observe the effect of intrayear compounding.

To construct an amortization table for a loan that uses the worksheet data above and assumes 12 equal monthly payments, follow these steps:

- Type the value 1 in cells B3 and B4.
- Type the following data in cells A5 and B5:
A5: NumberOfPmts B5: 12

- Select cell B5. On the Insert menu, point to Name, and then click Define. Click OK.
- 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. - Select cell B11. On the Insert menu, point to Name, and then click Define. Click OK.
- Type the following data in cells A13:D13:
A13: Principal B13: Interest C13: Payment D13: Balance

- Type the following data in cells A14 and B14:
A14: =Principal B14: =A14 * NomRate/NumberOfPmts

- Type the following data in cells C14 and D14:
C14: =Payment D14: =A14+B14-C14

- Format cells A14:D14 as Currency.
- In cell A15, type =D14.
- Select cells B14:D14, click the fill handle, and fill down one row.
- Select cells A15:D15, click the fill handle, and fill down 10 rows.

Notice that the final balance is $0.00. - Select cell C27 and double-click the AutoSum button to calculate the total amount paid in interest.

To see the correspondence between the EFFECT and NOMINAL worksheet functions, follow these steps:

- Type the following data in cells A29 and B29:
A29: Effective B29: =EFFECT(NomRate,NumberOfPmts)

- Select cell B29. On the Insert menu, point to Name, and then click Define. Click OK.
- Type the following data in cells A30 and B30:
A30: Nominal B30: =NOMINAL(Effective,NumberOfPmts)

- Format cells A30 and B30 as Percentage with two decimal places.

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.

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

XL97

Properties

Article ID: 291106 - Last Review: 09/25/2006 07:03:15 - Revision: 2.0

- Microsoft Excel 97 Standard Edition

- kbhowto KB291106