For anyone, not just those in a business or financial setting, being able to calculate what loan repayments would be is a vital skill that can assist you with all aspects of your life, from personal finance to business finance. With the aid of the tools and functions available in Microsoft Excel you will be able to work out a host of solutions from financial to statistical and many more.
With the Excel PMT function you can calculate payments for a loan based on constant loan repayments and a constant interest rate.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
How to use the PMT function:
To activate the PMT function you select a cell in which the answer can be placed and then you click the fx button on the Excel toolbar which will activate the Excel function wizard.
From the drop down selection box of categories select the ‘Financial’ category and then from the list that appears select the PMT function.
A new dialog box will appear in which you will be required to fill in the Function arguments for the PMT function.
There are several arguments that are required for the PMT function to be determined accurately by Excel:
- Rate: this is the interest rate per period for the loan.
- Nper: this is the total number of payments for the loan
- Pv: this is the present value or the total amount that a present series of future payments is worth now. In the case of the PMT function this will be equivalent to the amount that you loan from the bank.
- Fv: this is the future balance or the cash value you would like to obtain after the last payment is made. If this argument is omitted the Excel PMT function will substitute zero into the equation.
- Type: this is the last argument and is considered a logical value, if you make the payment at the beginning of the period you enter 1 for the PMT function and if you make your payment at the end of a period you can either fill in zero or leave this argument blank and Excel will substitute the zero in.
- Once you have filled in all the relevant information you can click OK and the PMT function will have determined the payments required to fully amortize your loan.
When you receive the PMT function answer, you should always remember that although the function includes the principal loan amount and the interest rate it does not however include taxes, reserve payments or fees that can be associated with loans. Also remember that when you are filling in the arguments for the PMT function you are required to use the right units otherwise your result will be inaccurate. If for example you are paying a loan over 6 years at 9.5% interest you will put 9.5% / 9.5 in the interest rate and 6*12 in the Nper. However if you are making annual repayments it will be 9.5%/9,5 in the rate and 6 in the Nper.
For all the advice and tips on the PMT function as well as other useful tools and functions that Excel has to offer contact Miricle Solutions, a company that offers high proficiency training on Excel. If it’s Excel training that you are looking for then Miricle Solutions is the perfect solution for you, visit www.auditexcel.co.za for more information about the different courses available.