Alif Bin Hussain
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.
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.
- Loan Amount
- Annual Interest Rate
- Loan Term (Years)
- 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.
=-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.
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.
- 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.
=-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.
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
- 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.
- 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.
