CSci 105

Introduction to Computers


Laboratory Assignment # 6

Advanced Spreadsheets

1. Write an EXCEL program to take three input parameters for an auto loan, the Amount of the loan, the Annual Interest, and the Period of the loan, and then compute the monthly payment and the repayment schedule indicating the Interest, Principle, Total Interest, and Remaining Principle. Below is a suggested format for the spreadsheet, but please show all $ values to the nearest ¢. Please use the input parameters shown in B4..B6. Use your own name in the Loan Schedule.

Your completed homework MUST show two decimal places after all of the dollar amounts

[Remember: Type in your cell labels (Amount of loan, etc.) and contents in the same exact location as shown on the image--if you don't, your formulas will not work!

Cell D5 should contain =PMT(B5/12,B6*12,-B4)--This formula must be in this exact order

Cell A9 should contain 1

Cell B9 should contain =B4*B5/12 (First month's interest = amount of loan * annual intest rate / 12)

Cell C9 should contain =D5-B9 (Principle = Monthly payment - Interest)

Cell D9 should contain =B9

Cell E9 should contain =B4-C9 (Amount of loan - Principle)

Cell A10 should contain =A9+1 (This will automatically number the months for you)

Cell B10 should contain =E9*$B$5/12 (Second month's interest = previous month's remaining principle * annual interest rate / 12)

Cell C10 should contain =$D$5-B10 (Principle = annual interest rate - interest)

where $B$5 and $D$5 are "absolute addresses"]

Cell D10 should contain =D9+B10

Cell E10 should contain =E9-C10

Copy the contents of cells B10..E10 down to the 36th. month on your sheet.]

2. Assume you inherit a bundle and decide on a fancier car on which you must borrow $25,000. However, on this loan you can get a 9.5% annual rate and spread it out over 5 years. Recompute and print the loan schedule, and write a short paragraph in which you discuss these two schedules as examples of "what if" problems-- i.e., Spreadsheets are great to use for "what if" problems like these because, Blah, blah, blah.