‹date/time›
Click to edit Master text styles
Second level
Third level
Fourth level
Fifth level
‹footer›
‹#›
The loan analysis function includes utilizing the PMT Function, the IPMT and PPMT functions, and the Goal Seek Command.  Mortgage analysis includes differentiating between long-term and short-term mortgages, as well as knowing the difference between relative and absolute addresses.
The power of Excel incorporates the Fill Handle, pointing with the mouse or arrows keys, and understanding basic functions such as Paste.  The Grade Book is an excellent way to understand how to manipulate the Average, Max, Min, and IF Functions.  The VLookUP and HLookUP functions also help in displaying various data.  Large spreadsheets will be explained thoroughly using the freezing panes and scrolling techniques.  This picture was taken from www.microsoft.com
Loans need to be analyzed in order to make sure monthly payments can be afforded.  Analysis will be able to tell you if you want to settle for a less expensive car or work harder in able to keep up payments.
Using an empty template, a template with the categories already filled in but no real values, the user can plug in specific data.  The template requires the original price of the car, the amount of the down payments, any possible rebates, the length of the loan, and the interest rate.  The user can then use the PMT Function to get the spreadsheet to figure out monthly payments.
Placing all the information in a spreadsheet allows different variables to be adjusted.  Looking at possible payment schedules allows the user to determine whether or not he needs to lengthen the life of his loan at a lower interest rate, for example.
Instructors should use this quick review question to assess how much students have understood up to this point.
Instead of using specific values, Excel allows arguments for the PMT Function to be entered as cells.  This was the computed payment can be based on information provided on a different worksheet.  This shows that the terms of the loan are displayed in specific cells.  The principle amount of the loan is always entered as a negative number because it is seen as the outflow of cash for the bank.
Have students use actual figures in place of cell references to make sure they understand which values go in which areas in the equation.  Later on in the chapter, actual cell references will be used.
The previous spreadsheet shows us various possible monthly payments.  Yet, if there is an exact amount that user cannot pay more than, then the Goal Seek Command is used to determine the maximum amount the user can borrow.  The Goal Seek Command allows the user to set the desired output so that the required inputs can be displayed.  This way, the maximum amount of loan the user can take out and still make a specific payment, is shown, along with the interest payment.  Only one input can be changed by the user at a time.
There are many quick tips users can look at in order to better utilize Microsoft Excel.  The above link goes to the Microsoft web site with published tips for the program.  Allow students to browse through the tips in order to better understand how to manipulate Excel.  It will provide further information about the excel system and other Microsoft systems.  This will further your knowledge on how Office works!
The PMT Function is used in this section in order to gather information to make decisions for the bigger purchase, such as a home, farm, etc.
By comparing and contrasting different loans and payment schedules, the borrower can see which offers less interest payments.  Some borrowers may not mind paying more in interest over the long run, but would rather make smaller monthly payments.  Others may settle for a higher monthly payment, but paying off the loan in a shorter amount of time, accruing less interest expense.
A variation of the variable rate mortgage is an amortization schedule.  The monthly payments are divided into different columns.  There is a principal and interest, with the principal reducing the amount of the loan.  The IPMT and PPMT Functions can be used to make the necessary computations.  Either way, the interest and principal amounts will add to equal the monthly payment.
The fill handle is an easy way to copy a value into many cells.  This function save a lot of time and is easy to perform.
These are the directions for the fill handle.  They are pretty simple and straight forward, this should be easy for students to follow.
Pointing is used as an alternate way to enter a formula.  This allows for the user to click the cells he/she wants to use in the formula as opposed to typing in the value.  This method is faster and easier than typing in the values contained in the cells.
These are more directions for pointing.  You may want to demonstrate a problem with the students for this can be slightly difficult to understand in writing.  (a good example to use is using pointing to get an average)
This is the end of the pointing directions.  At this point you may want to stop and ask students if they have any questions.
This is a listing of the different groups of functions.  It is important that the student know where to find their desired function when opening the functions window.
These are generalized instructions for the use of functions.  This will become useful later, when more complicated functions are described.  Picture is taken from www.microsoft.com
This step in the instructions is key for the use of functions.  This is the core of understanding, so make sure that students are clear on what needs to be done.
This slide explains what excel will do for you when everything is entered.  This demonstrates the power of excel and it’s capabilities.
These are the basic statistical functions used in Microsoft excel.  These can be used to make excel easier and faster when making a spreadsheet.
Counta functions are a continuation of the prior statistical slide.
These are two ways in which you can do worksheet calculations.  The second method on this slide (A1:A3) is the preferable and faster method of entering a function.
The SUM function modifies cell references which makes adjusting cells quicker, easier and more efficient.
If this comes up it means that the cell clicked is either non-existent or just invalid for other reason having to do with the formula involved.
Autofill is useful when entering series into cells.  This can also make using excel faster and easier.
This slide further explains the use of the Autofill function.  The next slide provides and example.
The IF function allows the user to make more educated decisions through the use of Microsoft Excel.
This slide gives examples of values which can be used in the IF function.
VLOOKUP and HLOOKUP functions perform the same task, except that one is arranged vertically and one is arranged horizontally.  This can be useful for teacher’s grade books.
This is a good way for a teacher to determine a curve for a class and to determine how students are performing.
This slide is more examples of how to use the VLOOKUP function.
Scrolling is one of the basic tools for using excel.  It is easy and enables the user to see the entire spreadsheet.
Freezing panes is useful with a large spreadsheet so that the user can know what category the values that he is looking at fall into.
These are the instructions detailing how to use the freezing panes function.
Grouping and outlines helps to keep information organized and grouped correctly.
These are the instructions to use in order to utilize the grouping and outline functions.