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

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 an Offset Mortgage Calculator in Excel (With Easy Steps)

Step 1 – Insert Mortgage Details

Insert the following four data points in Mortgage Details.

Loan Amount
Annual Interest Rate
Loan Term (Years)
Offset Balance

Step 2 – Compute the Monthly Payment and Interest

Select cell B11 and use the following formula.

=-PMT($D$6/12,$D$7*12,$D$5)

Press Enter to get the value of the Monthly Payment.

Click on cell C11, insert the following formula, and hit Enter.

=$D$6/12

Select cell D11 and insert the formula given below.

=B11*C11

Press the Enter button on your keyboard.

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

Step 3 – Determine the Offset Mortgage

Select cell E11 and insert the following formula.

=$D$5-$D$8

Hit Enter, and you will get the desired Offset Mortgage.

AutoFill this formula to the rest of the cells.

Step 4 – Calculate the Monthly Payment with Offset

Click on cell F11 and use the formula given below.

=-PMT($D$6/12,$D$7*12,E11)

Hit Enter.

AutoFill this formula to the rest of column F.

Step 5 – Determine the Interest Saved

Select cell G11, insert the following formula, and hit Enter.

=F11*C11

Click on cell H11 and put in the formula given below.

=D11-G11

Press Enter to get the value of Interest Saved.

AutoFill the formulas to columns G and H.

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 the Calculator

Offset Mortgage Calculator.xlsx