These functions are more complex to use than the above Loan functions, but are much more general. For all of these functions, all amounts that you pay are negative, all amounts you receive are positive. It is critical that you follow this standard, or the results will be very different from what you want.
The payments and the interest rate must be for the same period. If you enter the interest rate as a yearly value, the payments must also be yearly and for a loan are negative because you pay them.
PV or Present Value is the amount you start with, FV or Future Value is what is left at the end. For a normal loan, the PV is the amount of the loan you receive and is positive because you get it. The FV is zero because it will be completely paid off at the end
There are a lot of sources of further discussion of the use of these formulas in books on money management, and on the net. Here is what Napier has for these functions:
|
FV |
|
|
|
FV(pv, pmt, i, n) |
|
|
pv: start, pmt: payments, i: rate, |
|
|
n: periods |
|
|
Future balance of |
|
|
an account starting with pv |
|
|
balance, making pmt payments |
|
|
Values are - if you pay, + if |
|
|
you receive, for all entries |
|
|
i is per period. If 8.5%, use 8.5 |
|
PV |
|
|
|
PV(fv, pmt, i, n) |
|
|
fv: end, pmt: payments, i: rate, |
|
|
n: periods |
|
|
Present balance of |
|
|
an account needed to get to a |
|
|
fv balance, in n periods, |
|
|
making periodic pmt payments |
|
|
Values are - if you pay, + if |
|
|
you receive, for all entries |
|
|
i is per period. If 8.5%, use 8.5 |
|
NPER |
|
|
|
NPER(pv, fv, pmt, i) |
|
|
pv: start $, fv: end $, pmt: payment |
|
|
i: rate |
|
|
Number of periods to get to |
|
|
a fv balance with a pv starting |
|
|
balance making pmt payments |
|
|
Values are - if you pay, + if |
|
|
you receive, for all entries |
|
|
i is per period. If 8.5%, use 8.5 |
|
PMT |
|
|
|
PMT(pv, fv, i, n) |
|
|
pv: start, fv: end i: rate, |
|
|
n: periods |
|
|
Payments to an account |
|
|
starting with pv balance to get |
|
|
to fv balance making pmt payments |
|
|
Values are - if you pay, + if |
|
|
you receive, for all entries |
|
|
i is per period. If 8.5%, use 8.5 |
|
RATE |
|
|
|
RATE(pv, fv, pmt, n) |
|
|
pv: start, fv: end, pmt: payment, |
|
|
n: periods |
|
|
Interest rate for an |
|
|
account starting with pv balance |
|
|
to get to fv balance, making pmt |
|
|
payments for n periods. |
|
|
Values are - if you pay, + if |
|
|
you receive, for all entries |
|
|
Rate is per period. If 8.5%, use 8.5 |
|
BAL |
|
|
|
BAL(pv, i, n, p) |
|
|
pv: loan amount, i: rate, n: periods |
|
|
p: periods paid. |
|
|
= Balance at period p |
|
|
Values are - if you pay, + if |
|
|
you receive, for all entries |
|
|
Balance remaining after p periods |
|
|
i is per period. If 8.5%, use 8.5 |
|
TOTI |
|
|
|
TOTI(pv, i, n, s, e) |
|
|
pv: loan amount, i: rate, n: periods |
|
|
s: start, e: end periods to use |
|
|
Interest paid from periods s to e |
|
|
Values are - if you pay, + if |
|
|
you receive, for all entries |
|
|
i is per period. If 8.5%, use 8.5 |
|
TOTP |
|
|
|
TOTP(pv, i, n, s, e) |
|
|
pv: loan amount, i: rate, n: periods |
|
|
s: start, e: end periods to use |
|
|
Principle paid from periods s to e |
|
|
Values are - if you pay, + if |
|
|
you receive, for all entries |
|
|
i is per period. If 8.5%, use 8.5 |
These functions are under the Mony drop down key.
|
DNPV |
|
|
|
|
DNPV( i ) = Data set NPV |
|
|
|
i: int rate. NPV of Data Set |
|
|
|
values as flows at reg intervals |
|
|
|
for an investment, value are + |
|
|
|
for flows in, - for flows out |
|
|
|
i is %, eg 8.5% is 8.5 |
|
|
DIRR |
|
|
|
|
Data set IRR |
|
|
|
IRR using the Data Set |
|
|
|
values as flows at reg intervals |
|
|
|
for an investment, value are + |
|
|
|
for flows in, - for flows out |
|
|
|
DIRR value is %, eg 8.5 = 8.5% |
|
|
DTRtn |
|
|
|
|
Total return on data percents |
|
|
|
Data set should contain a set of |
|
|
|
periodic returns. This calculates |
|
|
|
the total return over all periods |
|
|
|
Entries are %, eg 8.5% is 8.5 |
|
|
DARtn |
|
|
|
|
Geo avg of data percents |
|
|
|
Data set should contain a set of |
|
|
|
periodic returns. This calculates |
|
|
|
the geometric average return |
|
|
|
Entries are %, eg 8.5% is 8.5 |
|
|
D2Rtn |
|
|
|
|
Convert Data Set to |
|
|
|
returns for each period. Set is |
|
|
|
sequential balances at regular |
|
|
|
periods, eg monthly, daily |
|
|
|
This replaces all the data with |
|
|
|
returns as %, eg 8.5% is 8.5 |
|
|
D2Bal |
|
|
|
|
Convert Data Set to |
|
|
|
balances for each period. |
|
|
|
Answer is starting amount |
|
|
|
Data set is sequential returns |
|
|
|
at regular periods, eg monthly |
|
|
|
Returns are %, eg 8.5% is 8.5 |
|
|
|
This replaces all the data with |
|
|
|
balances for each period |
|
|
|
|
|