Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 31

Frequently Used Financial Functions

There are a few Excel financial functions that look and feel alike. After you understand and master one of these functions, you can navigate and utilize all of them with relative ease. All these financial functions deal with a stream of equal periodical payments called annuities. Annuities could include loans, mortgages, or retirement funds; transactions where you receive or pay a series of equal cash payments for a specific number of periods at equal intervals. An annuity is essentially a level stream of cash flows for a fixed period of time.

These functions include PMT, IPMT, PPMT, FV, RATE, NPER, and PV.

I can start with an example that you will be familiar with if you read some of the previous chapters. This example analyzed the payment of a car loan. Consider the example I used before again: You are purchasing a car for $22,000. You are required to give $4,000 as a down payment. The annual interest rate is 8.00 percent and the loan period is three years. Assume end-of-period monthly payments. The following is the model created for this loan, see Figure 31.1. We named the cells in column B by the designations in the cells of column A. You will find the example on the sheet named Car loan in the Chapter 31 workbook.

FIGURE 31.1 The Car Loan Model

What is of interest to us in this example is the PMT, the Payment function in cell ...