For Canadian mortgage loans, the interest is compounded semi-annually, rather than monthly, even if the payments are monthly. Note: Visit your bank's website, or check with your banker, to confirm how your bank will calculate the payments. In cell C6, the PMT function calculates the monthly payment, based on the annual rate, the number of payments periods and the loan amount present value :.
In the previous examples, you had to enter the total number of payments due, after calculating that number -- number of years in the loan term, times the number of payments per year.
To make things easier, this Excel loan payment calculator lets you select the payment frequency from a drop down list of options.
In the sample file, the Lists sheet has a lookup table of frequencies and number of payments per year, for each frequency. Or, you can enter the known components of a loan in separate cells and reference those cells in your PMT formula.
With the interest rate in B1, no. Please remember that the payment is returned as a negative number because this amount will be debited subtracted from your bank account. By default, Excel display the result in the Currency format, rounded to 2 decimal places, highlighted in red and enclosed in parenthesis, as shown in the left part of the image below.
The image on the right shows the same result in the General format. If you'd like to have the payment as a positive number , put a minus sign before either the entire PMT formula or the pv argument loan amount :. Below you will find a few more examples of an Excel PMT formula that show how to calculate different periodic payments for a car loan, home loan, mortgage loan, and the like. For the most part, you can omit the last two arguments in your PMT formulas like we did in the above examples because their default values cover the most typical uses cases:.
As an example, let's calculate the amount of annual payments based on these input cells:. And you will have this result:. Monthly payment for a loan with with terms specified as arguments in A2:A4, except payments are due at the beginning of the period.
Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue.
The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.
Get the periodic payment for a loan. Return value. Defaults to 0 zero. Default is 0. Excel Usage notes. Notes: The payment returned by PMT includes principal and interest but will not include any taxes, reserve payments, or fees.
Be sure you are consistent with the units you supply for rate and nper. For annual payments on the same loan, use 12 percent for rate and 3 for nper. PMT formula examples.
0コメント