Excel Mortgage Repayment Calculator with Offset Account and Extra Payments [Free Download]

Get FREE Advanced Excel Exercises with Solutions!

A mortgage repayment calculator is an essential tool to calculate and track the loan repayment process. A borrower can visualize his due payment, interest paid, principal paid, and remaining loan balance after each payment through this calculator.

When paying off a loan, the borrower might get multiple options for interest compounding frequency, regular payment frequency, extra regular payment frequency, and many other variables. Borrower can repay his loan quicker than the initial deadline by making some extra payments. Moreover, he can reduce his interest by connecting an offset account with his loan account.

Download our free Excel Mortgage Repayment Calculator with Offset Account and Extra Payments to calculate and visualize your loan repayment with offset account and extra payments.

This calculator is very crucial for investors, bankers, finance planners, and all types of lenders and borrowers who are looking forward to taking or giving a loan.

Mortgage Repayment Calculator with Offset Account and Extra Payments

Click here to enlarge the image

Download Excel Calculator

Download Excel Calculator

For: Excel 2007 or later
License: Private Use


What Is Mortgage Repayment Calculator?

A mortgage repayment calculator is a loan payoff calculator containing all required loan parameters and the following output summary along with an amortization schedule and a summary chart.

The amortization schedule shows the payment date, due payment, interest paid, principal paid, and remaining balance after each payment. The summary table shows the final outputs along with time saved and interest savings by following any special approach. And, the summary chart shows the trend for interest paid, principal paid, and balance after each payment.

What Is Mortgage Repayment Calculator with Offset Account?

According to the AMP website, an offset account is an account that can be linked with loan account. The credit balance of this offset account will be deducted from the loan balance to calculate the interest rate and that would result in lesser interest to be paid by the lender.

For example, a borrower took a $200,000 loan from the bank. Now, he has linked an offset account of a $20,000 balance with the loan account. So he has to pay interest for $(200,000-20,000) = $180,000 which would definitely result in lesser interest than the actual loan interest.

This type of calculator deals with this type of loan repayment calculating all necessary outputs along with time savings and estimated interest savings.

What Is Mortgage Repayment Calculator with Extra Payments?

Making extra payments when repaying a loan is a smart approach to repay the loan quicker than the initial deadline and save some interest to be paid. If somebody takes a loan with a fixed due payment for 30 years and after a while, if he makes some extra payments (regular or irregular), then he would be able to repay his loan before 30 years. It would save him some time and interest to be paid.

This type of calculator deals with this type of approach and calculates all necessary outputs along with the time savings and estimated interest savings.

Read More: Biweekly Mortgage Calculator in Excel with Extra Payments


Mortgage Repayment Excel Calculator with Offset Account and Extra Payments

This calculator will take all necessary inputs of loan parameters including loan balance, loan term, offset balance, payment frequency, extra payment amount, etc. It will give a summary table containing all necessary outputs along with time savings and estimated interest savings. It will give an amortization table to visualize every payment and a summary chart to visualize the trend for interest paid, principal paid, and balance.

How to Use This Template

Follow the instructions below to use this template efficiently.

Instructions:

  • Open the calculator and insert all the required inputs in the blue shaded area according to the Loan Details column.
Insert Input Values

Click here to enlarge the image

  • After inserting all necessary inputs, you will get the following Payment Amount. You have to enter extra irregular payments manually in the Extra Payment (Irregular) column if you have plans to make some irregular extra payments.
Insert Irregular Extra Payments

Click here to enlarge the image

  • Finally, you will get a summary table containing all necessary outputs along with time saved and estimated interest savings.
  • You will also get an amortization table and a summary chart to visualize your loan repayment.

 

Mortgage Repayment Calculator with Offset Account and Extra Payments

Click here to enlarge the image

Read More: Mortgage Calculator with Extra Payments and Lump Sum in Excel


Mortgage Repayment Calculator with Offset Account and Extra Payments Tips

  • Insert all the inputs and select all the dropdowns properly according to the parameters.
  • When selecting dropdown, be careful to select Interest Compounding Frequency as greater than the Regular Payment Frequency. You will find a warning error if you don’t do so.
  • When selecting the dropdown, be careful to select the Extra Payment Frequency individually different each time according to the Regular Payment Frequency. You will get a warning here too if you do not choose accordingly.

Further Readings


<< Go Back to Mortgage CalculatorFinance TemplateExcel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo