Calculating the amortisation of a mortgage or personal loan can be done quickly through Excel by using a simple formula. An amortisation calculation is needed to determine the monthly amount required in order to payoff the loan, as most of the payments at the beginning go toward paying the interest. This Excel amortisation formula calculates the payment amount based on the original loan amount, years to payoff, annual interest rate and number of payments per year.
- Skill level:
Other People Are Reading
Things you need
- Microsoft Excel
- Loan principal amount
- Loan number of years to payoff
- Loan annual interest rate
- Number of payments per year
Collect the numbers needed in order to calculate the amortisation, which is the original loan amount, years to payoff, annual interest rate and number of payments per year.
Open Microsoft Excel with a new, blank spreadsheet.
Enter a title in the cell A1, so that you can recognise what this chart was for when referring to it later. For example, title it "Loan Amortization" or "Monthly Loan Payment Amount."
Type "Original Loan Amount" in cell A2.
Enter the amount of the original loan in cell B2. This should be the amount the loan was issued for, not including interest. This is often also referred to as the principal amount.
Type "Loan Duration (Years)" in cell A3.
Enter the number of years the loan was issued for in cell B3. This should be the length of time of the loan's duration. For example, if this is for a 30-year mortgage, then type "30" in this cell.
Type "Annual Interest Rate" in the cell B4.
Enter the annual interest rate in the cell B4. For example, if the loan was issued at 6.5 per cent annual interest, then type "6.5%" or "0.065" into the cell.
Type "Number of Payments" in cell A5.
Enter the total number of payments that will be made each year in the cell B5. For example, if you must pay monthly payments, then enter "12" in this cell.
Type "Monthly Payment" in cell A6.
Enter the following formula in cell B6: =PMT(B4/B5,B3*B5-B2). Be sure to include the equal sign and not include the period at the end of that sentence.
Click "Enter" after entering the formula to see the number that results in the cell B6. This is the monthly payment for your loan.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for