Excel 2016 Formulas

Chapter 13 Financial Schedules

In This Chapter

• Setting up a basic amortization schedule
• Setting up a dynamic amortization schedule
• Evaluating loan options with a data table
• Creating two-way data tables
• Creating financial statements
• Understanding credit card repayment calculations
• Calculating and evaluating financial ratios
• Creating indices

This chapter, which makes use of much of the information contained in the two previous chapters, contains helpful examples of a variety of financial calculations.

Creating Financial Schedules

Financial schedules present financial information in many different forms. Some present a summary of information, such as a profit and loss statement. Others present a detailed list, such as an amortization schedule, which schedules the payments for a loan.

Financial schedules can be static or dynamic. Static schedules generally use a few Excel functions but mainly exist in Excel to take advantage of its grid system, which lends itself well for formatting schedules. Dynamic schedules, on the other hand, usually contain an area for user input. A user can change certain input parameters and affect the results.

The sections that follow demonstrate summary and detail schedules, as well as static and dynamic schedules.

Creating Amortization Schedules

In its simplest form, an amortization schedule tracks the payments (including interest and principal components) and the loan balance for a particular loan. This section presents several examples of amortization ...