- Personal Finance»
- Mortgages & Loans
How to Calculate Repayment of a Loan in Excel
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.
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.
The calculation of annuity - simple method (proportional)
Copy the formula in cell C11
The rest is easy
in B12 copy =D5*B11
Now you have a template that can be used to calculate the annuity with a different data.
I am very glad that you did it!
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.
Please share your opinion !
What is the highest interest rate you are willing to pay?
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.