Microsoft Office/Loan Payment Amortization Schedule

Objectives

The Student Will Be Able To (TSWBAT):

Format sections of a worksheet for easy readability
Create Global references and use them in a formula
Determine the monthly payment of a loan with the PMT function
Determine the present value of a loan using the PV function
Analyze data in a worksheet
Create an Amortization schedule
Create hyperlinks in a worksheet
Set a print area and print part of a worksheet

Vocabulary

Hyperlink - links from one document to another, web page to another, or part of a document to another part of the same document

Global Cell Name - A name given to one cell in a workbook, that can be used in any sheet

PMT Function - determines the monthly payment on a loan based on the rate, payment amount and loan amount.
-PMT(Monthly_interest_rate/12, 12*years, loan_amount)

Data Table - a range of cells that shows the answers to a formula

one-input data table - one value in the formula varies
two-input data table - two values in the formula vary
Input Values - The values that are put into the formula for a data table

Amortization Schedule - A table of values that shows the beginning balance of a loan, ending balance for a loan, amount paid toward the principle, and amount paid toward the interest at the end of each year.

PV Function - determines the present value of a loan, or how much you still need to pay at the end of a given amount of time

Annuity - a series of fixed payments such as your monthly payments on a loan

Lesson

Payment Calculator

Titles

Titles
- Type "Payment Calculator" in cell B1
  - Merge and Center cells B1:E1
  - Format the font as you like - size 16pt
- Type "Date" in cell B2
- Type "Item in cell B3
- Type "Price in cell B4
- Type "Down Payment" in cell B5
- Type "Loan Amount" in cell B6
- Type "Rate" in Cell D2
- Type "Years" in cell D3
- Type "Monthly Payment" in cell D4
- Type "Total Interest" in cell D5
- Type "Total Cost" in cell D6
Formatting
- Make each column as wide as it needs to be for the labels
- Bold all label text
Change the sheet name to "Car Loan"
Save the spreadsheet to your storage space calling it "Loan Calculator"

Function for Today's Date

In cell C2 type:
=now()

Formatting

Format the first section of your spreadsheet with:

an outside border
column dividers
a fill color
- Go to the home tab
- go to the font group
- click on the borders drop down button
- choose more borders
make sure all colors go together and you can read the text

Format the data as the proper type

Dollars
- Price
- Down payment
- Loan amount
- Monthly payment
- Total interest
- Total cost
Percent
- Rate
General number
- Years
Date
- Date

Enter Loan Data

Find a Car you want to purchase and the price

go to Kelley Blue Book
- Find a NEW car you want to buy
- Find the price
go to BankRate.com
- Find the interest rate for a car loan
- Remember the number of years for that interest rate
Determine if you have any money to put as a down payment NOW
Type in the data you have collected into your spreadsheet
- C3 = name of car you are purchasing
- C4 = Price for the car
- C5 = down payment if you have one, 0 if you don't
- E2 = interest rate for the car loan
- E3 = number of years for the loan

Create cell Global Names

Click on the cell you wish to name - C4
Go to the Formulas tab
Go to the Define Names group
Click on Define Name
Make sure the name is what you want and click OK
- C4 = Price
- C5 = Down_Payment
- C6 = Loan_Amount

etc...

Compute loan amount

The amount of a loan you need to take out is the price of the car minus the amount you will pay when you purchase the car or the down payment.

go to cell C6
= price - down_payment

or

= C4 - C5

Determine the monthly payment

The monthly payment is how much you will pay each month to pay off the price of the car. This is computed with the interest rate of the loan, the years you will take to pay off the loan, and the total loan amount.

go to cell E4
-pmt(rate/12, 12*years, loan_amount

or

-pmt(E2/12, 12*E3, C6

Determine the total interest

The total interest is the total amount the bank is going to make on loaning you the money to purchase your car. For this computation you will need to know how much the loan amount was, and how much you paid in total over the years. That is figured by taking the years times 12 to find the months, and then multiplying by the monthly payment.

go to cell E5
=12*years*monthly_payment-loan_amount

or

=12*E3*E4-C6

Determine the total Cost

The total cost of the car is how much you paid including the down payment and interest. This is figured by adding the price of the car with the total interest.

go to cell E6
=price+total_interest

or

=C4+E5

Analyze your data

Go back to kelly blue book
find the price of the same car that is one year old
Go back to BankRate.com
find the interest rate for a used car
Change your data
Analyze the difference in how much total you pay for the used car versus a new car

Interest Rate Schedule

Titles

B7 = Interest Rate Schedule
- format to same fonts as B1
- center across B7:E7
B8 = Rate
C8 = Monthly Payment
D8 = Total Interest
E8 = Total Cost

make all titles bold

Create a series with the fill handle

B10 = 4.00%
B11 = 4.25%
select cells B10:B11
use the fill handle to drag down until you reach 8.00%

Add Initial Values

C9 = E4
D9 = E5
E9 = E6

Define the Data Table

select B9:E26
go to the Data tab
go to the Data Tools group
click on the what-if analysis drop down
choose Data Table
click in the column Input Cell
click on the rate from cell E2
click on OK

Format

select cells B8:E26
shade and border similar to the section above but a different color

Conditional Formatting

select B10:B26
go to the HOME tab
go to the styles group
click on the conditional formatting drop down
click on Highlight cell rules - equal to
click in the format cells that are EQUAL TO
click on E2
click on OK

Change the rate in cell E2 and watch the color section change.
Notice the color rate changes, but the values in the data table does not. You would have to re-select the cells and re do the data table steps.

Amortization Schedule

Titles

G1 = Amortization Schedules
- center across G2:K2
- same font style and color as the other two sections
G2 = year
H2 = Beginning Balance
I2 = Ending Balance
J2 = Paid on Principle
K2 = Interest Paid
Wrap text for H2:K2

Year Series

G3 = 1
use the fill handle to fill down to G26
click on the smart button - auto fill options
click on fill series

Beginning Balance Formula

H3 =C6

Ending Balance Formula

I3 =PV($E$2/12, 12*($E$3-G3), -$E$4)
I4 =IF(H4<=0,0,PV($E$2/12, 12*($E$3-G4), -$E$4))

Paid on Principle Formula

J3 =H3-I3
J4 =H4-I4

Interest Paid Formula

K3 =12*$E$4-J3
K4 =IF(H4<=0, 0, 12*$E$4-J4)

Fill Table with formulas

Select I4:K4
fill down

Finish the Beginning Balances

H4 = I3
fill down

Amortization totals

I28 = Subtotal
I29 = Down Payment
I30 = Total Cost
J28 = auto sum column J
K28 = auto sum column K
K29 = C5
K30 = J28 + K28 + K29

Formatting

format the same as the other sections just a different color

Hyperlinks

Place a Graphic

go to cell H25
Find a picture in clip art or the internet that represents your car
Go to the insert tab
go to picture or clip art depending on what you are using
Find your picture

Create a Graphic as a link

Right Click on the picture and go to Hyperlink
In the address place the URL for the web site - http://www.kbb.com
Find the web page for the car you are purchaseing

Set a print area

Select the area you want to print - click and drag
Go to the Page Layout Tab
Go to the Page Set up group
Click on the drop down for Print Area
Click on Set Print Area
Print Preview and you will see only that area will print

You can clear the print area or change the print area from this same drop down.

Project

Edit the spreadsheet you created to buy your dream home you find on http://www.remax.com/
Find the loan percentage from a bank such as http://www.boxhomeloans.com/
Decide what type of mortgage you will take out to pick the correct loan interest rate
Change the graphic on the page and have it link to your dream home from remax.com

Rubric