Updated date:

How to Calculate Repayment of a Loan in Excel

Author:

Prepare form

Use the same columns and rows that you'll be able to copy the formulas. When you're finished, you'll be able to carry out calculations for different amount, interest rates, the number of annuities with different maturity period.

Prepare the form as shown below.

how-to-calculate-repayment-of-a-loan-in-excel

Enter the data

Before you enter the formulas, fill the cells with initial data.

how-to-calculate-repayment-of-a-loan-in-excel

Number of months of repayment

Number of months of repayment is calculated by multiplying the number of annuities and with how much is the monthly annuity.

Copy to C9 : =D5*C7

how-to-calculate-repayment-of-a-loan-in-excel

The calculation of annuity - compound method (conformal)

Formula for the calculation is quite complicated. Copy it into cell B11.

=B5*(1/((((1+(100*(((1+C5/100)^(C7/12))-1))/100)^D5)-1)/(((1+(100*(((1+C5/100)^(C7/12))-1))/100)^D5)*((1+(100*(((1+C5/100)^(C7/12))-1))/100)-1))))

how-to-calculate-repayment-of-a-loan-in-excel

The calculation of annuity - simple method (proportional)

Copy the formula in cell C11

= B5*((((1+(C5/(1200/C7)))^D5)*((1+(C5/(1200/C7)))-1))/(((1+(C5/(1200/C7)))^D5)-1))

how-to-calculate-repayment-of-a-loan-in-excel

The rest is easy

in B12 copy =D5*B11
C12 =D5*C11
B13 =B12-B5
C13 =C12-B5
D11 =C11-B11
D12 =C12-B12
D13 =C13-B13

Now you have a template that can be used to calculate the annuity with a different data.

how-to-calculate-repayment-of-a-loan-in-excel

That's all.

I am very glad that you did it!

Annuities

The amount that a borrower in equal installments (monthly, quarterly, half-yearly, annual) returns the lender is called annuity. Each installment consists of interest and repayment. Repayment is amount which reduces outstanding debt of the borrower.

Mortgage Calculator with Extra Payments

Please share your opinion !

Made-craft repayment

Calculate loan repayments or mortgage is really simple. If someone borrow from $ 100 at an annual interest rate of 10%, then you have at the end of the year to return to his lender borrowed $ 100, and had to pay $ 10 interest - a total, therefore, $ 110. It did not say, however, that you can immediately repeat the loan on the same terms and at the end of next year again to return borrowed $ 100 and $ 10 interest - a total of $ 110 in the second year. If you look two years back, you'll see that during two years at their disposal with the principal of $ 100, and you have to pay the fees total $ 20 interest.

Estimated time of preparation and production

Prep timeCook timeReady inYields

5 min

15 min

20 min

Annuity Calculator

Please rate your satisfaction with banks.