Computer Science and Computer Engineering 120
Computerized Information Systems
Fall 2006
Lab 5
The goal for this lab is to help you develop and demonstrate your skills using some of the Excel financial functions: PMT, FV, PV, RATE, and NPER and again improve your skills with spreadsheets The PMT and FV function are discussed on page 162. All the functions have been (will be) discussed in class.
Download the file partially completed workbook lab05.xls from the downloads/excel folder in the class web site: http://www.cs.plu.edu/courses/csce120/fall06/download/excel/
This sheet is intended to allow the user to do a side by side comparison of three different cars and/or different purchase options on the same car. It is intended to be fairly complete. (First time buyers may be surprised the cost of a car is much more than the negotiated price.) The line with only a "-" in column A is there in case there are any additional discounts to the car price. The lines with only a "+" are for any additional taxable or after tax add on expenses. Complete formulas for the "Taxable subtotal", "Sales tax @", "Total", "Monthly payment", "Total payments", and "Total interest paid" rows. Format the cells that have formulas differently than the cells that user can supply values. Remember that the cells for the buyer's name and address, the make, model, and year are user changeable. Of course, format the sheet appropriately.
The sheet is designed as an amortization table for the #1 car on the previous table. This sheet has absolutely no user input. Instead get the "Amount borrowed", "Interest rate", and "Monthly payment" from the previous sheet. (E.g. for the amount borrowed, click in the cell needing the formula, type "=" in the normal manner and then click on the "Car" tab. Next click on the appropriate cell and finally press "Enter".) You will need to write a formula for the "Number of months" that uses a cell from the car page.
Fill in formulas for 72 months so the sheet can handle the common 6 year loan.. This leads to an interesting problem. Suppose the loan is for only 5 years. The balance in month 60 should be zero. (This is an easy check on your work.) But what happens in months 61 - 72? If you don't do anything, they will show a negative balances. That looks strange and detracts from your work. There are several solutions. One of them is to use conditional formatting. In the month column, if the month number is > than the last payment month, conditionally format to use the same color for the font and the background, effectively make the unwanted rows invisible. For the other 3 columns, you can use conditional formatting as well. Only this time you will have to select "Formula Is" in the first drop down box and write a formula having the general idea "if the month number > the length of the loan in months" use the conditional format to hide the values.
Add an appropriately named page to workbook after the "Amortization" page. Like that page, there is no user input on this page. Near the top of the page create labeled cells for the amount of the loan on Car 1 and the interest rate using the same techniques as on the previous page. The create a small table with column headings "Length of Loan", "Monthly payment", "Total Payments", and "Total Interest". In the year column include the years, 1, ..., 6. After completing the table create a chart showing the loan payments and total interest for each the 6 years. Hint: Because the category labels 1, ..., 6 are numbers, Excel would normally plot them as a data range instead of using them as categories. One solution is to move the label "Length of Loan" up one row and leave the cell where it would normally be blank.
The page is designed to allow the user to pick the appropriate section, supply the necessary values, and get the results. For example, in the "Rate of return" section, the user would supply the initial amount, the final amount, the regular payment, the number of times a year payments are made and interest is compounded, and the number of years for which the payments are made. The spreadsheet will then calculates the rate of return per period and the rate of return per year (APR).
Your spreadsheets will be graded on meeting the requirements, correctness, general looks, and impression they leave the reader.
Midnight, Wednesday, Oct. 11. There will be a 24 hour grace period. After the grace period late submittals will not be accepted except with written permission from the instructor and will be subject to a 20% per day. (Late assignments turned in over 24 hours after the due date are 2 days late and are subject to a 40% penalty.)
Revised: Oct. 9, 2006