Moneyvests.com - How To Make Money Tips, Save Money and Invest Money
Wednesday, July 6, 2022
  • Home
  • How to Make Money
  • Investment Tips
    • Real Estate
    • Law
  • Crypto
  • More..
    • Save Money
    • Money Advisor
    • Affirmations
    • Banking
    • Finance
    • Education
  • Contact
    • About Us
    • Privacy Policy
    • DMCA Notice
    • Disclaimer
    • Terms & Conditions
No Result
View All Result
  • Home
  • How to Make Money
  • Investment Tips
    • Real Estate
    • Law
  • Crypto
  • More..
    • Save Money
    • Money Advisor
    • Affirmations
    • Banking
    • Finance
    • Education
  • Contact
    • About Us
    • Privacy Policy
    • DMCA Notice
    • Disclaimer
    • Terms & Conditions
No Result
View All Result
Moneyvests.com - How To Make Money Tips, Save Money and Invest Money
No Result
View All Result
Home Finance

How to create a mortgage calculator in Microsoft Excel (21 Things to Know)

moneyvests by moneyvests
April 17, 2022
in Finance
0
How to create a mortgage calculator in Microsoft Excel
0
SHARES
12
VIEWS
Share on FacebookShare on Twitter

RELATED POSTS

What is a Cut-off in Finance?

What is Liability Definition?

Table of Contents

  • How to create a mortgage calculator in Microsoft Excel
    • #1. Creating a Mortgage Calculator
      • Method 2
    • Making a Payment Schedule (Amortization)
      • Incorporate the Loan formula.
      • Fact Check

How to create a mortgage calculator in Microsoft Excel

When it comes to mortgage-related expenses like interest and monthly payments, one of the most useful features of Excel is its ability to calculate them.

Even if you are not highly familiar with Excel operations, creating a mortgage calculator in Excel is a straightforward process.

This article will show you how to design a mortgage calculator as well as an amortization schedule in Microsoft Excel using the formulas provided.

Method No. 1

#1. Creating a Mortgage Calculator

Creating a Mortgage Calculator

Open the spreadsheet program on Microsoft Excel. If you don’t have Excel installed on your computer, you can substitute Outlook’s online Excel extension for the functionality of the spreadsheet program. It’s possible that you’ll need to create an Outlook account first.

Choose a blank workbook from the drop-down menu

Choose a blank workbook from the drop-down menu. This will create a new Excel spreadsheet on your browser.

 

Create a column for "Categories" 
Create a column for “Categories”  This will go in the “A” column of the spreadsheet.
This will require first moving the separator between columns “A” and “B” to the right at least three spaces in order to ensure that you do not run out of space to write on the next page. There will be a total of eight cells required 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

 

Annual Interest Rate

Enter your values. All of them will be placed in your “B” column, which will be just to the right of your “Categories” column.

You’ll need to fill in the blanks with the right numbers for your mortgage.

  • Your Loan Amount value is the total amount you owe.
  • Your Annual Interest Rate value is the percentage of interest that accrues each year.
  • Your Life Loan – The loan’s worth is the period of time you have to pay it off, expressed in years.
  • Your Number of Payments per Year value is how many times you make a payment in one year.
  • Your Total Number of Payments value is the Life Loan value multiplied by the Payments Per Year value.
  • Your Payment per Period value is the amount you pay per payment.
  • Your Sum of Payments value covers the total cost of the loan.
  • Your Interest Cost value determines the total cost of the interest over the course of the Life Loan value.
See also
 CANDLESTICK STRATEGY

 

Calculate the total number of payments

Calculate the total number of payments that will be made. It is not necessary to use a formula to compute this amount because it is equal to your Life Loan value multiplied by your Payments Per Year value.

You would enter “360” in this field if you were making a payment every month on a 30-year life loan.

mortgage calculator in Microsoft

The formula is “-PMT(B6/B8,B9,B5,0)” in the screenshot supplied above. If your values are somewhat different from the ones in the table, enter them in the corresponding cell numbers.

When you place a negative sign in front of PMT, this means that PMT refunds the amount that will be taken from the amount owing to the company.

Calculate the total cost of the loan
Calculate the total cost of the loan.

 

To do so, simply multiply the value of your “payment per period” by the value of your “total number of payments” value.

Consider the following example: if you make 360 payments of $600.00 each, the total cost of the loan will be $216,000.

mortgage calculator in Microsoft Excel
Calculate the total interest cost. All you have to do now is subtract the amount of your initial loan from the overall cost of the loan, which you estimated previously. After you’ve completed this step, your mortgage calculator will be complete.

Method 2

Making a Payment Schedule (Amortization)

mortgage calculator in Microsoft Excel
Create your Payment Schedule template to the right of your Mortgage Calculator template.

Because the Payment Schedule relies on the Mortgage Calculator to provide you with an accurate estimate of how much you’ll owe/pay off each month, both documents should be included in the same package. It is necessary to have a separate column for each of the following categories:

  • Date – The date the payment in question is made.
  • Payment (number) – The payment number out of your total number of payments (e.g., “1”, “6”, etc.).
  • Payment ($) – The total amount paid.
  • Interest – The amount of the total paid that is interest.
  • Principal – The amount of the total paid with no interest (e.g., loan payment).
  • Extra Payment – The dollar amount of any extra payments you make.
  • Loan – The amount of your loan that remains after a payment.

 

Microsoft Excel loan calculator
Add the original loan amount to the payment schedule. In the “Loan” column, this will be placed in the first vacant cell at the very top.

 

mortgage calculator in Microsoft Excel

Set up the first three cells in your “Date” and “Payment (Number)” columns.

 

To complete the date column, you’ll need to fill in the date on which you took out the loan, as well as the first two dates on which you intend to make the monthly payment (for example, 2/1/1999, 3/1/1999, and 4/1/1999).
In the Payment column, enter the first three payment numbers that have been received (e.g., 0, 1, 2).
creating a mortgage calculator in Microsoft Excel
Use the “Fill” function to automatically enter the rest of your Payment and Date values. To do so, you’ll need to perform the following steps:
  • Select the first entry in your Payment (Number) column.
  • Try to drag your cursor down until you’ve highlighted the number that applies to the number of payments you’ll make (for example, 360).
  • Since you have started at “0”, you’d drag down to the “362” row.
  • Click Fill in the top right corner of the Excel page.
  • Select Series.
  • Make sure “Linear” is checked under the “Type” section (when you do your Date column, “Date” should be checked).
  • Click OK.
See also
Top 2 Churches That Help With Rent - Find out why they do it
How to create a mortgage calculator in Excel
Select the first empty cell in the “Payment ($)” column.
loans mortgage calculator mortgage calculator
Enter the Payment per Period formula. The formula for calculating your Payment per Period value is as follows:
“Payment per Period, Total Loan+(Total Loan*(Annual Interest Rate/Number of Payments per Year)), Payment per Period, Total Loan+(Total Loan*(Annual Interest Rate/Number of Payments per Year)), Payment per Period, Total Loan+(Total Loan*(Annual Interest Rate/Number of Payments per Year))).
  • You must preface this formula with the “=IF” tag to complete the calculations.
  • Your “Annual Interest Rate”, “Number of Payments per Year”, and “Payment per Period” values will need to be written like so: $letter$number. For example: $B$6
  • Given the screenshots here, the formula would look like this: “=IF($B$10<K8+(K8*($B$6/$B$8)),$B$10, K8+(K8*($B$6/$B$8)))” (sans the quotation marks).
loans mortgage calculator
Press ↵ Enter.

This will populate the specified cell with the Payment per Period formula.

To ensure that this formula is applied to all consecutive cells in this column, you must employ the “Fill” option that you previously used.

mortgage Interest calculator

Select the first empty cell in the “Interest” column.

 

calculator your mortgage

Enter the formula for calculating your Interest value.
The formula used to calculate your interest value is as follows: “Total Loan*Annual Interest Rate/Number of Payments per Year”.
  • This formula must be prefaced with a “=” sign in order to work.
  • In the screenshots provided, the formula would look like this: “=K8*$B$6/$B$8” (without quotation marks).

calculating your Interest value

Press ↵ Enter.

This will apply the Interest formula to the cell that has been selected.

To ensure that this formula is applied to all consecutive cells in this column, you must employ the “Fill” option that you previously used.

