Microsoft Office Tutorials and References
In Depth Information
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
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
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.
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
RATE( nper , pmt , pv , [, fv =0], [ typ e=0])
Calculates the interest rate of a loan or investment based on
periodic, constant payments