To calculate a loan payment amount, given an interest rate, the loan term, and the loan amount, you can use the PMT function. In the example shown, the formula in C10 is: = PMT(C6 12, C7, - C5)The Excel formula used to calculate the lending rate is: =RATE(12 B4;-B2;B3) = RATE(12 13;-960;120000) Note: the corresponding data in the monthly payment must be given a negative ;· how to calculate loan repayments in excel First you need to enter your data. Principle = the amount you want to borrow. The Interest Rate = the per annum interest rate divided by ;· For example, a 30-year mortgage paid monthly will have a total of 360 payments (30 years x 12 months), so you can enter "30 12", "360", or the corresponding cell (in this case, C4) 12. If you wanted to calculate a five-year loan that's paid back monthly, you …30/09/2009 · To calculate the principle portion of the monthly repayment, subtract the interest amount from the total payment amount. The ending balance is just a matter of subtracting the principle amount from the balance at the beginning of the month, as it’s only the principle portion of our repayment that comes off the loan Loan Repayments With Excel FormulasCalculate Loan Repayments in Excel using the PMT functionExcel formula: Calculate payment for a loan | ExceljetHow to calculate loan payments in 3 easy steps3. Use the IPMT function to calculate the interest part of the payment. The second argument specifies the payment number. 4. Update the balance. 5. Select the range A7:E7 (first payment) and drag it down one row. Change the balance formula. 6. Select the range A8:E8 (second payment) and drag it down to row 30. It takes 24 months to pay off this loan.

Tags: Calculate loan payments in excel, Calculate loan amortization in excel, Calculate monthly loan payment in excel, Calculate mortgage repayments formula excel, Calculate loan payment amount in excel, Loan calculator formula excel, Calculating monthly loan payments in excel, Loan repayment calculation excel,