Canadian Mortgage/Loan Calculator - free Microsoft Excel template

This Excel Canadian mortgage calculator allows you to determine the payment and remaining balance for a mortgage based on various input parameters such as the term, additional unscheduled payments, compounding period, and payment frequency.  In some cases interest may be tax-deductable (for example on the loan taken to purchase investments that generate income) so you can use the template to calculate the interest that you can claim.

This spreadsheet generates an amortization schedule for a fixed-rate Canadian mortgage loan. It can be used to compare various loan terms, interest rates, and loan amounts.

Download free Canadian Mortgage/Loan Calculator template for Excel by clicking the download button at the end of this article.

Instructions on how to use the Microsoft Excel mortgage calculator spreadsheet

This calculator allows you to input values in the green-background cells on the top of the page and observe the resulting changes in payment, total interest, outstanding balance.

Loan amount  - the spreadsheet calculates the loan amount, which is the difference between the property asking price and the down payment. However, you have the flexibility to override this calculation and manually input the loan amount at any time, regardless of the property price and down payment figures.

Canadian Mortgage rate - enter the current mortgage rate for a Canadian loan.

Amortization period (in years) - enter the length of the loan in years. Typically, fixed-rate home loans in Canada have a 25-year amortization period.

Term (in years) - enter the duration of the loan term. Typically, it's 5 years.

Compound Period - refers to the frequency at which the interest on the loan is calculated and added to the outstanding principal. The compound period is typically semi-annual, meaning that the interest is calculated and added to the principal twice a year. This means that the interest on the outstanding balance is compounded semi-annually, which results in a higher overall interest cost to the borrower over the life of the loan.

Payment frequency -  refers to how often a borrower is required to make payments on a loan. Common payment frequencies for mortgages include monthly, bi-weekly, and weekly.

Payment first date - The start date of the loan repayment, which is typically one month after the loan was originated.

Payment - The payment amount is calculated based on the input values you have provided for the loan. It refers to the amount of money that a borrower is required to pay back to a lender on a regular basis in order to repay a loan. This payment includes both the interest and the principal, which is the original amount of the loan.

Additional Payment -  input the amounts to include non-regular prepayments.

Amortization schedule - The spreadsheet automatically generates a table that displays the breakdown of interest and principal for each payment throughout the loan term. The schedule also illustrates how the remaining balance of the loan decreases over time as payments are made.

Summary

The summary sheet of the calculator automatically provides an annual breakdown of your mortgage including the payment, interest, principal and additional payment amounts.

Please download our free Excel template for the Mortgage/Loan Calculator that automatically computes your loan.

Please be aware that our spreadsheet rounds the numbers and also adjusts the final payment to bring the balance to zero, which may result in slight differences between the calculations in our calculator and those provided by your bank.

Please be advised that the calculations in this spreadsheet are estimates only and seek the advice of a financial advisor or lending institution before making any final financial decisions. Also, after end of the term please create a new spreadsheet using new inputs.

The license for this material is for personal use only and it is not authorized for distribution or resale.

If you have any questions or need assistance using this calculator, please reach out for to us.

Get in touch with me to discover strategies for optimizing your savings.

Disclaimer:
“Please note that the information provided in this article is of a general nature and may not be accurate for your specific situation. The information is current as of the date of posting and is not intended to provide legal advice. It's always recommended that you consult with a banking representative and lawyer for personalized guidance and advice."