The PMT function calculates monthly payments based on the loan amount, interest rate, and length of time it takes to pay back the money. The syntax for the PMT function is PMT(interest rate per period, number of payments, loan amount). For example, to find the monthly interest on a $10,000, three-year loan at 9% (.09) interest, the PMT formula would be: =PMT(.09/12,3*12,10000). If you use the insert function button on the toolbar, a dialog box opens, showing each part of the formula and how to enter it: * .09/12 is the interest rate per period (the monthly interest rate equals the annual interest rate divided by 12 months). * 3*12 is the total number of payments (the number of years of the loan multiplied by 12 months). * 10,000 is the loan amount.
If you don’t know a function, then click on the insert function icon by the formula bar above the column headings to open the Insert Function dialog box and type in the Search for function box the type of function you want to use.
1. Open the moto.xls worksheet and then use the payment function to calculate the monthly payment. 2. In cell D3, enter the formula containing the PMT function for a three-year loan: =pmt(c3/12,3*12,b3)—the monthly payment should be ($321.85). The parentheses indicate a negative number or in this case a payment.
3. If the amount in cell D3 is correct, replicate the formula through cell D7 ($409.07, $353.55, $256.30, $407.04).
4. Save your work.
5. Go to the next section on Using the IF Function.