4. Understanding Loans

A Loan Calculator
We created a Loan Calculator using some basic loan data - amount, interest rate, and duration in years - along with Cell Names and the PMT Financial Function. The PMT Function is a powerful tool in Excel. Knowing how to use it will help you to analyze loans to see whether the conditions of a particular loan are good for you or not.

We used a interesting technique, Naming Cells. This operation has two very distinct advantages: first, it allows us to create a formula using words in English rather than simply using Cell References, which can sometimes be confusing; second, the cell's name actually is an Absolute Reference, wherever and however it is used in your worksheet!!

Using a Data Table for Analysis
Seeing how spreadsheet answers vary with changes to the spreadsheet's input is called 'Sensitivity Analysis'. It's a way of asking Excel 'what-if' questions, where you want to see a variety of answers. A DATA TABLE allows you to see and compare the results of a formula for several different values. A one-input data table lets you vary the value in one cell. A two-input data table lets you vary the values in two cells.

We created a one-input data table based on different interest rates to help us understand the effect of each rate on the Monthly Payment, the Total Interest, and the Total Cost of a loan. Understanding the answers, and knowing how they affect you, helps you to make smarter financial decisions.

Materials Covered: MS Excel - Project 4:
Financial Functions, Data Tables, and Amortization Schedules, pp 266-294


First CaseGrader Assignment

You will need CaseGrader: Microsoft Office Excel 2007 to do the following:

Case 1:

1. Read CaseGrader textbook pages ix in the text to get the overview of CaseGrader exercises.
2. Go to the CoursePort Website at: http://login.course.com
3. If you haven't yet done so, add CaseGrader and your text's Keycode to your account, followed by Joining the class with the Class Code: 7DD18525.
4. Select CaseGrader and go to the page containing a listing of the Cases
5. Select Case 1: Getting Started with Excel, Newleaf Paper Company - Calculating Employee Bonuses
6. Download and save Case 1
7. Follow the directions exactly while working on the spreadsheet.
8. When you've completed all the steps, save your file, then upload and submit your worksheet for grading.
9. From your GradeBook, generate the Summary Report to see a detailed report of how you did on each step.
10. Note: If you find errors that you made, you may make corrections to your worksheet and submit your file more than once, until you're satisfied with the results. I will only count the highest grade, so you have nothing to lose by trying until you succeed!
11. In your journal, write a short note letting me know what you think of the value of this experience. Thanks.