Calculating
Loan Repayments (PMT Function)
We also have Free
training videos on this site ( visit the
Home page
or the
Training Page) 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.
|
|
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. |
