Formulas and Functions

Chapter 6 Financial Functions 131
 present value: The value of the initial investment, or the amount of the loan or
annuity, specied as a negative number. present-value is a number value. At time
0, an amount received is a positive amount and an amount invested is a negative
amount. For example, It could be an amount borrowed (positive) or the initial
payment made on an annuity contract (negative).
 future-value: An optional argument specifying the value of the investment or
remaining cash value of the annuity (positive amount), or the remaining loan
balance (negative amount), after the nal payment. future-value is a number value.
At the end of the investment period, an amount received is a positive amount and
an amount invested is a negative amount. For example, It could be the balloon
payment due on a loan (negative) or the remaining value of an annuity contract
(positive).
 when-due: An optional argument that species whether payments are due at the
beginning or end of each period. Most mortgage and other loans require the rst
payment at the end of the rst period (0), which is the default. Most lease and rent
payments, and some other types of payments, are due at the beginning of each
period (1).
end (0 or omitted): Payments are due at the end of each period.
beginning (1): Payments are due at the beginning of each period.
Example 1
Assume you are planning for your daughter’s college education. You have $50,000 to set aside in
a savings account today and can add $200 to the account at the end of each month. The savings
account is expected to earn an annual interest rate of 4.5%, and pays interest monthly. You believe
you will need to have set aside $150,000 by the time your daughter reaches college.
Using the NPER function, you can determine the number of periods you would need to make the
$200 payment. Based on the assumptions given, it would be approximately 181 periods or 15 years,
1 month.
periodic-rate payment present-value future-value when-due
=NPER(B2, C2, D2,
E2, F2)
=0.045/12 -200 -50000 150000 1
Example 2
Assume you are planning to purchase your uncle’s mountain cabin. You have $30,000 to use as a
down payment today and can aord to make a monthly payment of $1,500. Your uncle says he is
willing to lend you the dierence between the cabin’s sale price of $200,000 and your down payment
(so you would borrow $170,000) at an annual rate of 7%.
Using the NPER function, you can determine the number of months it would take you to repay your
uncle’s loan. Based on the assumptions given, it would be approximately 184 months or 15 years,
4 months.