Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel

While dealing with the loan related to the mortgage or simply mortgage, you may need to utilize the calculator for faster calculation. In this article, I’ll show the mortgage repayment calculator with offset account and extra payments in Excel.

Download Template

What Is an Offset Account and Its Benefits?

Basically, an offset account is a linked bank account with the loan. This type of account is highly profitable, especially in the case of a home loan or mortgage. Because it decreases the interest rate as well as the principal amount from which the interest rate is beset with. In a more simple way, you can say that you’ll be free of charge from the interest on the balance of the offset account.

For example, if you have a mortgage loan amount of $300000 and an offset balance of $50,000, you have to pay interest in the amount of $250000.

Why Is Extra Payment Profitable?

At first thought, you might think that extra payment is not profitable as you have to pay higher than the actual payment rate. But extra payment gives at least two benefits.

Firstly, it’ll decrease the interest because the interest is calculated based on the rest amount. If you pay higher than the rest, the amount will be lower.

Secondly, extra payment reduces the pay-off years of your loan. That’s the blessing! Everyone wants to be free from debt and the burden of high-interest rates.

Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel

In this section, I am going to show you two calculators separately. The first one is about the mortgage repayment with extra payments and the last one is about the repayment with the offset account.

1. Mortgage Repayment Calculator with Extra Payments

Let’s say, you have a mortgage loan with the following details:

• Mortgage loan amount: $300000
• Annual interest rate: 4.87%
• Terms of Mortgage (in Years): 10
• Extra Payment (Monthly): 10

Note: In the case of your specific mortgage amount, you may also input them in the corresponding cell of the template after downloading.

Here, the extra monthly payment is $100 that will be added to your actual monthly payment i.e. $3162.94 (calculated using the PMT function in the I5 cell). Apart from this, I used the following formula using the same function to find the monthly payment with extra payments in the H5 cell.

Here, D6 is the annual interest rate, D7 is the term of the mortgage, D5 is the mortgage amount and D8 is the extra payment.                                                                                                                                                                             If you specify the inputs, you’ll see the comparison (G4:I9 cells) between extra payments and without extra payments. Also, it is a summary of two payment systems. From the comparison, it is clear that the number of payments and total interest in the case of extra payments is lower than that without extra payments. To get the number of payments, the following formula is used.

Total payments are calculated using the below formula.

The following formula is for finding the total interest.

More importantly, the amount of saved interest is $3304.25, which is not a negligible amount definitely. However, look at the following screenshot (located in the lower part of the working sheet) which displays the details calculation for each payment with Payments Number (116 months), Monthly Payment, Interest, Cumulative Interest, Principal with Cumulative Principal, the net Balance and lastly the Interest Savings. The below formulas are used to get the outputs.

For specifying the monthly payment for each payment:

Formula for Interest for each payment:

Formula for Cumulative Interest:

Formula for Principal:

Formula for Cumulative Principal:

Formula for Balance:

Formula for Interest Savings:

Note: This complex dataset will be automatically updated with your input data. Besides, you may also utilize the mortgage repayment calculator with extra payments and lump sum.

Read More: Mortgage Calculations with Excel Formula (5 Examples)

2. Mortgage Repayment Calculator with Offset Account

In addition, you may utilize the following calculator to compute the payments and interest if you have an offset balance.

Assuming that, you have the following mortgage loan details:

• Mortgage loan amount: $300000
• Annual interest rate: 4.87%
• Terms of Mortgage (In Years): 10
• Offset Balance: $50,000

If you insert the inputs, you’ll get the following loan summary as shown in the following screenshot.

Undoubtedly, the payments with an offset balance reduced the interest amount. The overall interest amount is $16293.67 in the case of having an offset account. Else it is $79,552.40.

However, if you look closely at the lower part of the working sheet, you’ll see the monthly payment with the offset is $2635.78 whereas it is $3162.94 in the case of no offset account. More significantly, the interest savings is $2.14 for each monthly payment. Look closely at what formulas I have used.

Formula for Monthly Interest Rate:

Formula for Monthly Interest:

Formula for deducting the loan from the offset balance:

Formula for Monthly Interest with offset:

Formula for Interest Savings:

Read More: How to Use Formula for Mortgage Principal and Interest in Excel

Conclusion

That’s the end of today’s session. And you may utilize the mortgage repayment calculator with offset account and extra payments in Excel. Anyway, if you have any queries or suggestions, please share them in the comments section below.

Further Readings

• Interest Only Mortgage Calculator with Excel Formula (A Detailed Analysis)
• Loan Amortization Schedule with Variable Interest Rate in Excel
• Excel Interest Only Amortization Schedule with Balloon Payment Calculator
• How to Use Formula for Car Loan Amortization in Excel (with Quick Steps)
• Creation of a Mortgage Calculator with Taxes and Insurance in Excel
• How to Use Formula for 30 Year Fixed Mortgage in Excel (3 Methods)