Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

If Amanda and Joseph were to buy a home with a $175,000 mortgage under the loan

conditions specified in this workbook, their average monthly expenses would increase

from $2,917 to $3,060.63 (cell B20), and the amount of money they could save each

month would drop from $533 to about $389 (cell B22). By replacing the rent expense with

the monthly home loan payment, Amanda can quickly gauge the effects of the loan on the

family budget. Because the differences don’t seem too unreasonable, Amanda now wants

you to increase the size of the loan to $250,000, but keep all of the other factors constant.

To explore a what-if analysis for the mortgage:

1.
Click cell
E4
, type
-250,000
as the new loan amount, being sure to enter this as a negative

value, and then press the
Enter
key. Under this scenario, the monthly payment increases to

about $1,419 and the family’s monthly expenses increase to about $3,486, which is more

than they make in a typical month. Obviously a loan of this size is more than they can afford.

2.
Click the
Undo
button

on the Standard toolbar to restore the worksheet to its previous

condition.

This time Amanda wants to know what would happen if the interest rate changed. To

determine the difference between the low interest rate of 5.5% and a higher one, you will

change the interest rate to 6.5%.

3.
Click cell
E6
, type
6.5%
, and then press the
Enter
key. Excel calculates the monthly payment

to be about $1,106. Amanda can see that if the interest rate increases by 1%, then the monthly

payment increases by about $113. She wants you to change the interest rate back to 5.5%.

4.
Click the
Undo
button

on the Standard toolbar to change the interest rate back to its

previous value.

The PMT function is just one of the many Financial functions supported by Excel.

Figure 2-29 describes some of the other functions that can be used for mortgage analysis.

For example, you can use the PV function to calculate the size of the loan that Amanda

could afford given a specific interest rate, monthly payment, and total number of pay-

ments. If Amanda wanted to know the size of the loan she could afford by using the $850

rent payment as a loan payment, you would enter the formula
=PV(5.5%/12,360,850)
,

which would return the value –$149,703.50, or a total loan of almost $150,000.

Figure 2-29

Financial functions

Function

Description

PMT(
rate
,
nper
,
pv
, [
fv
=0], [
type
=0])

Calculates the payments required each period on a loan or

investment, where
rate
is the interest rate per period,
nper
is the

total number of periods,
pv
is the present value or principal of

the loan,
fv
is the future value of the loan, and
type
indicates

whether payments should be made at the end of the period (0)

or the beginning (1)

PV(
rate
,
nper
,
pmt
, [
fv
=0], [
type
=0])

Calculates the present value of a loan or investment based on

periodic, constant payments

NPER(
rate
,
pmt
,
pv
, [
fv
=0], [
type
=0])

Calculates the number of periods required to pay off a loan or

investment

RATE(
nper
,
pmt
,
pv
, [,
fv
=0], [
typ
e=0])

Calculates the interest rate of a loan or investment based on

periodic, constant payments