Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Offset Mortgage Calculator in Excel

offset mortgage calculator excel

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


Download Practice Workbook

Download this practice workbook to exercise while reading 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.


Step-by-Step Procedures to Create Offset Mortgage Calculator in Excel

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 (with Alternative)


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.

Read More: Mortgage payoff calculator with extra principal payment (Free Template)


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

Alif Bin Hussain

Alif Bin Hussain

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Civil Engineering from Bangladesh University of Engineering & Technology (BUET). I am a fresh graduate with a great interest in research and development. I do my best to acquire new information and try to find out the most efficient solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo