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