Mortgage Calculator Hawaii: 10 Easy Steps

Mortgage Calculator Hawaii Edition

Whether you are homeowner in Hawaii, potential home buyer in the state or just interested in how mortgages work in this state. There’s a lot to learn in this article and we’ve made it very straight forward. The article below outlines how to build a mortgage calculator for Hawaii in 10 easy steps.

There are no shortages of mortgage calculators online. However, very few homeowners and home buyers understand how their mortgage payments are calculated. In addition to this, there are different expenses unique to specific states that need to be addressed. You’ll be happy to hear that the math behind mortgage calculators is not especially complicated. And so, we have put together a 10-step guide to help you build your own mortgage calculator in a simple spreadsheet!

Why You Should Build Your Own Mortgage Calculator

There are two reasons you should consider building a mortgage calculator. First, it’s a great way to learn how mortgages work and how your mortgage payment is calculated. By walking through the building blocks and learning what each component means, you gain a deep understanding. Second, building your own mortgage calculator allows you to customize the inputs and you get the added benefit of saving all the assumptions you enter, locally on your computer. The first downside of relying on online mortgage calculators is you’re always re-entering the inputs. The second downside of relying on online mortgage calculators is that if the site is down or your internet access is disconnected for whatever reason, you’re out of luck.

Building a Mortgage Calculator for Hawaii

There are a few important considerations when building a mortgage calculator for Hawaii state. These are several facts about home buying and mortgage lending in Hawaii:

• Median home values in Hawaii are approximately $650,000 – interestingly, the prices vary significantly among the Islands. The average price for single family homes sold in Honolulu and Maui were $1,000,000 in 2020 while in Hawaii it was $535,000!
• Property tax rates in Hawaii are approximately 0.30% of the home value, which is amongst the lowest in the United States
• The average homeowner in Hawaii pays $1,100 a year in homeowners insurance.

Getting Started

Before we dive into the steps, you’ll need a desktop or laptop computer, and access to Microsoft Excel. Other spreadsheet software such as Google Sheets is also fine, but ideally you have Microsoft Excel. Unfortunately, you won’t be able to follow these steps on a mobile phone or tablet. Once you’ve fired up your computer and opened excel, proceed to step 1!

Step 1

It’s a good idea to use the same cells as shown in the above as this guide provides very detailed instructions. In each step, you should be entering all of the information shown in the red box. These are the instructions for step 1:

• Enter the words Home Price in cell A5.
• Next, enter the words Down Payment Percentage in cell A6.
• Then, enter the words Down Payment Amount in cell A7.
• In cell C5 enter the value of the home you’re looking to buy. For the time being just use $650,000. This way you’ll be able to compare your calculator with ours to make sure you built it correctly.
• Next, in cell C6 enter the percentage of the home value you expect to pay as a down payment. As we previously covered in our first-time home buyer guide, on average home buyers in the U.S. have a down payment equal to 20% of the home value. For the purposes of this example, we’ve assumed 20%.
• Next, in cell C7 we are trying to calculate the down payment amount. Since we have the home price in C5 and the down payment percentage in C6, the down payment amount is simply the product of the two. So, for cell C7 enter the following (don’t forget the equal sign): =C5*C6
• If you’re getting $130,000 in cell C7, you’re ready to move on to the next step!

Step 2

Now that we have the home price and down payment, we can calculate the mortgage loan that is needed. This step is very easy:

• Enter the words Mortgage Loan Needed in cell A8
• In cell C8 we are going to calculate how much of a mortgage loan we need to buy the home based on the home price and the down payment. Another way to think about the mortgage loan is the amount you need to borrow to buy the house. It is the difference between the home price and down payment. Therefore, in cell C8 enter the following calculation (don’t forget the equal sign): =C5-C7.
• If you’re getting $520,000 in cell C8, you’re ready to move on to the next step!

Step 3

The mortgage interest rate is an important variable and represents the cost of financing. This is what your lender or bank is charging you for the loan. Usually, a mortgage pre-approval letter will state the interest rate the bank has pre-approved you for. For the purposes of this example, we’ve assumed a 3.0% annual interest rate on the mortgage, which is what the national average is at the time of this writing.

• Enter the words Mortgage Interest Rate in cell A9
• Next, enter the words Mortgage Interest Rate Per Month in cell A10
• In cell C9 enter the interest rate you expect to pay for your mortgage. For the time being enter 3.0%
• Then, in cell C10 we want to calculate the monthly rate of interest using the annual rate of interest we expect to pay. Since there are 12 months in a year, we simply want to divide the annual rate of interest by 12. So, in cell C10 enter the following: =C9/12

Step 4

The mortgage loan term is also another important variable. This represents how many years you will need to fully pay back the mortgage loan. Choosing a longer loan term will lower your monthly payment, but it also increases the period you are paying interest. Generally home buyers get mortgages with a 30-year loan term which is what we have assumed, but it is possible to have shorter loan terms.

• Enter the words Mortgage Loan Term in Years in cell A11.
• Then, enter the words Mortgage Loan Term in Months in cell A12.
• In cell C11 enter the mortgage loan term you expect to get a mortgage for. For the time being enter 30 years.
• Next, in cell C12 we simply want to convert the loan term from years to months. Since there are 12 months in a year, we just have to multiply by 12. And so, in cell C12 enter the following: =C11*12

Step 5

This is easily the most important and complicated step in the process. We’ve so far inputted all the pieces of information needed to calculate your mortgage monthly payment. The calculation itself involves properly utilizing the mortgage formula. The origins of this formula and how it works is actually pretty neat but for 99% of home buyers all you really need to know is that it works and that all banks and lenders use it.

• Enter the words Mortgage Monthly Payment Calculation in cell A13.
• In cell C13 we are going to make use of the mortgage formula and the mortgage loan needed (cell C8), mortgage interest rate per month (cell C10) and mortgage loan term in months (C12).
• Next, in cell C13 enter the following: =C8*((C10*(1+C10)^C12)/((1+C10)^C12-1))
• If you’re getting $2,192, yay! well done! If you’re not, double check your formula and make sure you’re not missing a parenthesis, for example.

Step 6

For this step, since we’re building a mortgage calculator for Hawaii, we want to enter property tax information for Hawaii state. Our research indicates that on average Hawaii residents pay 0.30% of their home value in property tax, so we have used that for this example.

• Enter the words Hawaii Average Property Tax Rate in cell A14.
• Next, enter the words Hawaii Average Property Tax Rate Per Month in cell A15.
• In cell C14 enter the property tax rate you currently pay. For the time being enter 0.30%.
• Then, in cell C15 we simply want to calculate what the property tax rate implies based on the home price. So we want to multiply the property tax rate and the home price. Then, we want to divide it by 12 to get the monthly property tax. Therefore, in cell C15 enter the following: =C14*C5/12

Step 7

Homeowners insurance is not mandated by law across the U.S. but is often a requirement by mortgage lenders. How much is homeowners insurance you pay will be a function of where the home is located and the value of the home, in addition to other factors. Our research suggests that in Hawaii homeowners pay approximately $1,200 a year for homeowners insurance, or $100 a month.

• Enter the words Hawaii Average Homeowners Insurance Per Month in cell A16.
• In cell C16 enter the monthly amount you expect to pay for homeowners insurance. For the time being enter $100.

Step 8

As noted earlier, mortgage insurance is often not needed if your down payment represents 20% of the home price or more. However we added this step just in case. On average home buyers that need mortgage insurance will pay 0.50% of the home price each year. You can adjust this assumption as needed.

• Enter the words Will You Need Mortgage Insurance? in cell A17.
• Next, enter the words Mortgage Insurance Rate in cell A18.
• Then, enter the words Mortgage Insurance Payment Per Month in cell A19.
• In cell C17 we are going to build simple logic into this cell as part of our calculator. The basis of the logic is as follows: if the answer to the question “will you need mortgage insurance” is yes, then input 1 in cell C17. If it is no, then input 0 in cell C17. To represent this thinking enter the following in cell C17: =IF(C6<20%,1,0)
• Next, in cell C18 enter the mortgage insurance rate. For this example, we are assuming 0.50%. Don’t forget that this is an annual rate and relates to the home price.
• Then, in cell C19 we want to calculate how much we will be paying in mortgage insurance per month. Clearly, if we don’t need mortgage insurance the amount will be zero. This is where the logic we built into cell C17 comes into play.
• In cell C19 enter the following: =C18*C5*C17/12.

Step 9

We’re now on the home stretch!

• Enter the words Other Monthly Fees and Charges in cell A20.
• In cell C20 enter any additional monthly expenses you expect to have that relate to your mortgage or financing your home. This will usually be zero but some homes could have other expenses like homeowner association fees. For the time being enter $0.

Step 10 – Wrapping Up Your Mortgage Calculator For Hawaii

Congratulations on making it this far. In many ways this is the easiest step and hopefully the most satisfying. Having done all the hard work building your mortgage calculator for Hawaii state, we want to pull together all the pieces.

• Enter the words Total Payment in cell A21.
• In cell C21 we want add up the 5 components of the monthly payment (1) the mortgage monthly payment (2) monthly property tax (3) monthly homeowners insurance (4) monthly mortgage insurance and (5) other monthly fees and charges.
• In cell C21 enter the following: =C13+C15+C16+C19+C20.
• If you’re getting $2,455, excellent work and well done! If you’re not, quickly recheck each step and make sure you’re getting the right values.

Final Thoughts

Now that you’ve built your own calculator you can change any of the assumptions to fit your current situation. Remember that Hawaii is a unique among the states in that it consists of a grouping of islands. Depending on where in Hawaii you’re looking to buy the prices might vary quite significantly.