Excel: Adjusting the Period of Financial Functions

Last reviewed: April 18, 1997
Article ID: Q36656

The information in this article applies to:

  • Microsoft Excel for the Macintosh, versions 1.x, 2.2, 3.0, 4.0, 5.x
  • Microsoft Excel for Windows, versions 3.x, 4.x, 5.x
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows

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 will have 48 periods (four years times 12 months). The rate should be entered as the rate for each period.

To calculate a 9 percent annual interest rate, compounded monthly, enter 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 the functions:

Any ver. of Microsoft Excel    Microsoft Excel ver. 1.5 and later
---------------------------    ----------------------------------

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


Additional query words: 1.00 1.03 1.04 1.06 1.50 2.20 3.00 4.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 18, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.