Click to edit Master text styles
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
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
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
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
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
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
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.