Microsoft Office/Loan Payment Amortization Schedule

< Microsoft Office

Objectives

The Student Will Be Able To (TSWBAT):

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

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

Function for Today's Date

In cell C2 type:
=now()

Formatting

Format the first section of your spreadsheet with:

Format the data as the proper type

Enter Loan Data

Find a Car you want to purchase and the price

Create cell Global Names

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.

or

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.

or

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.

or

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.

or

Analyze your data

Interest Rate Schedule

Titles

make all titles bold

Create a series with the fill handle

Add Initial Values

Define the Data Table

Format

Conditional Formatting

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

Year Series

Beginning Balance Formula

Ending Balance Formula

Interest Paid Formula

Fill Table with formulas

Finish the Beginning Balances

Amortization totals

Formatting

format the same as the other sections just a different color

Place a Graphic

Set a print area

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

Project

  1. Edit the spreadsheet you created to buy your dream home you find on http://www.remax.com/
  2. Find the loan percentage from a bank such as http://www.boxhomeloans.com/?gclid=CJXO0KDv3p8CFQwpawodoFwTHw
  3. Decide what type of mortgage you will take out to pick the correct loan interest rate
  4. Change the graphic on the page and have it link to your dream home from remax.com
This article is issued from Wikibooks. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.