EXCEL FUNDAMENTALS - PART 1
Car Loan Practical Exercise #1
(to be completed before our next class meeting)
In this assignment you will create a spreadsheet for analyzing car loans. The spreadsheet you create should look something like the following:
The user of this spreadsheet should be able to enter values for the Purchase Price, Down Payment and Trade-in (in the appropriate cells) and have the spreadsheet automatically calculate the Amount Financed. Similarly, the user should then be able to enter the Term of the Loan (in years) and the annual Finance Rate and have the spreadsheet automatically calculate the Monthly Payment, Total Payments and Finance Charge.
SOME HELPFUL HINTS
You should use the following EXCEL function to calculate the Monthly Payment figure:
=PMT( i , t , -p )
where "p" represents the principal or the amount financed, "i" represents the interest rate and "t" represents the term of the loan. Keep in mind that since car loan payments are typically made on a monthly basis "i" should indicate the monthly interest rate (i.e., (Annual Finance Rate)/12) and "t" should indicate the term of the loan in months (i.e., (Term of the loan in years)*12). Note that "i", "t" and "p" can be numbers, the addresses of cells containing values, or formulas involving numbers and the addresses of cells containing values.
The Total Payments figure should equal the Monthly Payment figure times the number of monthly payments that must be made.
The Finance Charge figure should equal the Total Payments minus the Amount Financed.
Make the spreadsheet display the dollar signs ($) and two decimal places on all currency figures. NOTE: Sometimes when you set a currency format some cells on the screen which previously contained numbers will now display asterisks (i.e., "##########"). Don't panic! This simply means that EXCEL is unable to display these numbers in the format you asked for because the column is not wide enough. If this happens, simply increase the width of the column.
The Finance Rate should be displayed in a percent sign format.
Excel Fundamentals - Part I
By the end of this session you should know how to:
* Enter labels, numbers and formulas
* Edit and delete cell entries
* Format cells
* Left justify, right justify, center and center across columns
• Identify the icons on the Standard and Formatting toolbars
* Change column widths and row heights
* Insert/delete and hide/unhide rows and columns
* Insert, delete and change the names of worksheets in a workbook
• Change the zoom factor on a worksheet
• Hide and display various toolbars
• Insert, format and use a text box from the Drawing toolbar
* Save your workbook to disk