XL: Adjusting the Period of Financial Functions (214150)

SUMMARY

The compounded period used by Microsoft Excel in calculating financial functions can be altered by changing the number of periods over which the function is calculated. For example, a four-year loan compounded monthly has 48 periods (4 years times 12 months). You need to type the rate as the rate for each period.

To calculate a 9-percent annual interest rate, compounded monthly, type the monthly interest rate, which, in this example, is .75 percent (9 percent divided by 12 months).

As an example, when you calculate the future value of $10,000 four years from now at an annual interest rate of 9 percent, Microsoft Excel returns the following results:

   Compounded Period     Formula                      Result
   -----------------     -------                      ------
   yearly                =FV(9%,4,,-10000)            $14,115.82
   quarterly             =FV(9%/4,4*4,,-10000)        $14,276.21
   monthly               =FV(9%/12,4*12,,-10000)      $14,314
   daily                 =FV(9%/365,4*365,,-10000)    $14,332.66

MORE INFORMATION

This information regarding the number of periods is true for all of the financial functions that take a percentage rate as an argument. The following is a list of those functions:

FV()
IRR()
IPMT()
MIRR()
NPER()
NPV()
PMT()
PPMT()
PV()
RATE()