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

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.

 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)` 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.

### Activity 3

Using the formula that you have learned, calculate the monthly payment you'll need to pay you loan off in four years.

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?

Click on the arrow to complete the Reading Challenge. 