Creating Excel Mortgage Formula : Step wise Method

Creating Excel Mortgage Formula for personal mortgage-related expenses like interest, monthly payments, and total loan amount using a Microsoft Excel spreadsheet can be done very methodically.

Once you’ve done this, you may also create a payment schedule that uses your data to generate a monthly payment plan to ensure you pay off your mortgage in time.

Let’s start with it.

Microsoft Excel has all the basic facility of a spreadsheet. It uses a grid of cells which are arranged in numbered rows and letter-named columns to organize data functions like arithmetic operations. It has a array of pre-approved functions to help all the statistical, engineering, and financial needs of a user/company.

In addition to all the above helpful measures, it has the capability to display data as line graphs, histograms and charts, but with a small and limited three-dimensional graphical display. It does allow sectioning of data which helps in viewing its dependencies on various other factors for different perspectives of the speculations and data observations.

Creating Excel Mortgage Formula

Step 1 : Open Microsoft Excel

If you are unable to have Excel installed on your system, you may use Outlook’s online Excel extension in its place. You may require to create an Outlook account first for starting with it.

Step 2 : Select Blank Workbook

This step will open a new Excel spreadsheet.

Step 3 : Create the “Categories” column

This will Tap in the “A” column. To do so, you should first click and drag the divider between columns “A” and “B” to the right at least three spaces so that you might not run out of writing room. You will require total of eight cells for the following categories

  • Loan Amount
  • Annual Interest Rate
  • Life Loan (in years)
  • Number of Payments per Year
  • Total Number of Payments
  • Payment per Period
  • Sum of Payments
  • Interest Cost

Step 4 : Enter the values

These will be written in your “B” column which lies directly to the right of the “Categories” column. You will be required to write the appropriate amount for the mortgage.

  • The Loan Amount value is the total amount owed by you.
  • The Annual Interest Rate value is the percentage of interest that accrues per year.
  • The Life Loan value is the amount of time in years for you to pay off the loan.
  • The Number of Payments per Year value is how many recurring times you have to make a payment in one specific year.
  • The Total Number of Payments value is the Life Loan value multiplied by the Payments Per Year value.
  • The Payment per Period value is the amount you must pay per payment throughout the years.
  • The Sum of Payments value totals the exact cost of the loan.
  • The Interest Cost value evaluates the exact total cost of the interest for the time period of the Life Loan value.
Sample Excel Sheet

Step 5 : Calculate the total number of payments

As we know that as it is the Life Loan value multiplied by the Payments Per Year value, you don’t require a formula to calculate this value.
For example, if you make a payment a month on a 20-year life loan, you would just type in “240”.

Step 6 : Evaluate the monthly payment

To figure out how much you must shell out, on the mortgage each month, use the following formula: “= -PMT(Interest Rate/Payments per Year,Total Number of Payments,Loan Amount,0)”.
If the values are slightly different, input them with the appropriate cell numbers.
Note : We are putting a minus sign in front of PMT because PMT returns the amount to be deducted from the amount owed by you.

Step 7 : Sum up the total cost of the loan

To get this, just multiply the “payment per period” value by the “total number of payments” value. For example, if you make 240 payments of $600.00, the summed up or total cost of the loan would be $144,000.00.

Step 8 : Evaluate the total interest cost

Now, everything is all set. You are just required to do here, is a bit of subtraction. Subtract the initial loan amount from the total cost of the loan that has been calculated above. Once you have done that, the mortgage calculator is complete.

Hopefully, everything stated above, will definitely help you in creating excel mortgage formula for your personal needs.

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More