Loan Repayment Spreadsheet – PMT payment in Excel can be used to calculate loan repayments. This can be very useful both in personal and professional life.
All you have to do is food to the necessary details, short stories like the interest rate, the duration of the loan period and the loan amount in your Excel cells and PAYMENT function make rest the rest.
The information that the function requires and how it should be inserted is shown below.
The PV brackets and type arguments indicate that they are optional. 0 is inserted in each case are omitted.
In the worksheet below the interest rate, has been added and the number of periods of the loan amount. We will enter the PMT function in cell D4 to calculate the monthly payments on a loan.
In cell D4 enter;
= PMT (C4 / 12, B4 * 12, A4)
In C4 / 12, the annual interest rate is divided by 12 to calculate the monthly interest rate. If the loan payments were weekly we entered C4 / 52, and if we wanted to enter C4 quarterly / 4, and so on
In B4 * 12 the number of payment periods has been multiplied by 12. This is because the number of periods has been included in years, but the payments are monthly. This now calculates the number of monthly payment periods
A4 goes into the amount of the loan in function
The PV argument was omitted so Excel assumes 0 for the value. This means that, after all payments are made total remaining should be 0. The total repayment of the loan
The type of argument has also been omitted so Excel reassumes the value 0 for. This means that payments are made at the end of each month
Repayments of loans have been translated f1,162.95.
The spreadsheet displays the end of the function. Additional formulas are added to the E4 and F4 cells to calculate the total payment including interest, and interest paid on the loan.
The formula in cell E4 = D4 * 12 * B4
The formula in cell F4 = E4-A4
Learn how to calculate future values, such as a saving amount with the Excel FV and Meta Seek tool.
This page is dedicated to a list of the most useful functions of Microsoft Excel. It provides explanations on how to write each function and examples of its use.
Excel Ebook formulas
Make formulas easier with this ebook Excel formulas and not fear them any more.