mortgage calculator Interest formula
Select the first empty cell in the “Principal” column.

 

Interest calculator on your mortgage

Enter the Principal formula.
  • Simply deduct the “Interest” value from the “Payment ($)” value in this formula.For instance, if the “Interest” field is H8 and the “Payment ($)” cell is G8, you would type “=G8 – H8” without quotation marks.
mortgage calculator in Excel
Press ↵ Enter.

This will apply the Principal formula to the cell that has been selected.

To ensure that this formula is applied to all consecutive cells in this column, you must employ the “Fill” option that you previously used.

See also
How to Take Online Surveys for Money: A Quick Guide
Microsoft Excel loan calculator
It is necessary to select the first empty cell in the “Loan” column from the list. This sum should be equal to or less than the amount of the initial loan you acquired (e.g., the second cell in this column).
mortgage Interest calculator

Incorporate the Loan formula.

The following formula is used to determine the loan’s worth: “Loan“-“Principal“-“Extra.”

For the screenshots that were provided, you’d type “=K8-I8-J8” without quotation marks.

Excel mortgage calculator Interest

Press ↵ Enter.

This will apply the Loan formula to the cell that you have selected.

Use the “Fill” tool, which you previously used to apply this formula to all subsequent cells in this column, in order to apply it to all subsequent cells in this column.

calculator loans mortgage Interest

Fill functions are used to fill in the blanks in your formula columns.

Your payment should be the same from the beginning to the end. The interest rate and loan amount should go down, but the values of the principal should go up in value.

Interest calculator on your mortgage calculator

The payment schedule should be added up. The total of the payments, interest, and principal may be found at the bottom of the table.

Compare these figures to the ones provided by your mortgage calculator. If they are the same, you have completed the formulas correctly.

  • The amount of your principal should be the same as the amount of the original loan.
  • The overall cost of the loan, as calculated by the mortgage calculator, should be the same as your monthly installments.
  • Your interest rate should be the same as the interest rate calculated by the mortgage calculator.

Fact Check

We hope you enjoyed this article… What are your thoughts?

 

Please feel free to share with us in the comments section below.

We strive to provide the latest valuable information for our readers with accuracy and fairness. If you would like to add to this post or advertise with us, don’t hesitate to contact us.  If you see something that doesn’t look right, contact us!

Post Views: 603
Tags: microsoft excelmortgage calculatormortgage calculator in excel
ShareTweetPin
moneyvests

moneyvests

Related Posts

cut-off in finance

What is a Cut-off in Finance?

by moneylife
June 30, 2022
0

What is a Cut-off in Finance? Cut-off, called easy reduce is a reinsurance settlement provision that stops the reinsurer from...

What is Liability Definition?

What is Liability Definition?

by moneyvests
June 17, 2022
0

What is Liability Definition?   The word liability is defined as being legally responsible or obligated. Liability concerns both criminal...

Penny Stocks - What is Penny Stocks Definition?

Penny Stocks – What is Penny Stocks Definition?

by moneyvests
June 17, 2022
0

What is Penny Stocks Definition?   A penny stock is a common share in a small, public company that trades...

Philanthropy 101 - What is Equity Definition?

Philanthropy 101 – What is Equity Definition?

by moneyvests
June 17, 2022
0

What is Equity Definition?     What is Equity Definition? Simply put, it's the ownership of assets, less the debts...

why use a consultant

Why Use a Consultant for Your Business?

by moneylife
June 16, 2022
0

Why Use a Consultant for Your Business? In the contemporary financial environment, agencies must locate methods to face out, tighten...

Next Post
Forgiveness program for student loans

Forgiveness program for student loans: 21 Things to Know (Ultimate Guide)

How much savings should you have to Save for an Emergency Fund?

How much savings should you have to Save for an Emergency Fund?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

4 × five =

Recent Posts

  • Next Earth is unstoppable: plans the biggest Metaverse Fund July 6, 2022
  • Web Analytics Tools | Frequently Asked Google Analytics Questions July 4, 2022
  • How Yuri and Julia Milner and Other Notable Members of The Giving Pledge Donate Their Wealth July 3, 2022
  • What is a Cut-off in Finance? June 30, 2022
  • How To Calculate Your Crypto Trading Profit And Loss June 26, 2022

