When you borrow to buy a house you’ll want to know how much you will have to repay each month. This month we’ll show you how to calculate mortgage repayments using Excel.
When you are shopping for a new house you’ll want to know how much your loan is going to cost you each month. If you already have a loan then you’ll be interested in how you are progressing with your loan. In each case you can use Excel to calculate the loan repayments and to determine how much the loan is costing you. To help you out, we’ve found a great template for you to download and use.
Start by launching Microsoft Excel and choose File > New (in Excel 2007 click the Office button and click New). Type loan calculator into the Search box and click to search. When the results appear, locate the Loan Calculator and Amortization Table template and click to Download it.
When the loan calculator opens in Excel look at cells E4, E5, E6 & E7 as these are the only cells you will be entering data into. All the other cells will calculate automatically for you. So, into cell E4 type the amount you will be borrowing. If you already have a mortgage you can type the amount you currently owe on the loan. We entered $200,000 as the loan amount.
Into cell E5 type the interest rate you will be paying (or if you already have a loan – your current interest rate). So, for a rate of 5.5% we typed 5.5. Into cell E6 type the term of the loan in years – if you already have a loan, type the number of years you have to go on your loan. We entered 25 for a 25 year loan. Into cell E7 type the start date of the loan – we typed 1-Feb-2015 for the first of February 2015. If you already have a loan, type the date of your next payment.
As soon as you enter the start date into cell E7 all the cells in the worksheet update and you will see all the details about the loan. In cell E9 you will see the monthly repayment amount and in cell E11 is the total interest that you will pay over the life of the loan. Rows 16 and onwards show you payment details. Each row contains the payment number, how much you are paying in principle and interest and what you still owe when that payment has been made. Scrolling down the worksheet you will see that, over time, the principle payment will increase and the interest payment will decrease.
Important Note: This worksheet assumes your loan is a standard fixed interest loan that is repaid by monthly payments of principle and interest. It gives you a guide to what your payments might be. However only your lender can tell you exactly what you will pay as actual repayments may vary from these figures and they may also include additional fees and taxes.
Important Note: This worksheet assumes your loan is a standard fixed interest loan that is repaid by monthly payments of principle and interest. It gives you a guide to what your payments might be. However only your lender can tell you exactly what you will pay as actual repayments may vary from these figures and they may also include additional fees and taxes.
