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