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

Get FREE Advanced Excel Exercises with Solutions!

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

### 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.
`=-PMT(\$D\$6/12,\$D\$7*12,\$D\$5)`
• 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.
`=\$D\$6/12`

• Now select cell D11 and write the formula given below.
`=B11*C11`
• Then press the Enter button on your keyboard.

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

### 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.
`=\$D\$5-\$D\$8`
• Hence, hit Enter and you will get the desired Offset Mortgage.

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

### 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.
`=-PMT(\$D\$6/12,\$D\$7*12,E11)`
• After that, get the result by pressing Enter.

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

### 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.
`=F11*C11`

• Then click on cell H11 and type the formula given below.
`=D11-G11`
• 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.

Notes
• Donâ€™t forget to give proper cell references or you wonâ€™t get the desired results.
• While using the calculator, only insert the values of Mortgage Details.

## 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

<< Go Back to Mortgage Calculator |Â Finance Template |Â Excel 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.
Alif Bin Hussain

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF