XL: Detailed Solutions for Interest Rate Formulas

 Last reviewed: February 2, 1998
Article ID: Q123757
The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, the functions FV, NPER, PMT, PV, and RATE are all elements of the following two general interest formulas. 

   If rate is not 0
   ----------------

   PV*((1+ rate)^NPER)+ PMT*(1+rate*type)*(((1+ rate)^NPER)-1)/rate+FV = 0

   If rate = 0
   -----------

   (PMT*NPER)+PV+FV = 0
If you look in Help to find detailed information about one of the functions listed above, these two formulas are referenced. However, Help does not contain solutions for the individual functions. The information in the "More Information" section of this article contains solutions for each of these functions.

MORE INFORMATION

For each function below, a definitions is given, and then general solutions are provided with the special case of rate=0 solutions given last. The solutions are in alphabetical order by derived function name.

NOTE: All of the general terms are shown as positive, but the actual value will be positive (receiving) or negative (paying) cashflow. Input variables must always be entered with the appropriate sign.

Definitions

FV - Future value of the investment

NPER - Number of periods in the investment

PMT - Payment amount of the investment

PV - Present value of the investment

rate - Interest rate of the investment

type - 0 if payment is at the end of a period, 

       1 if payment is at the start

Formulas If Rate Is Not 0

FV = (PMT*(1+rate*type)*(1-(1+ rate)^NPER)/rate)-PV*(1+rate)^NPER

NPER = LOG10((PMT*(1+rate*type)-FV*rate)/(PMT*(1+rate*type)+PV*rate))/ 

       LOG10(1+rate)
PMT = (rate*(FV+PV*(1+ rate)^NPER))/((1+rate*type)*(1-(1+ rate)^NPER))

PV = (PMT*(1+rate*type)*(1-(1+ rate)^NPER)-rate*FV)/(rate*(1+rate)^NPER)

rate = Must be solved using iterative processes since it is an equation of 

       variable order depending on the value of NPER.

Formulas If Rate Is 0

FV = -1(PV+PMT*NPER)

NPER = -1(FV+PV)/PMT

PMT = -1(FV+PV)/NPER

PV = -1(FV+PMT*NPER)

These solutions are a derivation of the model. If you test the solutions against the actual functions in Microsoft Excel, be sure the terms are not forced to be integer at any point because rounding errors become more significant as the order of the equation increases. (If you test the function programmatically, define the terms as type double to reduce computation errors).

Additional query words: 5.0 5.0a 5.0c 7.0 97 XL97 98 XL98
Keywords : xlformula xlhelp
Version : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform : MACINTOSH WINDOWS

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: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.