MY EXCEL LOAN SHEET
Part 3
A WebTask for Adult Learners

Ask for help getting started. Continue to ask for help when you need it!

In this task, you will complete the following objectives:
  • Open and save your new Excel file
  • Enter a formula to calculate payment amounts with interest
  • Change the information to calculate new amounts
  • Compare payment options on credit cards
  • Complete a reading challenge
  • Calculate different pay-off figures on two accounts, for one, two and three years

You will evaluate yourself (compete rubrics) at the end. Click on the arrow to read the rubric (evaluation) items at the end. 
(To come back here, click the BACK button on your browser.)


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.

  1. In any cell in your new worksheet, type:
=PMT(6%/12,30*12,75000)

  • 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.)
  1. 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

  1. In any cell in a worksheet, type:
=PMT(17%/12,2*12,5400) (Review what each number means)

=PMT

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.
  1. 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. 

Unlimited Learning, Cortez, CO 2004
Permission granted to use our materials for educational purposed only, as long as credit is given to the source.