If you need to calculate a loan repayment in Excel, you can do this using the PMT function.
This function can be used for any kind of loan, like a mortgage, car finance or a personal loan.
Download File
If you would like to follow along download the attachment below.
What is the PMT function
The PMT function in Excel calculates the payment for a loan based on constant payments and a fixed interest rate.
The syntax for the function is:
=PMT(rate, nper, pv, [fv], [type])
Inputting Data to use for PMT Calculation
For Excel to be able to calculate a loan repayment with the PMT function it needs to be told your:
Loan rate
Loan term
Loan amount
You can enter these values directly into the function (known as hardcoding), but a better approach is to reference cells. This makes your calculations more flexible, allowing you to easily update values and see the impact on repayments (to read more about hardcoding v referencing cells click here).
To reference cells it’s best to have your data laid out in your spreadsheet in a clear way so that you can reference it in your PMT function and easily change it to see the impact on your repayment.
Using PMT to Calculate Annual Repayments
If you input annual values into the compulsory parameters for the PMT function the output will be an annual repayment assuming you only made one repayment a year on the last day of the year for the length of the term.
Most loans are not repaid on an annual basis, so if you want to see what the repayments would be over a different repayment period, like monthly or quarterly, you will need to adjust the rate and nper parameters.
Using PMT to Calculate Monthly Repayments
To use the PMT function to calculate the monthly repayment for the loan you would need to convert the annual amounts to monthly amounts by:
Dividing the rate by 12
Multiplying the nper by 12
Using PMT to Calculate Quarterly Repayments
To use the PMT function to calculate the quarterly repayment for the loan you would need to convert the annual amounts to quarterly amounts by:
Dividing the rate by 4
Multiplying the nper by 4
Using PMT to Calculate Total Loan Cost
To calculate the total loan cost, the total amount that you will pay above the value of the loan, you would need to multiply the function by the total number of periods.
This will return the total amount that will be paid for the loan. Subtracting the loan amount from this will give you the cost.
Using the optional fv parameter in PMT
The optional fv parameter can be used when part of the loan remains unpaid at the end of the primary term.
For example, if you take a £12,000 loan and repay £10,000 over five years, you would still owe £2,000 at the end of that period.
To calculate your monthly payments for the initial £10,000 you could enter £2,000 in the fv parameter.
This returns a lower monthly payment of £159.30 as the full amount of the loan has not been paid within the five year period.
Using the optional type parameter in PMT
By default, the PMT function assumes payments are made at the end of each period e.g. at the end of the month for monthly payments.
If this needs to be changed to the beginning of the period, then the optional type parameter can be used.
There are two options that can be entered in this parameter:
0 - for end of period payments (this is the default so if you want payments at the end of the period type can just be left blank)
1 - for beginning of period payments.
Conclusion
The PMT function can be used to calculate the amount a repayment of a loan will be.
It will calculate annual repayments if this is what is input into the parameters, but the rate and term can be divided and multiplied respectively by a different number of periods to reflect the repayments e.g. monthly or quarterly.
Optionally a future value can be added to the calculation if you are only trying to calculate the repayment for part of the loan.
Repayments are assumed by default to be made at the end of a period, but by entering 1 in the optional type parameter you can change this to be the beginning of a period.
Komentarze