Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

Session 2.2

(
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.