PMT
Function
The PMT function computes a payment
amount given the principal (loan amount), interest rate, and the term of
the loan.
=PMT(interest_rate, term,principal)
Most interest rates are stated
on an annual basis. Given an annual interest rate, this function
gives an annual payment. To compute a monthly payment, the interest
rate must be a monthly rate and the term must be stated in months.
To find the monthly interest rate, divide the annual interest rate by 12.
If the term is stated in years, multiply by 12 to change it to months.
The formula to compute a monthly
payment would be:
=PMT(interest_rate/12, term
in months,principal)
Note the formula in the worksheet
below. The interest rate is divided by 12. The term was already
stated in months, so B3 was entered as the term. There was
no need in this situation to multiply by 12. However, if the term
was stated in years, B3 would need to be multiplied by 12 to convert the
years to months.
Notice that the result
is a negative number. To make the result a positive value, you must
put a minus sign between the equal sign and the PMT: =-PMT(B2/12,B3,B1)
FV
Function
You use FV function to calculate
the future value of your money when you invest it in equal payments over
a number of years.
=FV( interest_rate, term,
payments)
Suppose you deposit $200 into
your bank account at the end of each month for 15 years. How much
money will you have at the end of 15 years, assuming an 5% annual interest
rate compounded monthly? The function to calculate this and the result
are shown in the worksheet below.
Notice that as
in the PMT function, the interest rate and the term must match; both must
be stated in months OR both must be stated in years. If the
interest is compounded yearly instead of monthly, the interest rate and
term should be stated in years.
Notice again that
the result is a negative number. To make the result a positive value,
you must put a minus sign between the equal sign and the PMT:
=-FV(B2/12,B3*12,B1)
The FV function
is covered in your textbook on pages 7.19 through 7.21.
| Assignment:
Complete the four
problems below. Use any filename you wish, but please remember to
include your name in the filename. Send all four problems to me in
one e-mail message with the subject Functions Assignments.
Problem 1: Click
here to download the file for this problem. The instructions
are on the worksheet.
Problem 2:Click
here to see how to create this problem. The instructions
are given in the example. Remember to round where necessary.
If you'd like to print it, just click the print button on your browser's
toolbar.
Problem 3:
Your dream is that one day your son or daughter will be able to attend
a university. For the next ten years, you plan to make $500 monthly
deposits to a savings account at a local bank in order to have $100,000
for your child's education. The account pays 6% annual interest,
compounded monthly. Create a worksheet that uses a financial
function to show the future value of your investment. At the end
of ten years, will be funds be sufficient to cover the tuition?
In another column on the same
worksheet, calculate the amount you should save each month in order to have
$100,000 in 10 years at 6% interest. Problem 4:Click
here to see an example of this problem. The instructions are
given in the example. |
|
