Buying a house is a dream for everyone, but how many of you know about the total interest that you pay back to the bank? If you are planning to buy a house I strongly suggest that you use a home loan calculator to understand the repayment structure and the interest that you pay.

Excel provides an inbuilt template called Loan Amortization Schedule that can help you to calculate the loan amount that you repay to the bank.

This home Loan Calculator also gives you detailed summary of how much you pay per month, what is the principal and interest amount that is deducted from your payment, what’s your cumulative interest etc.,

Using Excel Home Loan Calculator

The Loan Amortization Schedule template is available from Excel 2003 version onwards. Let’s quickly see how to launch and use it now.

  • Locate Microsoft Excel from your Start Menu Programs or type EXCEL in your run window to launch it.
  • Navigate to File Menu and click New
  • Click Sample templates
  • Select Loan Amortization and click Create.

Excel Loan Amortization Template

  • You should now see Loan Amortization Schedule Sheet.
  • For Example, let’s assume that you are provided with a bank loan of 3500000 at an annual interest rate of 10.25 percent. Enter the Loan Amount as 3500000 and Annual interest rate as 10.25.
  • Enter Loan period in years as 25, this is the number of years by which you will settle down the complete loan amount back to the bank.
  • Enter number of payments per year as 12, this indicates the months.
  • Put the start date of loan as today. You can use the formula =TODAY() or press CTRL + : to auto populate today’s date.
  • Once you have entered all the above details you should see the Loan Summary on the right side of the sheet automatically calculated and displayed for you.
  • The Scheduled payment shows the monthly amount that you have to pay to the bank.
  • Scheduled number of payments tells you the total number of payments that is required to complete the loan. For example we have specified Number of payments per year as 12 and Loan period as 25 hence you should see the Actual number of payments as 300 (12 x 25)
  • Total interest will spin your head off for sure :-) . This cell will tell you the total interest that you are paying to the bank, please note that this is just the interest alone.

Home Loan calculator payment details

If you are planning to make an extra payment on yearly basis then you can put that amount in the Extra payment column and the loan calculator automatically calculates and adjusts the Total Interest.

Online Home Loan Calculator

You can also use the online home loan calculator provided by ICICI Bank. It doesn’t give you detailed report on monthly basis but is sufficient to get just the monthly amount that you have to pay.

The Repayment comparison calculator from WestPac allows you to do a repayment comparison.

These calculators can also be used to check your personal loan repayment structure too; all you have to do is change the rate of interest and loan period.

Planning to buy a house? Have you done your homework?