Using financial functions – PMT in Microsoft Excel 2019

The PMT function calculates payments for a loan withconstant interest rate and constant payments. You would like to purchasea second-hand car for about 30,000.00. Different dealers give youdifferent rates and different payment plans. You know that you cannotafford more than 30,000.00 and the payment you want to make per monthmust not be more than 800.00. As you were saving for a car, you haveabout 5,500 in your savings account for a down payment but you wouldprefer not to use that cash. You would like to find out how to put downas little as possible on the deposit to pay the amount of 800.00 permonth for the car. On investigation at a few car dealers, you now havean idea of the number of years you could pay for the car over, and alsothe interest rate you would qualify for.

The syntax for the PMT functionis: =PMT(rate,nper,pv,fv,type).

  • Rate: This is the annual interest rate for theloan.
  • Nper: This is thetotal number of payments to be made for the loan.
  • Pv: This is thepresent value of the investment (principal value).
  • Fv: This is the amount you want the investment tobe at the end of the last payment on the loan. Therefore, fvis 0.
  • Type: Enter 0 for theend of the month or enter 1 for the beginning of the monthwhen payments will be made.

Let’s use the preceding information to work out the figures and seeif you can afford the car, as follows:

  1. To follow this example, make sure you have the PMT.xlsxworkbook open.
  2. Type the following information into the relevant cells in columnA on the worksheet.
    1. Enter an interest rate of 0.005% in cell C4. This is one-twelfth of a 6% annualrate—this example interest rate is relating to a single repayment periodand not an annual rate. Note that this is a simple example and thatinterest rates and the repayment period differ depending on various loanconditions, as well as credit scoring.
  3. Enter the payment period in months that the loan will be over incell C5. For this example, we will use36 months.
  4. The amount of the loan is entered into cell C6 as 30000.
  5. Only enter a future value if the loan will have a cash balance to bemade when the last loan payment is paid; otherwise, enter a 0.
  6. The formula will be constructed in cell C8 as follows: =PMT(C4,C5,C6,0,0) if using the typingmethod; alternatively, follow these next steps to access the Function Library.
  1. Click on the Financial booklocated on the FunctionLibrary, then select the PMT function, as illustrated in thefollowing screenshot:

ms office 309

  1. Add the function arguments as per the information given.
  2. Click on the OK command to see theresult and the payment per month based on the input.
  3. Notice that there is a negative payment value now in cellC8. To make this a positive value, add a minus sign infront of the PMT function so that itreturns a positive value and not a negative value, as follows=-PMT(C4,C5,C6,0,0).

Now, you have a good idea of the PMT function and canchange the payment period to 48 months to see if you can bring down thepayment or ask the dealer for a better interest rate.

Leave a Comment