Calculate loan payment
In case the payments are made annually, you can use 5% as the interest rate (as shown below). Note that since the payments are monthly, the interest is taken as 5%/12. Here is the formula that will calculate it: You can also use the PMT function to calculate how much you should invest per month to get a certain amount in the future.įor example, suppose you want to invest in a way to get USD 100,000 in 10 years when the annual interest rate is 5%. Example 2 – Monthly Payment to Grow Your Investment to USD 100,000 If you want it to be positive, make the loan amount negative.Īlso, remember that the interest rate remains constant throughout the period. Note that the loan payment is negative as it’s a cash outflow. You can omit the optional arguments as these are not needed.īelow is the formula that will calculate the loan payment amount using the PMT function: pv – $200,000 (this is the loan value that I get today).nper – 20*12 (since the loan is to be paid for 20 years every month).rate – 4%/12 (since this the payment is monthly, you need to use the monthly rate).Here are details regarding the arguments: Suppose you have a house loan of $200,000 that needs to be paid back in 20 years when the payment is made every month, and the interest rate is 4%. Example 1 – Calculating the Monthly Loan Amount in a House Mortgage PMT function can be used in many different ways in Excel.īelow are some examples of using it. For example, if payment is due on 31st January, this will be 0, but if it’s due on 1st January, make this 1. In case the payment is due at the beginning of the month, make this 1. type: If the payment is due at the end of the month, omit this or make this 0.In case you only want to get the loan paid and nothing else, omit it or make it 0.
fv: It is the future value of your payments you want after the loan is paid off.In the above house loan example, this would be USD 200,000. pv: It is the present value of the loan.nper: It is the number of periods in which the loan is to be paid back.Similarly, if it’s quarterly, it will be rate/4. For example, if it’s monthly payments, it will be rate/12. rate: It is the interest rate you need to pay per period.Example 2 – Monthly Payment to Grow Your Investment to USD 100,000īelow is the syntax of PMT function in Excel:.Example 1 – Calculating the Monthly Loan Amount in a House Mortgage.In our case, it is 12 × 10 = 120.Īpply the below formula for calculating the periodic payment. In our case, it is 0.06 / 12 = 0.005.Ĭompute the total number of payments (or periods, n n n) required to repay the loan principal.
If you want to check the formula for this calculation, visit our equivalent rate calculator.Ĭalculate the periodic rate ( i i i) by dividing the annual interest rate by the number of payments in a year. Now, consider this: If your bank allows you to make overpayments and you choose to pay an additional 100 a month, you could find. Since, in the present case, the payment frequency and the compounding frequency coincide, the equivalent rate equals the given interest rate. Using our calculator tools, we can work out that your monthly payment would be 295.88, meaning that by the date of your last loan payment (in February 2039) you will have paid just over 13,250 in total interest. You can easily insert this data into our loan interest calculator:Īs a first step, you need to compute the equivalent rate, which is adjusted for compounding frequency. Let's assume you are considering obtaining a loan for a car purchase, so you decide to turn to a bank that offers you a personal loan of 10,000 dollars with 6% interest, repaid monthly in 10 years with the same compounding frequency. The best way to understand how interest is calculated on a loan is to introduce it with a real-life example.