Excel tools to demonstrate

Financial Functions

Excel has a wide assortment of financial functions that are used by thousands of financial analysts every day. I’ll illustrate the most common of these. If you’re going to be a financial analyst, you should become familiar with the others!

PMT Function

The PMT function is usually used to find the monthly payment for a car loan or a home mortgage loan. The inputs are typically an annual interest rate, a term (number of months financed), and the amount borrowed (the principal). The PMT function finds the amount you have to pay each month of the term. Part of this payment is principal and part is interest. At the end of the term, you will have paid just enough to pay off the entire loan.

To use the PMT function:

Enter the formula =PMT(interestrate,term,principal). For technical reasons, if you want PMT to return a positive value, you should enter the principal as a negative number. The interest rate argument should be the monthly rate (assuming you’re paying monthly), which is the annual rate divided by 12.

Try it! Suppose you take out a $30,000 loan for a new car when the annual interest rate is 6.75% and the term of the loan is 36 months. Calculate your monthly payment in cell B4. Then use a data table (the perfect place for one!) to see how your monthly payment varies for terms of 24, 36, 48, or 60 months. (Scroll to the right to see the answers.)