Microsoft Office Tutorials and References
In Depth Information
( Note: This session presents topics related to Financial functions and Logical functions.
This session is optional and may be skipped without loss of continuity of the instruction.)
Working with Financial Functions
After reviewing the figures calculated in the Budget worksheet, Amanda thinks she has a
better understanding of the family finances. Now she would like to determine whether the
family could afford the monthly payments required to purchase a house if they were to take
a loan from a bank. To do this, she wants to create a worksheet containing a “typical”
month’s income and expenses, and then she wants to use an Excel Financial function to cal-
culate the monthly payments required for a loan of $175,000. Excel’s Financial functions
are the same as those widely used in the world of business and accounting to perform vari-
ous financial calculations. For instance, these functions allow you to calculate the deprecia-
tion of an asset, determine the amount of interest paid on an investment, compute the
present value of an investment, and so on. Although she is not a business or financial profes-
sional, Amanda’s question is a financial one: Given the family budget, how great a loan pay-
ment can they afford if they want to buy a home? There are four principal factors involved in
negotiating a loan:
For hands-on practice of
key tasks in this session,
go to the SAM 2003
Training Companion CD
included with this text.
• The size of the loan
• The length of time in which the loan must be repaid
• The interest rate charged by the lending institution
• The amount of money to be paid to the lending institution in periodic installments,
called payment periods. (For most home loans, payments are due monthly, so the pay-
ment period is a month.)
To be sure, this is a simplified treatment of loans. Often other issues are involved, such
as whether payments are due at the beginning of the payment period or at the end. For the
purposes of this exercise, the above are the major factors on which Amanda will concen-
trate for now. Once you know any three of these factors, you can use Excel to calculate the
value of the remaining fourth. Amanda is interested in a loan with the following conditions:
• The size of the loan is equal to $175,000.
• The length of time to repay the loan is equal to 30 years.
• The annual interest rate is equal to 5.5%.
She wants to calculate the fourth value—the monthly payment required by the lending
institution to pay back the loan. To answer this question, you’ll add a new worksheet to her
workbook in which she can analyze various loan possibilities.
To create the Loan Analysis worksheet:
1. If you took a break after the last session, make sure that Excel is running and that the
Budget2 workbook is open.
2. Insert a new worksheet at the end of the workbook named Loan Analysis , and then save
the workbook as Budget3 in the Tutorial.02\Tutorial folder included with your Data Files.
3. Click cell A1 , type LOAN ANALYSIS , and then press the Enter key.
Now you need to copy the labels and the average values from the Budget worksheet, which
you completed in the previous session.