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

offset mortgage calculator excel
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.

offset mortgage calculator excel


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

Insert Mortgage Details to Create Offset Mortgage Calculator in Excel

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.
=-PMT($D$6/12,$D$7*12,$D$5)
  • Then press Enter to get the value of the Monthly Payment.

Calculate Monthly Payment to Create Offset Mortgage Calculator in Excel

  • After that, click on cell C11. Then type the following formula and hit Enter.
=$D$6/12

Calculate Monthly Interest Rate to Create Offset Mortgage Calculator in Excel

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

Calculate Monthly Interest to Create Offset Mortgage Calculator in Excel

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

AutoFill Formula to Create Offset Mortgage Calculator in Excel

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

Determine Offset Mortgage to Create Offset Mortgage Calculator in Excel

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

AutoFill Formula

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

Calculate Monthly Payment with Offset to Create Offset Mortgage Calculator in Excel

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

AutoFill Formula to Create Offset Mortgage Calculator in Excel

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

Determine Monthly Interest with Offset

  • Then click on cell H11 and type the formula given below.
=D11-G11
  • After that, press Enter to get the value of Interest Saved.

Calculation of Interest Saved

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

Apply AutoFill Feature

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.

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


<< 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.
Alif Bin Hussain
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo