How much will you need each month for your car payment? How much could you save
by paying off a credit card balance in two years
instead of four years? How much should you save
each month to pay for college tuition? Microsoft Excel formulas make it simple to get
answers to these and other financial
questions.
Excel lets you work with
formulas, also called functions.
Let's work with some of them.
| Calculating your mortgage payment |
Activity 1
Let's say that you're shopping for a home. You
want to figure out how much you would pay each
month for a $90,000 mortgage over 30 years, with a 6
percent annual interest rate.
You can do that by using an Excel function and
by supplying arguments.
An argument is information that tells the
function what to
calculate. In the example below, you will use the PMT
function. The PMT function calculates loan payments using
regular, identical payment amounts and an
unchanging interest rate.
- In any cell in
your new worksheet, type:
- The equal sign
(=) tells
Excel that this is a formula. Within the
parentheses are the arguments, separated by
commas.
- The interest rate of
6
percent annually is divided by 12. That is because you
are calculating monthly payments, and so you need a
monthly interest rate.
- The number of payments
over the 30-year mortgage period is multiplied
by 12, because you will make 12 monthly payments
each year.
- The present value is the
starting amount of the mortgage loan before
interest. (This value is entered without a
thousands separator: 75000 instead of 75,000.)
- Press ENTER to display the monthly mortgage
payment: ($449.66). Since the result is a sum
that must be paid out, it is displayed as a
negative number. Negative numbers are shown in d by red
and in parentheses.
-->> Print your spreadsheet, sign it, and place it in your
folder or portfolio.
| Finding the costs of paying off a credit
card sooner or later |
Activity 2
You can also use the PMT function to find how
much difference it would make, in monthly
payments, to pay off a credit card
balance faster. How much would you save if you
paid the card off in two years, for example, instead
of four years?
Imagine a balance of $5,400 and a
17% annual interest rate. Suppose also that nothing
more will be charged to this account while the
balance is being paid off.
To calculate what you need to pay each month
in order to be finished in two years
- In any cell in a worksheet, type:
=PMT(17%/12,2*12,5400) (Review what each number means)
The arguments are the same as before:
- The interest rate is
again the annual rate, 17 percent, divided by 12
to give a monthly rate.
- The number of payments
over the two year period is multiplied by 12,
because you will make 12 monthly payments each
year.
- The present value is the
credit card balance due before interest, entered
as 5400.
- Press ENTER to display the monthly payment.
As you see, it will cost ($266.99) a month to
pay the balance off in two years.
-->> Print your spreadsheet, sign it, and place it in your
folder or portfolio.
Activity 3
Using the formula that you have learned,
calculate the monthly payment you'll need to pay
you loan off in four years.
Work out the answer on your own. Then compare
your results with those in the Answer
Sheet.
-->> Print your spreadsheet, sign it, and place it in your
folder or portfolio.
Activity 4
Now that you
know your monthly payments for two and four years,
compare how much you will pay over two years and
over four years. What is the difference?
Work
the activity on your own and compare your results
with those in the Answer
Sheet.
Activity
5
Click on
the arrow to complete the Reading Challenge. 
Project
Work
with two people who are making monthly payments on
a house, car, credit card or other item. Figure
the totals they will pay by paying off their
balances in one, two, and three years. Print your
calculations, sign them and place them in your
folder or portfolio.
