# How to Calculate Repayment of a Loan in Excel

Updated on January 7, 2019 ## 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.

## Enter the data

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

## 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

## 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))))

## 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))

## 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.

## 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.

### What is the highest interest rate you are willing to pay?

See results

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.

See results

## Estimated time of preparation and production

Prep time: 5 min
Cook time: 15 min