37
Note that, with mortgages, we always assume payments are in arrears and that
the type argument is 0. Also note that the rate of interest (and the payments) are
monthly. Therefore, the term of 20 years must be converted to months.
You can check the answer by using the calculated answer to determine the rate
on a mortgage of $366,433.74 over 240 months. The following formula returns
0.45%:
=RATE(240,-2500,366433.74,0,0)
EXAMPLE 22
I currently owe $150,000 on a mortgage, and make payments of $1,900 per month.
The current interest rate is 0.45% per month. How long will it take to repay the
loan?
Function required: NPER(rate, pmt, pv, fv, type)
The following formula returns 97.76:
=NPER(0.45%,-1900,150000,0,0)
Because interest and payments are monthly, the formula returns the amortiza-
tion period in months. This answer, although correct in mathematical terms, has a
practical implication. Payments are actually made on exact monthly anniversaries.
This calculation implies that the loan somehow gets repaid 0.76 of the way through
the 98th month. In reality, you have a choice: make an additional payment at the
end of 97 months, or make a reduced level payment after 98 months. These options
can be calculated using the FV function.
To calculate the additional payment at the end of 97 months, calculate the
amount due using this formula (which returns –$1,429.85):
=FV(0.45%,97,-1900,150000,0)
Therefore, the final payment after 97 months is –$3,329.85 (that is, the normal
payment of –$1,900 plus –$1,429.85).
To calculate the reduced payment after 98 months, use this formula (which
returns +$463.72):
=FV(0.45%,98,-1900,150000,0)
Therefore, the final payment after 98 months is –$1,436.28 (that is, the normal
payment of –$1,900 plus $463.72).
A relatively frequent problem arises where the payment is less than the
amount of the interest portion on the outstanding balance.In this example,
the outstanding loan is $150,000, and interest in the first month is $675
($150,000 * 0.45%).If the payment is less than this amount,the outstanding
310
Part III: Financial Formulas