Computer Science and Computer Engineering 115
Solve it with a Computer
Fall 2006



Lab 5

Financial functions

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 the IF function as well as improve your skills with spreadsheets. The PMT and FV function are discussed on page 162. All the financial 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/csce115/fall06/download/excel/

The "Car" sheet

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 "Amortization" sheet

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. For example, 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 the IF function. After you are sure that you have the correct calculations for a 60 month loan, enclose those formulas inside a function. Pretend that a formula for a 60 month loan was
=B5 +C5 + K4
(Clearly that formula is not correct.) It could be replaced by something like
=if(month number is too large,"", B5 +C5 + K4)
Of course, you would have to write an condition that Excel would understand for "month number is too large". (Remember that while it would be very unusual, loans could be as short as 1 month.)

Possible extra credit. (5 extra credit points)

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 "Length of Loan" 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.

Advisor

The page is designed to allow the user 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).

Additional Requirement

• Remember to use formulas whenever appropriate. Assume the paper grader will change the data before grading the sheet. Your spreadsheet should correctly solve the problem with the new data.
• Make sure name and section are on every page.
• Each page must be formatted in professional manner. All user input cells must be colored in a manner makes it clear which cells the user can type in.

Grading:

Your spreadsheets will be graded on meeting the requirements, correctness, general looks, and impression they leave the reader.

Submitting your workbook

1. Use the URL http://www.cs.plu.edu/submit/login.php
2. Select your class (csce115-brink, Fall 2006) from the drop down box.
3. Enter your submit system username and password.
4. Click "Login".
5. In the add Assignment box, browse to find the workbook for lab 5.
6. Select "Lab 5" for the assignment and then click "Upload".
7. Double check to make sure the file has been uploaded.
8. Click "Sign Off" near the top right hand corner of the window.

Due date

Midnight, Monday, Oct. 16. 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