RECOMMENDED

next earth

Next Earth is unstoppable: plans the biggest Metaverse Fund

July 6, 2022
web analytics tools

Web Analytics Tools | Frequently Asked Google Analytics Questions

July 5, 2022
  • 85.9k Followers
  • 650 Followers
  • 23.6k Followers

MOST VIEWED

  • Top 15 Bob Proctor Money Affirmations for Attracting Money and Wealth

    Top 15 Bob Proctor Money Affirmations for Attracting Money and Wealth

    0 shares
    Share 0 Tweet 0
  • Top 5 Charities That Help Single Mothers 2022

    0 shares
    Share 0 Tweet 0
  • Top 5 Grants For Single Mothers (Find out here…)

    0 shares
    Share 0 Tweet 0
  • How to make money on TikTok by watching videos – 6 Tips to Know

    0 shares
    Share 0 Tweet 0
  • How to make money on YouTube for Beginners in 2022 [+ 5 Common Mistakes]

    0 shares
    Share 0 Tweet 0
Moneyvests.com – How To Make Money Tips, Save Money and Invest Money

How to make money today with MoneyVests.com - We provide the latest tips for making money, save money, finance, investing, insurance, crypto, and more...

  • Trending
  • Comments
  • Latest
What is online credit card generators and what are their Uses 

What is online credit card generators and what are their Uses 

July 6, 2022
Top 15 Bob Proctor Money Affirmations for Attracting Money and Wealth

Top 15 Bob Proctor Money Affirmations for Attracting Money and Wealth

May 16, 2021
Top 5 Charities That Help Single Mothers

Top 5 Charities That Help Single Mothers 2022

May 3, 2022
next earth

Next Earth is unstoppable: plans the biggest Metaverse Fund

July 6, 2022
web analytics tools

Web Analytics Tools | Frequently Asked Google Analytics Questions

July 5, 2022
How Yuri and Julia Milner and Other Notable Members of The Giving Pledge Donate Their Wealth

How Yuri and Julia Milner and Other Notable Members of The Giving Pledge Donate Their Wealth

July 3, 2022
ADVERTISEMENT
  • About Us
  • Terms
  • Privacy Policy
  • DMCA Notice
  • Disclaimer
  • Contact

© 2022 - www.moneyvests.com - All rights Reserved.

No Result
View All Result
  • Homepages
    • Homepage Layout 1
    • Homepage Layout 2
  • Business
  • Entertainment
  • Tech

© 2022 - www.moneyvests.com - All rights Reserved.

en English
af Afrikaanssq Albanianam Amharicar Arabichy Armenianaz Azerbaijanieu Basquebe Belarusianbn Bengalibs Bosnianbg Bulgarianca Catalanceb Cebuanony Chichewazh-CN Chinese (Simplified)zh-TW Chinese (Traditional)co Corsicanhr Croatiancs Czechda Danishnl Dutchen Englisheo Esperantoet Estoniantl Filipinofi Finnishfr Frenchfy Frisiangl Galicianka Georgiande Germanel Greekgu Gujaratiht Haitian Creoleha Hausahaw Hawaiianiw Hebrewhi Hindihmn Hmonghu Hungarianis Icelandicig Igboid Indonesianga Irishit Italianja Japanesejw Javanesekn Kannadakk Kazakhkm Khmerko Koreanku Kurdish (Kurmanji)ky Kyrgyzlo Laola Latinlv Latvianlt Lithuanianlb Luxembourgishmk Macedonianmg Malagasyms Malayml Malayalammt Maltesemi Maorimr Marathimn Mongolianmy Myanmar (Burmese)ne Nepalino Norwegianps Pashtofa Persianpl Polishpt Portuguesepa Punjabiro Romanianru Russiansm Samoangd Scottish Gaelicsr Serbianst Sesothosn Shonasd Sindhisi Sinhalask Slovaksl Slovenianso Somalies Spanishsu Sudanesesw Swahilisv Swedishtg Tajikta Tamilte Teluguth Thaitr Turkishuk Ukrainianur Urduuz Uzbekvi Vietnamesecy Welshxh Xhosayi Yiddishyo Yorubazu Zulu