XL2000: Detailed Solutions for Interest Rate Formulas (214005)
The information in this article applies to:
This article was previously published under Q214005 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 Microsoft Excel Help to find detailed information about one of the functions listed above, these two formulas are referenced. However, Microsoft Excel Help does not contain solutions for the individual functions. 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) cash flow. 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).
Modification Type: | Major | Last Reviewed: | 10/8/2003 |
---|
Keywords: | kbhowto KB214005 |
---|
|