How to Create Offset Mortgage Calculator in Excel (With Easy Steps)

Excel can be a very helpful tool to keep track of your loans and mortgage. You can calculate offset mortgages in Excel to reduce the total interest while paying loans. In this article, we will walk you through five easy steps to create an offset mortgage calculator in Excel. Here’s an overview of the offset mortgage calculator we’ve prepared for this article.

Introduction to Offset Mortgage

Offset mortgages offset the amount you owe on your mortgage against the amount you have in your savings. The amount in your savings account is taken away by borrowers from the amount you owe on your mortgage. The amount of money in your account is not used for paying off your mortgage; rather, it helps reduce the amount of interest.

How to Create Offset Mortgage Calculator in Excel (With Easy Steps)

In this article, we will demonstrate five easy steps to create an offset mortgage calculator in Excel. The steps are described in detail in the following section.

Step 1: Insert Mortgage Details

In the first step, we need to insert the mortgage details, which will be used as inputs.

• Insert the following four data in Mortgage Details.

1. Loan Amount
2. Annual Interest Rate
3. Loan Term (Years)
4. Offset Balance

Read More: How to Create Fixed Rate Mortgage Calculator in Excel

Step 2: Compute Monthly Payment and Interest

In this step, we will use the PMT function to calculate the monthly payment. We will also determine the monthly interest rate.

• First of all, select cell B11 and write down the following formula.

• Then press Enter to get the value of the Monthly Payment.

• After that, click on cell C11. Then type the following formula and hit Enter.

• Now select cell D11 and write the formula given below.

• Then press the Enter button on your keyboard.

• Finally, AutoFill formulas to columns B, C, and D to get the results in all cells.

Read More: Biweekly Mortgage Calculator with Extra Payments in Excel

Step 3: Determine Offset Mortgage

Now we will determine the offset mortgage. We will need the values of the loan amount and offset balance for this purpose.

• First, select cell E11 and write down the following formula.

• Hence, hit Enter and you will get the desired Offset Mortgage.

• After that, AutoFill this formula to the rest of the cells.

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

Step 4: Calculate Monthly Payment with Offset

In this step, we will calculate monthly payments with offset using the PMT function.

• First, click on cell F11 and type the formula given below.

• After that, get the result by pressing Enter.

• Finally, AutoFill this formula to the rest of column F.

Read More: How to Make Chattel Mortgage Calculator in Excel

Step 5: Determine Interest Saved

In the final step, we will determine how much interest can be saved with a $60,000 offset.

• First of all, select cell G11 to write down the following formula and hit Enter.

• Then click on cell H11 and type the formula given below.

• After that, press Enter to get the value of Interest Saved.

• Finally, AutoFill formulas to columns G and H to get your desired results in all cells.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

Conclusion

Thanks for making it this far. I hope you find this article useful. Now you know five easy steps to create an offset mortgage calculator in Excel. Please let us know if you have any further queries, and feel free to give us any recommendations in the comment section below.

Related Articles

• Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel
• Early Mortgage Payoff Calculator in Excel
• How to Create Reverse Mortgage Calculator in Excel
• Calculator for Effective Interest Method of Amortization
• Creation of a Mortgage Calculator with Taxes and Insurance in Excel
• Interest Only Mortgage Calculator with Excel Formula

<< Go Back to Mortgage Calculator | Finance Template | Excel Templates