Excel Formula Help - PMT for loan repayments
Calculating monthly loan repayments with interest in Microsoft Excel is made easy with the PMT, or Payment, function. To get a quick understanding, take a look at the table below showing a series of loans to separate clients spread over 2 years.
The first thing we need to do is define the interest rates on each loan. To generate a percentage, we need to work in hundredths; we therefore enter in column C, 0.08, 0.07, 0.06 and so on. We can then select the column and use the Excel % button from the Home toolbar and Number pane, as seen above.
We then proceed to our formula with the following format: =PMT( interest _ rate, number _ payments, PV, [FV], [Type]) Number of payments defaults to one annual payment. We therefore need to divide the interest rate by 12 to achieve accurate monthly figures. PV refers to the original value of the loan; FV is optional and refers to the future value of the load once all payments are made. The Type field can be either 0, meaning payments are due at the end of the period, or 1 meaning payments are due at the beginning of the period. In our loan book, payments are set for the beginning of the month:
Loan repayments are given in the negative. Select D3, copy and paste down to receive monthly payment figures for all other client loans, for example, the formula in D7 should by =PMT(C7/12,2*12,B7,0,1)
For further help working with PMT and other financial functions and formulas, contact our experts. More help on PMT can also be found on the Microsoft Excel Help pages here.
