Excel Mortgage Repayment Calculator with Offset Account and Extra Payments

What Is Mortgage Repayment Calculator with Offset Account?

According to the AMP website, an offset account can be linked to a loan account. The credit balance of this offset account will be deducted from the loan balance to calculate the interest rate, resulting in less interest paid by the lender.

For example, a borrower took a $200,000 loan from the bank. He linked an offset account with 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 less interest than the actual loan interest.

This 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), he can repay his loan before 30 years. It would save him some time and interest to be paid.

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


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

  • Get a summary table containing all necessary outputs along with time saved and estimated interest savings.
  • 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

 


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 a dropdown, be careful to select the Interest Compounding Frequency as greater than the Regular Payment Frequency. If you don’t, you will receive a warning error.
  • When selecting the dropdown, be careful to select the Extra Payment Frequency individually, different each time according to the Regular Payment Frequency. You will also get a warning here if you do not choose accordingly.

Download Excel Calculator

Download Excel Calculator

For: Excel 2007 or later
License: Private Use


Further Readings


<< Go Back to Mortgage CalculatorFinance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
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