Microsoft Office Tutorials and References
In Depth Information
4 Financial Functions, Data Tables,
and Amortization Schedules
Two of the more powerful aspects of Excel are its wide array of functions and its capability
of organizing answers to what-if questions. In this chapter, you will learn about ﬁ nancial
functions such as the PMT function, which allows you to determine a monthly payment
for a loan, and the PV function, which allows you to determine the present value of an
In earlier chapters, you learned how to analyze data by using Excel’s recalculation
feature and goal seeking. This chapter introduces an additional what-if analysis tool,
called data tables. You use a data table to automate data analyses and organize the answers
returned by Excel. Another important loan analysis tool is the Amortization Schedule
section. An amortization schedule shows the beginning and ending balances and the
amount of payment that applies to the principal and interest over a period.
In previous chapters, you learned how to print in a variety of ways. This chapter
continues with a discussion about additional methods of printing using names and the Set
Print Area command.
Finally, this chapter introduces you to cell protection; hiding and unhiding rows,
columns, sheets, and workbooks; and formula checking. Cell protection ensures that
users do not change values inadvertently that are critical to the worksheet. Hiding portions
of a workbook lets you show only the parts of the workbook that the user needs to see.
The formula checker checks the formulas in a workbook in a manner similar to the way
the spell checker checks for misspelled words.
Table and Amortization Schedule
The project in the chapter follows proper design guidelines and uses Excel to create the
worksheet shown in Figure 4–1. Braden Mortgage operates as a small home loan institu-
tion. The company’s Chief Financial Ofﬁ cer has asked for a workbook that calculates loan
payment information, displays an amortization schedule, and displays a table that shows
loan payments for varying interest rates. To ensure that the loan ofﬁ cers do not delete the
formulas in the worksheet, she has asked that cells in the worksheet be protected so they
cannot be changed accidently.