How to create a mortgage calculator with microsoft excel

How to create a mortgage calculator with microsoft excel

How to create a mortgage calculator with microsoft excel

Mortgage Payment Loan Calculator in Excel

Mortgage Calculator in excel is not a built-in feature in excel but we can make our own mortgage calculator using some formulas, to make a mortgage calculator and calculate the amortization schedule we need to create our categories column for all the categories and data to be inserted and then we can use the formula for mortgage calculation in one cell, now for future, we can change the values and we have our mortgage calculator in excel.

Formula to Calculate Mortgage Payment in Excel

PMT function includes 3 mandatory and 2 optional parameters.

All the above three parameters are good enough to calculate the monthly EMI, but on top of this, we have two other optional parameters as well.

  • [FV]: This is the future valueFuture ValueThe Future Value (FV) formula is a financial terminology used to calculate cash flow value at a futuristic date compared to the original receipt. The objective of the FV equation is to determine the future value of a prospective investment and whether the returns yield sufficient returns to factor in the time value of money.read more of the loan. If you ignore this default value will be zero.
  • [Type]: This is nothing but whether the loan repayment is at the start of the month or at the end of the month. If it is at the start, then the argument is 1, and if the payment is at the end of the month, then the argument will be zero (0). By default, the argument will be zero.

How to create a mortgage calculator with microsoft excel

You are free to use this image on your website, templates etc, Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For eg:
Source: Mortgage Calculator in Excel (wallstreetmojo.com)

Examples

Mr. A wants to buy a car, and the cost of the car is Rs 600,000. He approached the bank, and the bank agreed to sanction the loan based on the below conditions.

  • Down Payment: Rs 150,000
  • Loan Tenure: 3 years and above
  • Interest Rate: 15% PA.

Now Mr. A wants to evaluate his monthly savings and decide on the loan taking possibilities. In excel, using the PMT function, we can calculate the EMI.

    Enter all this information in Excel.

How to create a mortgage calculator with microsoft excel

Open PMT function in the B7 cell.

How to create a mortgage calculator with microsoft excel

The first thing is the rate, so interest rate select B6 cell. Since the interest rate is per annum, we need to convert it to month by dividing the same by 12.

How to create a mortgage calculator with microsoft excel

How to create a mortgage calculator with microsoft excel

PV is nothing but the loan amount Mr. A taking from the bank. Since this amount is a credit given by the bank, we need to mention this amount is negative.

How to create a mortgage calculator with microsoft excel

Close the bracket and hit enter. We have an EMI payment per month.

How to create a mortgage calculator with microsoft excel

So, in order to clear the loan of Rs 450,000 in 3 years at an interest rate of 15%, Mr. A has to pay Rs15,599 per month.

Things to Remember

  • You need to fill the only orange colored cell.
  • The loan amortization tableLoan Amortization TableLoan amortization schedule refers to the schedule of repayment of the loan. Every installment comprises of principal amount and interest component till the end of the loan term or up to which full amount of loan is paid off.read more will show the breakup of the Principal Amount and Interest Amount each month.
  • It will also show you the extra amount you are paying as a percentage.

Recommended Articles

This has been a guide to Mortgage Calculator in Excel. Here we discuss how to prepare a mortgage loan payment calculator using PMT Formula along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

How to create a mortgage calculator with microsoft excel

If you are planning to purchase a home to live in or a building to start a business, it is important for you to know how much you can afford to pay every month in mortgage payments. A monthly mortgage payment basically consists of two parts, an amount that goes towards the principal of the loan and a second amount that goes towards the interest agreed between the two parties. The second part is actually the cost of borrowing money. You can easily calculate monthly mortgage payments using the Excel software from the Microsoft Office suite.

Others are Reading

  • How to Print Business Cards In Microsoft Word
  • How To Create Boxes In Word

Instructions

    font-size: 13px !important;color: #474747;text-align: justify;line-height: 21px;” >

Gather potential loan information

Gather information about your existing or potential loan. This information includes the interest rate, loan terms and the loan amount. If it is a hypothetical loan, you would need to estimate the interest rate.

Launch MS Excel

Launch Microsoft Excel on your computer and open up a blank workbook.

Define Mortgage rate or interest rate

Click cell “A3” in the upper left corner of your workbook and enter the interest rate or the rate of your mortgage as a percentage. For example, if you are looking at a four and a half percent loan, type “4.50” in the cell and press the “Enter” key on the keyboard.

Define your Mortgage period or loan length

Scroll right to the cell B3 and enter the amortization period of your mortgage, also known as loan term, as a number of years. 15 or 30 years are usually used as standard loan terms, but you can also use 20 or 25 years as your loan term. If you want to calculate mortgage payment for a typical 30-year loan term, type 30 in B3 and hit Enter.

Define loan amount

Again scroll right to cell C3 and enter the loan amount in it as a negative number. For example, if you want to look at the cost of a $450000 mortgage, enter “-450000” in the cell and press “Enter”.

Calculate the payment

Navigate to cell D3 and copy this formula =PMT(A3/12,B3*12,C3) and hit “Enter” on the key board. As soon as you press the Enter key, a payment amount will show up in the cell in parenthesis and red. The amount is in red colour because it is showing the amount of money owed. For a realistic estimate of the mortgage payment you will have to pay ever month, add property tax, insurance and other necessary fees to this amount.

For most of modern people, to calculate monthly mortgage payment has become a common job. In this article, I introduce the trick to calculating monthly mortgage payment in Excel for you.

Calculate monthly mortgage payment with formula

To calculate monthly mortgage payment, you need to list some information and data as below screenshot shown:

Then in the cell next to Payment per month ($), B5 for instance, enter this formula =PMT(B2/B4,B5,B1,0) , press Enter key, the monthly mortgage payments has been displayed. See screenshot:

Tip:

1. In the formula, B2 is the annual interest rate, B4 is the number of payments per year, B5 is the total payments months, B1 is the loan amount, and you can change them as you need.

2. If you want to calculate the total loancost, you can use this formula =B6*B5 , B6 is the payment per month, B5 is the total number of payments months, you can change as you need. See screenshot:

In Excel, you can easily create a mortgage calculator with the PMT function

Explanation of the PMT function

PMT calculates the PayMenT for a loan for a constant interest rate.

The arguments of the function are:

  • Rate The interest rate on the loan.
  • Nper The total number of payment periods for the loan.
  • Pv The Present Value, the value of the mortgage or loan.
  • Fv The future value. It’s optional and the value = 0 most of the time.
  • Type Whether the payment calculation is done at the beginning of the period (1) or at the end (0)

Example to calculate the mortgage payments

The amount of your mortgage is 50,000$ for a period of 15 years and the interest rate is 4%.

=PMT(4%;15;50000) => -4 497,06 €

How to create a mortgage calculator with microsoft excel

This result is the annual refund (1 payment for the whole year). But usually you refund a mortgage monthly and the value for the arguments of the function is different.

Mortgage calculator by month

To return the value to refund by month, you must convert the value of the interest rate and the period in month

Convert the period in month

This conversion is very easy to do. You just have to multiply the number of years by 12.

=Number of years*12

Convert the annual interest rate in month

This is the hardest part of the calculation. In fact there is 2 situations

  • The interests are calculated at the end of the period
  • The interests are running over the period

If the interest are calculated at the end of the period, the conversion is really easy

=annual interest rate/12

If the interests are calculated over the period (common situation) the formula is more complex.

=(1+annual interest rate)^(1/12)-1

Result for the monthly value

By replacing the previous formulas in the arguments of the function

When creating a mortgage calculator, it is important to consider everything that would make the mortgage profitable for your company. We are going to create a mortgage calculator together in a step-by-step format. We will not just create it, but also calculate it.

Data preparation

Prepare the components of the calculator.

How to create a mortgage calculator with microsoft excel

Display the data you already have. How much is being loaned (loan amount) (1), the interest rate per year (interest rate/year) (2), and the length of time it will take to repay the loan (loan length) (3). How many times will the client pay per year (4).

You could right-click on empty cells before stating the values, choose Format Cells, choose Currency, and Percent if you have any issues creating the mortgage calculator.

Preparing the mortgage formula

Click on the empty cell beside the total number of payments (1), and type in =b6*b7 (2), and then press enter.

Click on the empty cell beside payment/period (1), and type in =PMT(interest rate/year, total number of payment, -loan amount) (2).

If you do not add a minus in front of the loan amount, you will get negative numbers as a result.

Click on the empty cell beside the total cost of the loan (1), and multiply payment/period with the total number of payments (=b9*b8) (2).

Click on the empty cell beside interest cost, and subtract the total cost of the loan from the loan amount (=b10-b4).

In conclusion, we now know how much profit we will make if we borrow this amount of money from the client and how much the client would pay every month.

How to create a mortgage calculator with microsoft excel

Mortgage calculator is an automated tool that helps the users to determine the financial suggestions of change in different variables in a mortgage financial schedule. To use the Excel Mortgage Calculator, you simply need to enter your mortgage details into the user input fields. The input fields would ask for your loan amount, the interest rate (%), number of years over which your loan has been withdrawn and the number of payments per year.

How to create a mortgage calculator with microsoft excel

How To Make A Mortgage Calculator With Excel

Once you complete the above process, the summary table at the right-top of the spreadsheet will directly display the summary of your mortgage payment details. Along with this, the amortization arrangement will be displayed in the bottom line of the spreadsheet.

Secondly, if you want the spreadsheet to display the dates of your schedule payments, you need to enter the initial loan start date into the column box of the user-input fields. Once the date has been entered, on the top left of the spreadsheet a summary box will be displayed, where you can enter the final payment. Moreover, each of the schedule payment dates will be displayed in spreadsheet’s bottom half.

Do you know the functions used in the Excel mortgage calculator?

PMT function

The Excel PMT function has been used to calculate the monthly payments on a loan ($50,000), the total amount is supposed to be paid after 5 years. The interest is charged at a 5% rate per year. Finally, the payment of the loan will be made by the end of each month. Thus, to calculate the PMT function you need to use the formula, =pmt (5%/12,60,50000), calculating the formula, you will get the result as -943.56.

PPMT function

The PPMT function majorly calculates the Interest part of your regular scheduled payment. For example, the Excel PMT function will help you to calculate your payment on the principal amount in months 1 and 2 on the loan of ($50,000). The entire amount needs to be paid off after 5 years. Your interest will be charged at the rate of 5% per year and your payment over the loan requires being made by the end of each month. To understand the entire process, you need to use the formula, month 1: =ppmt(5%/12,1,6,50000) and for month 2: =PPMT(5%/12,2,60,50000)

Calculating the formula, you will get a sum total of -$735.23 for month 1 and -$738.29 for month 2.

IPMT Function

The IPMT function would help you to calculate the interest part of your regular scheduled payment in month 1 and month 2. In case your principle loan amount is $50,000, you need to pay off the entire amount by the end of 5 years. Just like the previous process, the charge rate of the interest would be 5% per year. To get the interest amount, you need to use the formula:

Month 1: =IPMT (5%/12, 1, 60, 50000) which comes to -$208.33

Month 2: =IPMT (5%/12, 2, 60, 50000) which come to -$205.27

Calculating a mortgage payment in Microsoft Excel is really easy to do. This article will walk thru the steps needed to set up the calculation of the monthly loan payment. In the end, you will learn how to calculate a mortgage payment with Excel.

Excel has a number of financial functions built in. Namely, a function called PMT() that used to calculate the payment for a loan for a fixed amount of time with a constant or fixed interest rate.

Just a Few Steps

In the next few steps we’ll set up the basic template to calculate a mortgage payment with Excel.

Start Microsoft Excel and get to a blank workbook to begin. This example will assume that you started in the very upper left corner of the workbook in cell A1.

Start Microsoft Excel

This can be any desktop version or the online version.

Start Microsoft Excel and get to a blank workbook to begin. This example will assume that you started in the very upper left corner of the workbook in cell A1.

Starting in cell A1, enter the text into the first set of cells. Adjust the width of column A so that the labels fit.

Set up Your Fields

We’ll use these few fields to run the mortgage payment calculator. Use the labels below.

Loan Amount
Interest Rate
Loan Duration
Loan Payment

Starting in cell A1, enter the text into the first set of cells. Adjust the width of column A so that the labels fit.

You can choose the different value types for each of the cells by clicking in the cell, and then clicking the button in the ribbon that matches the type.

For currency, look for the “$” button. For percentage, look for the “%” button.

To increase the number of decimals, look for the button with the “.00” and an arrow pointing to the LEFT.

Set the Formatting for Each Cell

For cell B1, choose currency.

For cell B2, choose percentage. AND increase the number of decimals showing.

For cell B3, there’s no need to change anything.

For cell B4, choose currency.

You can choose the different value types for each of the cells by clicking in the cell, and then clicking the button in the ribbon that matches the type.

For currency, look for the “$” button. For percentage, look for the “%” button.

To increase the number of decimals, look for the button with the “.00” and an arrow pointing to the LEFT.

The colors you see in the image above will appear as you type in the formula.

You’ll see #NUM! in the cell at first after you press Enter. It’s OK. That will all change in the final step.

Enter the Formula to Calculate a Mortgage Payment

The formula here needs to go into cell B4, right next to the Loan Payment label you entered.

The colors you see in the image above will appear as you type in the formula.

You’ll see #NUM! in the cell at first after you press Enter. It’s OK. That will all change in the final step.

In this example, the monthly principal plus interest payment for a $450,000 mortgage at 4.5% over 30 years is $2,280.

Add Your Info

The Loan Amount is the amount you would finance. Or to make it simple let’s just say it’s the cost of the home.

The Interest Rate is the annual interest rate. The formula you entered above divides it by 12 to get to a monthly cost.

The Loan Duration is the number of years for the loan. The formula you entered above multiplies the number by 12 to get to the number of months for the loan.

In this example, the monthly principal plus interest payment for a $450,000 mortgage at 4.5% over 30 years is $2,280.

After you have gone thru the above you have the basic mortgage payment calculation for a fixed interest rate loan.

Other Considerations for How to Calculate a Mortgage Payment in Excel

It Really Needs to be PITI

So far, the calculator you have set up is calculating the monthly principal plus interest payment. This is the “PI” of “PITI“. Most mortgage payments consist of the principal and interest, plus property taxes and homeowner’s insurance. These last two items are the “TI” of “PITI”: taxes and insurance.

Most often, the principal and interest payments are the majority of the overall monthly payment. But say you’re getting a smaller loan on a home that has a high value. In that case you may find that your property taxes and homeowner’s insurance are a much larger part of your mortgage payment.

Look Out for PMI

Another acronym! PMI stands for Private Mortgage Insurance. Basically, PMI comes into the picture if the amount of your loan is going to be more than 80% of the value of the home you are buying. From the example above if the home’s value was $450,000, any loan for more than $360,000 would require PMI. The PMI component is in addition to any PITI part of your mortgage payment. If you start out with a mortgage that includes PMI in the payment, and the home you bought appreciates in value over time enough you may be able to remove the PMI part of your mortgage payment.

Part II

In an upcoming post we’ll extend how to calculate a mortgage payment with Excel to factor in your guestimate of the annual property taxes as well as homeowner’s insurance. We’ll add in PMI too.

Managing personal finances can be a challenge, especially when trying to plan your payments and savings. Excel formulas and budgeting templates can help you calculate the future value of your debts and investments, making it easier to figure out how long it will take for you to reach your goals. Use the following functions:

PMT calculates the payment for a loan based on constant payments and a constant interest rate.

NPER calculates the number of payment periods for an investment based on regular, constant payments and a constant interest rate.

PV returns the present value of an investment. The present value is the total amount that a series of future payments is worth now.

FV returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Figure out the monthly payments to pay off a credit card debt

Assume that the balance due is $5,400 at a 17% annual interest rate. Nothing else will be purchased on the card while the debt is being paid off.

Using the function PMT(rate,NPER,PV)

the result is a monthly payment of $266.99 to pay the debt off in two years.

The rate argument is the interest rate per period for the loan. For example, in this formula the 17% annual interest rate is divided by 12, the number of months in a year.

The NPER argument of 2*12 is the total number of payment periods for the loan.

The PV or present value argument is 5400.

Figure out monthly mortgage payments

Imagine a $180,000 home at 5% interest, with a 30-year mortgage.

Using the function PMT(rate,NPER,PV)

the result is a monthly payment (not including insurance and taxes) of $966.28.

The rate argument is 5% divided by the 12 months in a year.

The NPER argument is 30*12 for a 30 year mortgage with 12 monthly payments made each year.

The PV argument is 180000 (the present value of the loan).

Find out how to save each month for a dream vacation

You’d like to save for a vacation three years from now that will cost $8,500. The annual interest rate for saving is 1.5%.

Using the function PMT(rate,NPER,PV,FV)

to save $8,500 in three years would require a savings of $230.99 each month for three years.

The rate argument is 1.5% divided by 12, the number of months in a year.

The NPER argument is 3*12 for twelve monthly payments over three years.

The PV (present value) is 0 because the account is starting from zero.

The FV (future value) that you want to save is $8,500.

Now imagine that you are saving for an $8,500 vacation over three years, and wonder how much you would need to deposit in your account to keep monthly savings at $175.00 per month. The PV function will calculate how much of a starting deposit will yield a future value.

Using the function PV(rate,NPER,PMT,FV)

an initial deposit of $1,969.62 would be required in order to be able to pay $175.00 per month and end up with $8500 in three years.

The rate argument is 1.5%/12.

The NPER argument is 3*12 (or twelve monthly payments for three years).

The PMT is -175 (you would pay $175 per month).

The FV (future value) is 8500.

Find out how long it will take to pay off a personal loan

Imagine that you have a $2,500 personal loan, and have agreed to pay $150 a month at 3% annual interest.

Using the function NPER(rate,PMT,PV)

it would take 17 months and some days to pay off the loan.

The rate argument is 3%/12 monthly payments per year.

The PMT argument is -150.

The PV (present value) argument is 2500.

Figure out a down payment

Say that you’d like to buy a $19,000 car at a 2.9% interest rate over three years. You want to keep the monthly payments at $350 a month, so you need to figure out your down payment. In this formula the result of the PV function is the loan amount, which is then subtracted from the purchase price to get the down payment.

Using the function PV(rate,NPER,PMT)

the down payment required would be $6,946.48

The $19,000 purchase price is listed first in the formula. The result of the PV function will be subtracted from the purchase price.

The rate argument is 2.9% divided by 12.

The NPER argument is 3*12 (or twelve monthly payments over three years).

The PMT is -350 (you would pay $350 per month).

See how much your savings will add up to over time

Starting with $500 in your account, how much will you have in 10 months if you deposit $200 a month at 1.5% interest?

Using the function FV(rate,NPER,PMT,PV)

in 10 months you would have $2,517.57 in savings.

Graphic skills

A mortgage calculator is commonly used to estimate monthly mortgage payments. It would be time-consuming to create a mortgage calculator from scratch. For convenience, WPS Office provides you with professional and practical mortgage calculator templates. Without further ado, let ’ s get started!

· Steps to create a mortgage calculator with templates in WPS Office.

1. Go to the homepage of WPS Office, and click Apps > Templates .

2. Input “Mortgage Calculator” in the search box on the top and press the Enter key.

3. Choose the desired template from the searching results, and click Use Now to apply it.

4. Then you can edit the template as needed and save it as a normal document.

How to create a mortgage calculator with microsoft excel

· Recommended mortgage templates provided by WPS Office.

Tips: You can click the following pictures to use these templates quickly.

How to create a mortgage calculator with microsoft excel Home Mortgage Calculator

How to create a mortgage calculator with microsoft excel Extra Payment Mortgage Calculator

How to create a mortgage calculator with microsoft excel Mortgage Payoff Calculator

How to create a mortgage calculator with microsoft excel Fresh Mortgage Calculator Chart

The templates provided by WPS Office go far beyond mortgage calculator charts, and you are welcome to visit WPS Templates to use more elegant and professional templates. Click the following links to view more templates quickly:

· What is WPS Templates?

WPS Templates offers you its massive number of free templates to work with. It not only simplifies your document creation but also saves time and money. Moreover, it gives you consistency and clarity. It ensures that your document has a standard layout, look and feel.

Check out these tips and tricks to master WPS Office. Whether you need to create a report, write a letter, update your resume, create a cover page or add a cross reference, free WPS Office is ready and waiting. Learn how to take your Word, PPT, Excel and PDF from plain to exceptional. Find out the tips and tricks you need to know so that you can use Writer, Presentation, Spreadsheet and PDF like a pro!

283 results for mortgage calculator excel spreadsheet

  • All Tutorials
  • Video Tutorials
  • Quick Tutorials
  • All
  • Spreadsheet
  • Presentation
  • Writer
  • PDF

How to create mortgage calculator in WPS Office Excel

A mortgage calculator is commonly used to estimate monthly mortgage payments. It would be time-consuming to create a mortgage calculator from scratch. For convenience, WPS Office provides you with professional and practical mortgage calculator templates. Without further ado, let’s get started! · Steps to create a mortgage calculator with templates in WPS Office.1. Go to the homepage of WPS Office, and click Apps > Templates.2. Input “Mortgage Calculator” in the search box on the top an.

A quick way to calculate sum, average, count, max, and min

A quick way to calculate sum, average, count, max, and min

Here we have received a sheet on the Masks Sales in Single-Day Sheet from a supermarket. How can we quickly calculate the sum, average, maximum, and daily sales volume during the period from August 19th to 31st?We can use the AutoSum feature of the WPS Spreadsheet, rather than calculating all data one by one with a calculator, which would be a waste of time. Follow these step.1. Select the data area, click the AutoSum drop-down button in the Formulas tab.2. Find the Sum button and clic.

How to unlock excel sheet in WPS Spreadsheet

· Option 1If we have set up password protection:1. Use WPS Office to open the spreadsheet.2. Click the Review tab.3. Click the Unprotect Sheet button.4. In the pop-up Unprotect Sheet dialog box, enter the password you set before, and then click OK. · Option 2 If there is no password protection:1. Use WPS Office to open the spreadsheet.2. Click the Review tab.3. Click the Unprotect Sheet button. Then we can unlock excel sheet in WPS Spreadsheet. To be office excel advanced, you could le.

More features in picture inserting

More features in picture inserting

WPS Spreadsheet could be an alternative to Microsoft Office Excel. It includes 100’s of built-in formulas, pivot tables, and more. WPS Spreadsheet and Microsoft Excel both have the function of inserting pictures. Compared with Microsoft Excel, WPS spreadsheet has an added advantage-Embed to cell. In Microsoft Excel, we usually need to manually adjust the size of the pictures after inserting them. If the position of the table changes, the inserted pictures will not fit in the new ce.

More features in Picture to Text

More features in Picture to Text

Both WPS Spreadsheets and Microsoft Excel have the function of Converting pictures to text. What are the differences between them? Microsoft Excel itself does not support the OCR function. If you want to convert Picture to Text, you need to open another application, OneNote. 1. Copy the picture and paste it into OneNote.2. Click Copy Text from Picture with the right mouse button3. Paste the text into Excel as needed. In WPS Spreadsheet, the OCR function allows us to convert pictures to.

How to view two sheets side by side

How to view two sheets side by side

When checking data in WPS Spreadsheet, we frequently need to check and edit multiple sheets at the same time. We’ll learn how to see two pages on the same screen today.· Step one: Create and view two sheets side by sideFirst, let’s open an Excel file containing multiple worksheets. Click New window in the View tab. At this time, another identical Excel document window will appear. These two windows are presenting the same file. No matter which one is edited, the changes can be.

How to create a Form in spreadsheet

How to create a Form in spreadsheet

WPS Spreadsheet could be an alternative to Microsoft Office Excel. It includes 100’s of built-in formulas, pivot tables, and more. Today, we will learn how to create a Form in WPS spreadsheet, which allows us to input, search and modify the data. Note: If the table contains merged cells, the Form function will be unavailable. · 1. Data inputFirst, click the Data tab and click the Form button. In the pop-up window, the system will automatically take the title row as the field name. .

How to create a table with one click

How to create a table with one click

Did you know that, when you are using WPS Spreadsheet, you can directly create a table in the original datasheet? The generated table comes with functions such as sorting, filtering, and setting styles, which can save us a lot of time for subsequent operations. For all, easy our work with WPS Spreadsheet like Microsoft Excel. Here are the steps to create a table. Take the sales table in this article as an example. 1. Click the Table button in the Insert tab. (Notes: You can also use th.

Providing an online calculator on your website makes people come back. Design your online calculation in Microsoft Excel, then convert it into a calculating and interactive web page that can be published on yourВ website.

Help your visitors do a better job, and they will return to your site for more. At last you can create online calculators for your website without the help of a programmer. All you need is Microsoft Excel.

  • Price calculation: Online pricing calculators help your customers get the total price including options, maintenance etc.
  • Financial calculation: provide online mortgage calculators, interest calculation, loan calculators or online lease calculation.
  • Calculators for governments and counties: online income tax calculation, online social benefits, online payroll calculators.
  • ROI: OnlineВ return on investment calculation to better show the benefits of your product, or calculate Internal Rate of Return (IRR).
  • Technical and engineering calculators: online dimensioning tools, online HVAC calculations, load calculators, concrete calculation, torque calculation, area calculators, volume calculation, electrical calculation, power calculation.
  • Health calculators: online body mass index calculator, online calorie calculation, online body fat calculator.

You can use any feature of Excel on the web, including formulas and functions, to validate data entry, select intelligent default values or show customized images or maps.

How to create a mortgage calculator with microsoft excel

Online calculators boost sales

Online calculators turn passive browsers into active visitors. If you make them start using your price calculator for a quote, or to check how much they can save by changing a mortgage, research shows a drastic increase in sales.

Online calculation is valuable also on websites that don’t sell anything. If you can supply anВ online calculator that solves problems that your visitors have, they will come back to use your calculator, and they will tell friends and colleagues about your site and theВ online servicesВ it offers.

Online spreadsheetsВ make your web professional

A time-saving calculator makes your web site stand out from the competition. It’s so easy to put any existing form or calculator on the web: expense reports, surveys, quote generators, order forms, reservation forms, employment applications, financial calculators, engineering tools – the list is endless.

We’re not only talking simple plus-and-minusВ spreadsheets here.

  • Create mobile-friendly, responsive forms that automatically adapt to different screen widths, portrait/landscape orientation etc.
  • To make it easier to understand a large form, split it into two or more separate steps and let users navigate between the sections using tabs, next/previous buttons or sliding panels.
  • Use graphical widgets like menus and calendars to make the form easier to use.
  • Hide rows or complete sections of a form until you know they are applicable.
  • Allow file attachments with submitted web forms.
  • You can have mandatory responses.
  • Use hidden fields to discretely include supplemental information in the form.
  • With Real-time Sync, two or more persons can share a web form and collaborate in real-time.
  • Fetch live data from corporate databases when run on ASP.Net or Node.js servers.
  • Build forms-based web apps for iPhone and Android devices that can submit smart forms directlyВ – even when the phone is offline.
  • Use live, interactive charts that invite users to point-and-click, and the charts automatically update when values are changed.

The free form-to-Inbox service requires no installation, no programming skills and no configuration. Once you upload your first web form to your server, submitted forms start arriving at your Inbox. You are up and running within minutes. For a small monthly fee, we can store submitted forms in a cloud database for you until you visit our server to pick them up.

How to create a mortgage calculator with microsoft excel

Home Equity calculator with live interactive chart

This is one of our favorite examples because it shows the advantage of live charts. Change any cell in the spreadsheet and the chart immediately reflects the new values.

In this example, you can also see a useful implementation of a slider. To change the years remaining on a loan, we could just have used a standard numeric field. Using a slider allows for much easier visualization of how the equity varies over the lifetime of the loan.

Features and benefits

SpreadsheetConverter is the perfect tool for creating calculating web forms and web pages that attract attention.

This example teaches you how to create a loan amortization schedule in Excel.

1. We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000. We use named ranges for the input cells.

How to create a mortgage calculator with microsoft excel

2. Use the PPMT function to calculate the principal part of the payment. The second argument specifies the payment number.

How to create a mortgage calculator with microsoft excel

3. Use the IPMT function to calculate the interest part of the payment. The second argument specifies the payment number.

How to create a mortgage calculator with microsoft excel

4. Update the balance.

How to create a mortgage calculator with microsoft excel

5. Select the range A7:E7 (first payment) and drag it down one row. Change the balance formula.

How to create a mortgage calculator with microsoft excel

6. Select the range A8:E8 (second payment) and drag it down to row 30.

How to create a mortgage calculator with microsoft excel

It takes 24 months to pay off this loan. See how the principal part increases and the interest part decreases with each payment.

How to create a mortgage calculator with microsoft excel

Related Articles

  • How to Pay Off a 30-Year Mortgage in 15 Years
  • How to Work Out Real Estate Salesperson Commissions
  • Can We Get a Mortgage if We Are Both Working Part Time?
  • How Do I Create a Loan Amortization Table in Excel?
  • How do I Calculate a Mortgage Payment Using Excel?

When you take out a fixed-rate mortgage to buy or refinance a home, your lender takes three numbers and plugs them into a formula to calculate your monthly payment. Those three numbers are your principal, or the amount of money you’re borrowing; your interest rate; and the number of months in your loan term. You can quickly create a spreadsheet in Microsoft Excel to perform the calculation for you–and, in the process, gain a greater understanding of just how a mortgage loan works.

Launch Microsoft Excel. Open a new workbook by pressing “Ctrl” and “N.”

Type “Principal” into cell A1 on the Excel worksheet. Type “Rate” into cell A2. Type “Months” into cell A3.

Enter the amount of the mortgage principal in cell B1.

Enter the interest rate in cell B2. Just enter the number; don’t use the percent sign. So, if your rate is 7 percent, just enter 7. If it’s 5.75 percent, enter 5.75.

Enter the number of months in the loan term in cell B3. Most mortgages are for either 15 or 30 years. Enter 180 for a 15-year mortgage or 360 for a 30-year loan. If your loan is for some other number of years, simply multiply that number by 12 and enter the result in cell B3.

Enter the following formula in cell A4, beginning with the “equals” sign:

This converts your annual interest rate to a decimal figure by dividing it by 100, then breaks it down into a monthly rate by dividing it by 12.

Enter the following formula in cell A5, beginning with the “equals” sign:

This step takes into account the compounding of the interest over the life of the loan.

Enter the following formula in cell A6, beginning with the “equals” sign:

This takes all the data and boils it down to a multiplier that’s applied to your principal to determine your monthly payment.

Enter the following formula in cell A7, beginning with the “equals” sign:

This applies the multiplier to your loan principal.

Right-click on cell A7 and select “Format Cells.” Set the formatting to “Currency.” Set “Decimal Places” to 2. Set the “Currency Symbol” to the dollar sign. Click “OK.” This cell now gives you the amount of your mortgage payment based on your principal, interest rate and loan term.

Experiment with different principal amounts, interest rates and loan terms just by changing the values in cells B1, B2 and B3. The total payment in cell A7 will change to reflect the new figures.

Warning

Most lenders require that you pay your property taxes and homeowners’ insurance premiums on a monthly basis, with 1/12 of the total tacked on to each mortgage payment. Those amounts are not included in this calculation. The result in cell A7 includes only the amount that goes to your lender–the total principal and interest due each month.

  • Amortization Formula
  • Bank Rate: Mortgage Amortization Calculator

Cam Merritt is a writer and editor specializing in business, personal finance and home design. He has contributed to USA Today, The Des Moines Register and Better Homes and Gardens”publications. Merritt has a journalism degree from Drake University and is pursuing an MBA from the University of Iowa.

Want to Calculate Loan Payments Offline?

We have offered a downloadable Windows application for calculating mortgages for many years, but we have recently had a number of people request an Excel spreadsheet which shows loan amortization tables.

Our Simple Excel loan calculator spreadsheet offers the following features:

  • works offline
  • easily savable
  • allows extra payments to be added monthly
  • shows total interest paid & a month-by-month amortization schedule

Microsoft Excel Loan Calculator Spreadsheet Usage Instructions

The calculator updates results automatically when you change any input.

loan amount – the amount borrowed, or the value of the home after your down payment.

interest rate – the loan’s stated APR. For your convenience, we publish local Los Angeles mortgage rates below to help you see currently available rates.

loan term in years – most fixed-rate home loans across the United States are scheduled to amortize over 30 years. Other common domestic loan periods include 10, 15 & 20 years. Some foreign countries like Canada or the United Kingdom have loans which amortize over 25, 35 or even 40 years.

payments per year – defaults to 12 to calculate the monthly loan payment which amortizes over the specified period of years. If you would like to pay twice monthly enter 24, or if you would like to pay biweekly enter 26.

loan start date – the date which loan repayments began, typically a month to the day after the loan was originated.

optional extra payment – if you want to add an extra amount to each monthly payment then add that amount here & your loan will amortize quicker. If you add an extra payment the calculator will show how many payments you saved off the original loan term and how many years that saved.

Enable Your Worksheet to Calculate Your Loan Payments

How to Enable Your Mortgage Calculation Spreadsheet

When you download Excel spreadsheets from the web they download in PROTECTED VIEW.

You need to click on [Enable Editing] in the yellow banner at the top of the spreadsheet to change variable amounts.

How to create a mortgage calculator with microsoft excel

Right click on any of the above images to save a copy of our Excel loan calculator spreadsheet, or click on them to directly open it.

By default this calculator is selected for monthly payments and a 10-year loan term.

Some of Our Software Innovation Awards!

Since its founding in 2007, our website has been recognized by 10,000’s of other websites. A few of our software innovation awards are listed below:

Current Mortgage Rates

The following table shows locally available mortgage rates which you can use to help calculate your monthly home loan payments.

Homeowners May Want to Refinance While Rates Are Low

The Federal Reserve has hinted they are likely to taper their bond buying program later this year. Lock in today’s low rates and save on your loan.

Are you paying too much for your mortgage?

Find Out What You Qualify For

Check your refinance options with a trusted local lender.

Answer a few questions below and connect with a lender who can help you refinance and save today!

Saturday, 23 April 2016

How to build a Mortgage Amortization Table in EXCEL

A mortgage amortization table splits monthly mortgage repayments into two elements, the principal paid and also the interest paid. Amortization details the process of separating mortgage repayments over the duration of the mortgage loan between interest paid and principal payment.

Home loans are usually front-loaded with interest. What this means is at the start of the mortgage you are paying out much more in interest than you are repaying on the original balance. This operates to your benefit at the end of the mortgage loan since the interest is computed on the outstanding balance. The smaller the outstanding balance, the much less you’ll pay out in interest.

The mortgage amortization table provides you with a comprehensive picture of the home loan over its whole term. So you can see exactly how payments are applied, what remains when it comes to principle and interest payments as well as the bottom line on the amount of money you’re paying to borrow money for your house. Needless to say, a mortgage amortization calculator will certainly cause you to think about shorter term mortgages with reduced rates of interest. Understanding how much money you’re ultimately spending to have reduced repayments every month will actually help you think about the smarter long-term alternatives.

Typically with each monthly payment, you only pay back part of the amount of money initially borrowed plus interest on the decreasing balance of the principal. How much your monthly payments are is dependent, in part, on the principal, the rate of interest and the amount of time permitted for repayment. Amortization term is the amount of time necessary to amortize the home loan. The amortization period is the amount of time, generally, twenty-five years which it takes to totally repay a mortgage loan by means of repayment of the initial debt or principal and also the accrued interest.

To create a mortgage amortization table for your home loan use one of the many online amortization schedule calculators available. Amortization schedules can be calculated immediately online at one of these sites. One such site is Yahoo and see also Karl’s Mortgage Calculator.

Another option for creating a mortgage amortization table is Microsoft Excel. If you have the program installed on you computer you can download an amortization template from the Microsoft site here.

A mortgage amortization table using Excel will give you a good idea of the monthly payments on a loan or mortgage and how much in interest you will pay over the term of the mortgage. Setting up this in Excel or any other spreadsheet application is quiet easy.

Now you can setup the formulas yourself or you can do what I do and download a simple template for Excel. With a template, it is just a matter of filling in the required information and you get an instant amortization table. The information you need to hand includes the loan amount, the interest rate and the term of the loan in months.

Another option for creating a mortgage amortization table is Microsoft Excel. If you have the program installed on you computer you can download an amortization template from the Microsoft site here.

You need to keep in mind that a loan amortization table will only be accurate up to a point. You will need to get a detailed amortization table from your lenders like personalloansny or a major bank.

It is also advisable to get a quotation from a number of banks before taking up any loan offer.

If you are about to buy your next new home or are thinking of remortgaging Excel provides a great way to work out what your monthly repayments will be. Using the PMT function we can work out what your repayments will be – all you need to do is enter your mortgage amount, term of loan and borrowing interest rate – Excel does the rest for you.

Start by entering the data as shown below. You can enter you own mortgage amount, loan term and interest rate. Note that the loan amount has been entered as a negative figure. It will work if you don’t enter a negative but the resulting repayment figure will be negative if you don’t. The term or length of the loan has been entered in months to get a monthly repayment figure: 300 months is 25 years.

How to create a mortgage calculator with microsoft excel

In cell B7 type =PMT and then use the shortcut keyboard combination CTRL A to open the Functions Arguments dialog.

In the Rate argument type B5/12 (you are dividing the annual interest rate by 12 to the month equivalent)

The Nper argument needs to know the number of payments – the answer to that is in B4

Fv (future value) is omitted and can stay that way if you plan to pay the mortgage off by the end of the loan term

Type is omitted as we are making our repayments at the end of each month. If you plan to make payments at the beginning of the month enter 1 in this argument. This makes very little difference to the formula result.

Click OK to confirm.

How to create a mortgage calculator with microsoft excel

Flexible Mortgage Calculator

If you have a flexible mortgage you can make huge savings by making regular overpayments or making lump sum overpayments. Our flexible mortgage calculator allows you to calculate the savings you can make. Download it now for free – it’s an Excel spreadsheet.
How to create a mortgage calculator with microsoft excel

Watch the video below to see how to use the Excel PMT function…

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

A loan payment calculator is a must-have tool if you’re planning on taking out a loan.

It’s a good way to determine how the loan amount, its interest, and the loan term affect the total amount you’ll be paying.

If you want to make your own, the ‘PMT’ function in Excel , coupled with other functions, can be used to create a loan payment calculator.

But if you like the easier and quicker way, you can download and use existing loan calculator templates online.

Today, we’ll be showing you how to use Microsoft’s free simple loan calculator to create an amortization table and payment schedule for any type of loan.

*This tutorial was written in Microsoft Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Table of Content

Microsoft’s free loan calculator template

How to create a mortgage calculator with microsoft excel

Not only is this a free loan calculator, but it also lists out your payment schedule on it. You can see details like balance , principal , interest , and ending balance .

To download this template, simply head over to this link and click the ‘Download’ button .

Once you got the file, open it up and you’ll see the calculator.

How to use the loan calculator

The first thing to do after you downloaded the file is to enable editing.

You can’t input your own figures without doing this.

Click ‘Enable Editing’ at the top just below the Ribbon.

How to create a mortgage calculator with microsoft excel

Now that you can edit the file, you have to know first the appropriate cells you should edit.

In this calculator template, there are lots of information you can see:

How to create a mortgage calculator with microsoft excel

  • Loan amount – the amount you’ll be borrowing
  • Annual interest rate – the interest rate of the loan annually
  • Loan period in years – the term of the loan in years
  • Start date of loan – the day when the payments for the loan will begin
  • Monthly payment – the total amount you’ll be paying monthly for the duration of the loan
  • Number of payments – total number of payments/installments
  • Total interest – the total interest you’ll be paying
  • Total cost of loan – the total amount you’ll be paying

In addition to that, there’s an amortization table and chart showing you the principal paid, interest paid, and the loan balance.

Using the calculator is really easy. Once you change a value, the result gets automatically updated.

However, you should only change the loan details:

  • Loan amount
  • Annual interest rate
  • Loan period in years
  • Start date of loan

Here’s what would happen if you change a single detail:

How to create a mortgage calculator with microsoft excel

The ‘Number of payments’ is automatically calculated (multiplied by 12 ) given that you would have to pay monthly for the duration of the loan.

But if your loan’s payment period isn’t monthly ( weekly , quarterly , semi-quarterly ), editing this calculator would prove to be a bit of a hassle.

As most loan calculator templates, this one is currently set up to calculate and show the payments you’ll be doing monthly.

If you really need an Excel loan calculator where you can adjust the payment periods, you can actually create your own advanced PMT calculator in under a minute . Just download the file on that guide and copy-paste the formulas from the article to complete the calculator.

by Adam | Aug 21, 2015 | Blog

How to create a mortgage calculator with microsoft excel

Many businesses use custom calculators externally and internally in finance, real estate, insurance, return investment, energy industry and several others. What if a bank could save you hundreds of dollars every year by choosing another mortgage plan? You would certainly listen to what they have to say. Online calculators provide quick results and are a great source for proving valuable information to customers.

It makes perfect sense to develop these calculators in Excel. Built-in worksheet formulas make it very easy to implement even the most sophisticated calculators. Here in this example, we are going to be using a spreadsheet for calculating how much you could save, using LED bulbs instead of conventional bulbs. The break-even point and return time are calculated for different scenarios.

How to create a mortgage calculator with microsoft excel

This calculator works great in Excel. But it is not very efficient to ask customers to download the document and fill it out. First, they may not have Excel on their computers and we all know of compatibility issues in different operating systems. Moreover, clients might be on a tablet or a mobile device that has limited accessibility. There may be proprietary information in the file that business owners may not want others to see. These are only a fraction of reasons why businesses prefer a different delivery instrument for these calculators, and not distribute Excel files.

This is where SpreadsheetWEB comes handy in converting these Excel based calculators into web applications. End users don’t need Excel. They can access it from any device with a browser. Business owners protect their proprietary data and algorithms. They can update underlying data and algorithms whenever they want. It will become a live tool and will keep up with the latest data. In an organic, evolving economy, keeping abreast with the latest information is paramount.

Most companies provide online calculators for free and ask users enter their email address in return. In this application, we would also like to add a “Send” button (the gray box on the bottom is suitable for this), to send the results to the email address provided. This mechanic will also deter users from entering fake emails. The email is stored in the database, widening the company’s marketing network.

Here, the end users don’t even have to have any kind of software installed. All that is needed is a web browser. This aspect prevents issues with version incompatibilities and lets you reach a wider customer base with less trouble, while making the calculator available for almost any device.

The spreadsheet first needs to be converted into an appropriate form. Thanks to SpreadsheetWEB Conversion Wizard, this process is quite straightforward and can be done in a matter of minutes. Once the conversion is complete, it is uploaded to the SpreadsheetWEB Cloud or your servers. In our case, the final product will look like this:

How to create a mortgage calculator with microsoft excel

This application can now be made public by sharing the corresponding link. Users enter their data into the designated input fields and receive a PDF document in their email, on how much they could save on electricity bills every year.

How to create a mortgage calculator with microsoft excel

The report compares possible scenarios side by side, and includes details like the payback time and total savings per year. The user realizes that a lot is wasted in their household and the company gains a potential customer. SpreadsheetWEB also features visual reporting capabilities; you can track every entry on assorted visualizations and set company goals accordingly.

Business specific online calculators are a great way to convince a doubtful customer, because claims are backed up with numbers and few can argue with that. Among several advantages in using a web application are; secure sharing, ease of use, enhanced customer base tracking and a unified network. SpreadsheetWEB allows you to create fancy calculators using Excel and then distribute them to whoever you want.

By: Waseem Patwegar

The PMT Function in Excel returns the periodic repayment amount for a loan. You will find below the steps to use Excel PMT Function to calculate monthly mortgage payments.

How to create a mortgage calculator with microsoft excel

Excel PMT Formula

You can get the exact Syntax of the PMT Function in Microsoft Excel by typing =PMT in any cell of an Excel worksheet.

Syntax of PMT Function = PMT(Rate, NPER, PV, [FV], [Type])

  • Rate: Interest Rate
  • NPER: The number of periods required to pay the loan
  • PV: The present value of the loan
  • [FV]: [Optional] – The future value or cash balance that you want after the last payment is made. Excel assumes a default value of 0 in case you do not specify anything
  • [Type]: [Optional] – Excel assumes a default value of “0” which means the payments are due on the last day of the month. You can specify the value “1” in case the payments are due on the first day of the month.

1. Find Monthly Mortgage Payments Using Excel PMT Formula

In this example, let us try to find monthly mortgage payments required to pay back a loan of $260,000 obtained at fixed interest rate of 3.69% per annum.

Similar to housing loans from a bank, the borrowed amount is to be fully paid back in 25 years using fixed monthly payments.

1. The first step is to organize the available data ( Loan Amount , Period of loan, Interest Rate ) in an Excel Worksheet and convert the data into monthly values.

How to create a mortgage calculator with microsoft excel

As you can see in above image, the Period of Loan ( Amortization period ) and Interest Rate have been converted into their equivalent monthly values.

2. Next, place curser in Cell C8 (where you want the result) and type =PMT – This will make Excel provide the Syntax of PMT Function that you can follow in the next steps.

How to create a mortgage calculator with microsoft excel

3. Going by the Syntax, select Cell C6 (0.31%) as the Rate .

How to create a mortgage calculator with microsoft excel

4. Select, Cell C5 (300) as nper or the number of payments required.

How to create a mortgage calculator with microsoft excel

5. Select, Cell B4 ($260,000) as the present value of the loan.

How to create a mortgage calculator with microsoft excel

6. Enter or Type 0 (zero) as the Future Value of the loan (to be fully paid back at the end of term).

How to create a mortgage calculator with microsoft excel

7. Select 0 (zero) as Type (Paid by End of period).

How to create a mortgage calculator with microsoft excel

8. Finally, close the bracket and hit the enter key on the keyboard of your computer to get your monthly mortgage payments.

How to create a mortgage calculator with microsoft excel

As you can see from above steps, all that is required to use the PMT Formula in Excel is to follow the Syntax of the function.

2. Quick Way to Use PMT Formula

Here is an alternate and a quick way to use the Excel PMT formula to calculate monthly mortgage payments.

1. Open Microsoft Excel Worksheet.

2. Click on Formulas tab in the top Menu bar > select Financial from ‘Function Library’ section and double-click on PMT in the drop-down menu.

How to create a mortgage calculator with microsoft excel

2. In Function arguments box, you can directly enter Rate , Nper and Pv values to calculate monthly mortgage payments.

How to create a mortgage calculator with microsoft excel

Tip: Instead of typing values, you can also point directly to Cells in the worksheet where the values for rate, Nper and Pv are located.

How to create a mortgage calculator with microsoft excel

To calculate an estimated mortgage payment in Excel with a formula, you can use the PMT function. In the example shown, the formula in F4 is:

When assumptions in column C are changed, the estimated payment will recalculate automatically.

The PMT function calculates the required payment for an annuity based on fixed periodic payments and a constant interest rate. An annuity is a series of equal cash flows, spaced equally in time. A mortgage is an example of an annuity.

To calculate the monthly payment with PMT, you must provide an interest rate, the number of periods, and a present value, which is the loan amount. In the example shown, the PMT function is configured like this:

  • rate = C5/12
  • nper = C6*12
  • pv = -C9

Because mortgage rates are annual, and terms are stated in years, the arguments for rate and periods are adjusted in this example. The rate is divided by 12 to get a monthly rate, and the term in years is multiplied by 12 to get the total number of monthly payments (nper). The present value (pv) comes from C9 which holds the loan amount. We use a minus operator to make this value negative, since a loan represents money owed, and is a cash outflow.

Note: When using PMT, always be consistent with the units provided for rate and periods.

Other formulas

The down payment amount in C8 is calculated with:

How to create a mortgage calculator with microsoft excel

Related Articles

  • How Do I Read an Amortization Table?
  • How to Decorate a Table for a Fancy Breakfast
  • How do I Figure Out Mortgage Interest Savings?
  • How to Make a Beautiful Tablescape
  • How to Saw a Table in Half for Decorating

A mortgage table, also called an amortization table or schedule, is a simple chart that shows how a mortgage balance changes with time after a series of consistent payments. The table also calculates the monthly payment amount based on the total mortgage value, the interest rate and the duration of the loan. You can create mortgage tables in Excel easily. The program offers financial functions to calculate payments, and the columnar grid allow for easy arrangement of a payment series. While you could program Excel to create a mortgage table from scratch, many free tables are available online.

Download the “Amortization Schedule for Excel” by Vertex 42 and distributed by CNET. This free Excel spreadsheet offers pre-programmed formulas and a formatted table structure to complete any mortgage table. You do not need Excel programming knowledge to make your table. Since the program already includes all the financial functions to calculate your mortgage scenario, you don’t need to fully comprehend the details of the mathematics, either, since they are all taken care of for you. To use the “Amortization Schedule for Excel,” you simply type in the total value of the mortgage, the interest rate and the term or duration of the loan. The program takes care of the rest by showing due dates, payment amounts and the effect your payments and interest have on the remaining mortgage balance over time.

Install official Microsoft templates for Excel that cover loan and mortgage amortization schedules and charts. Microsoft provides templates of many kinds, free of charge, to Office users. The “Loan amortization schedule template” provides all the basic components of a mortgage table: the amount of each payment, the portion of each payment that reflects interest paid, and the amount of each payment that contributes to paying down the mortgage balance. The template takes advantage of built-in financial functions in Excel to complete all the calculations for you. Microsoft also provides the “Loan analysis worksheet,” which displays a table for comparing different mortgage terms. You can see how monthly payments change based on small differences in interest rates.

Download the “Loan and Mortgage Calculator Template for Excel” by Spreadsheets 123. This Excel spreadsheet contains several columns of information so that you can control even the most flexible of payment plans on your mortgage. The mortgage table includes options for occasional extra payments. It keeps track of your total payments to date, and also calculates the interest you save by paying more than the required amount each month. In addition to these features, it calculates all the standard mortgage table formulas, and shows how each payment contributes to the balance due.

The Home Mortgage Tracker Spreadsheet allows you to track and analyze your current loan agreement to easily consider the total cost of the mortgage. Use the interactive dashboard to understand when your loan will be paid off, how much interest you are paying the bank, and what happens if you increase your monthly payments.

Current Version: Version 1.3
Compatibility: PC Only | Excel 2007 or later | Works on 32-bit or 64-bit
License: Each template purchased equates to one single-user license

Your home mortgage is most likely the largest debt you will take on during your lifetime (it certainly was for me). One major problem I ran into after purchasing my new home and getting my mortgage setup, was knowing how to plan out my payments. Do I go with the minimum and just pay what the bank tells me to do? Do I overpay? How much should I overpay? These were all questions that ran through my head and inspired me to put together a spreadsheet that helped me track my loan and understand exactly how much interest I was paying to the bank.

After putting this spreadsheet together, I was able to easily see how I could save over $10,000 if I just increased my monthly payment by a few hundred dollars. It also served as motivation to try to control my expenses and put more money towards my loan as I could easily see how increases of an extra $25, $50, and $100 made a huge impact to the amount of interest I would be paying over the life of my mortgage loan.

I saw how valuable this spreadsheet was for my personal finance and put a ton of effort into it to make it efficient enough to work for everyone. So go ahead and check out what I’ve put together and determine if one of the two offerings is something that would be useful for you!

Chris
Founder, TheSpreadsheetGuru.com (and proud homeowner)

“>,”hSize”:5,”floatDir”:”right”,”html”:” \n “,”url”:”https://youtu.be/U7Pzfq9ktz4″,”height”:480,”thumbnailUrl”:”https://i.ytimg.com/vi/U7Pzfq9ktz4/hqdefault.jpg”,”resolvedBy”:”youtube”>” data-block-type=”32″ >

The Dashboard is where you will spend most of your time. This single tab will consolidate all the important information about how you are tackling paying off your home mortgage. You can see when your loan is projected to be paid off, what your average payment has been, and track how much interest expense your have prevented.

There is also a section that will allow you to quickly perform a scenario analysis on your monthly payments. Just enter in how much extra per month you believe you could put towards paying off your loan and see how that impacts your projects.

To use this template, you will need to provide just a few things about your loan agreement:

Which Currency do you want your numbers in (Dollars, Euros, Yen, or Pounds)?

Interest Rate.

The amount of money that was loaned to you.

How long you have to pay off the loan (typically 15 or 30 years)?

The start date of your loan.

Your current Escrow payment.

Are you making payments Monthly, Bi-Weekly, or Weekly?

What was your down payment for your home?

How much have you spent on home improvements (ie new furnace, finished basement, etc…)?

After you have entered in those key data points, just click the Refresh Loan Table button and the spreadsheet will do the rest!

How to create a mortgage calculator with microsoft excel

After you have entered in your loan information, you will immediately see some calculated information about your loan. For example, you will learn what month/year you will be paying off your loan and also how much interest you will be paying the bank. You can then continue to the Amortization table and enter in any changes to your planned payments (see next section).

Unless you are an accountant, you are probably cringing (or completely terrified) after seeing the word: Amortization! Don’t worry, I’ve gone ahead and done all the hard work and created all the formulas that will track the interest, principal, and current balance of your loan. Instead of an “Amortization Table”, let’s just call it your “Loan Table”. This table is going to perform the same calculations your bank will be using to track the progress of how you are paying back your loan.

All you need to do is go into this table each month and enter your loan payment amount, Escrow payment (if applicable), and interest rate (if you don’t have a variable interest rate, it will remain the same no matter what). That’s all there is to it! You can then proceed to your awesome Dashboard tab and get an nice visual update on how you are doing with your loan.

I know not everyone has the money to buy a fancy spreadsheet, so I went ahead and created a simple & clean Loan Amortization Table spreadsheet that will help you track your monthly payments and keep track of how much interest you will be paying over the course of the loan. This single tab Excel file is easy to maintain and does a great job at helping you track your loan.

There are two versions of this template available to download. Skim down through the Feature Grid below, so you can determine which version is right for you!

Free Future Updates

As with all purchased products sold by TheSpreadsheetGuru, you don’t have to worry about purchasing too early! After you purchase this template, you will be placed on an exclusive email list where you will directly receive any update made to this product.

30-Day Money Back Guarantee

I’m so confident you will love this Excel spreadsheet, I’m willing to give you 30 days from your purchase date to decide if this is the tool for you. If you come to the conclusion that this Excel spreadsheet was not what you were expecting, just shoot me an email and I’ll refund you your money back.

So what’s there to lose? Now is your opportunity to pick up this revolutionary Excel spreadsheet that will bring clarity to your home loan and hopefully give you the confidence to save some money on interest.

Click the Buy Now button below to quickly get your hands on this amazingly powerful Excel spreadsheet!

You now have the ability to track and analyze how you have been paying off your home mortgage. Knowing the amount of interest you could potentially end up paying versus how much you can afford to prevent is tremendously powerful and using this spreadsheet is going to make the process so much easier for you. Pickup a copy of the Home Mortgage Tracker Excel spreadsheet today!

The use of a “mortgage calculator” can be very helpful when trying to figure out monthly housing payments, how much one can afford, and how one should structure a mortgage. You really should use these one (or two or three) before you consider buying real estate.

Check Out My Web-Based Mortgage Calculators

  • Mortgage payment calculator
  • How much house can I afford calculator
  • Refinance calculator
  • Early mortgage payoff calculator
  • Rent vs. buy calculator

There are a number of different mortgage calculators available that serve all types of different purposes. Some are very simple, while others are very involved, including things like property taxes, homeowners insurance, HOA dues, and so forth.

There’s also the question of accuracy, as some may underestimate the true cost of homeownership. I’d say they more often underestimate rather than overestimate, which is an obvious problem for would-be home buyers, especially first-timers who may also downplay the many hidden costs involved.

Anyway, let’s stop worrying and start calculating, shall we?

You may want to start with the classic mortgage payment calculator, which as the name implies, allows you to calculate a monthly mortgage payment. This is ultra-basic, but also a good first step in determining how much a given loan amount will set you back. And this is before we even factor in insurance, taxes, and utilities. It can really add up.

You can use it to calculate a 30-year fixed mortgage, a 15-year fixed, or any other loan term of your choosing. It’s very simple to use and is a good starting point on your mortgage journey.

If you’re a first-time home buyer or a seasoned pro looking to buy a second or third home, the how much house can I afford calculator might be just the ticket to narrow down your real estate search.

If you’re already a homeowner and have an existing mortgage, you can check out my refinance calculator, which will factor in your current home loan and proposed refinance loan to determine if it’s more or less a good deal.

It doesn’t always make sense to refinance, depending on your unique situation, so taking the time to run the numbers might be a good idea before you dive in.

Once you do find that forever home, you might be interested in paying off your mortgage once and for all, and perhaps even early.

If that’s your goal, check out the early mortgage payoff calculator, which allows you to plug in extra payments in a variety of different ways to see how quickly you can pay off your mortgage, and more importantly, how much you can save on interest over the life of the loan.

Excel-Based Mortgage Calculators

  • If you prefer an offline mortgage calculator
  • To a web-based one
  • I also have some free Excel-based calculators available
  • Including a DTI calculator, a blended rate calculator, and a neg-am calculator

Back in the day, I created a few mortgage calculators using Microsoft Excel. Unfortunately, I’m not able to integrate the calculators into my blog, so you’ll have to download the Excel spreadsheets below. They are safe. Use a virus checker if you are at all concerned or simply stick to the web-based mortgage calculators above instead.

If you’re thinking about buying a piece of property you’ll need to know your debt-to-income ratio. Use this handy mortgage calculator to tally up your monthly debts and proposed housing payment and then compare the sum to your monthly income. It’s much better than simply ballparking those figures.

Another crucial mortgage calculator is the interest-only payment mortgage rate calculator. It calculates your monthly mortgage payment if you just pay the interest portion of your mortgage payment. Many borrowers choose interest-only home loans to save money, but it won’t pay off any of the principal of your home.

If you want to know the total principal and interest payment each month, try my 30 year mortgage calculator. It calculates your monthly payment based on a 30-year fixed-rate, assuming you pay the full principal and interest payment each month. This payment is always higher than an interest-only payment, but some of the monthly payment actually goes towards the ownership of your home.

If you’re considering a combo, that is, a first and second mortgage, use my “blended rate mortgage calculator” below to decide if two loans are better than one. This calculator generates a single interest rate based on both proposed loan balances and their corresponding interest rates. First learn more about how to figure out blended rates, then download the calculator!

Here is my personal favorite, the negative amortization mortgage calculator. Businessweek magazine featured this type on mortgage as their cover story no more than a couple months ago. For anyone thinking about an option-arm/neg-am, this calculator is a must!

Here is a new calculator I have been using for my own finances for the last few months. It allows me to manage all my credit cards, including their balances, minimum payment, due date, APR, and total credit line. It also allows me to see my aggregate credit line and total available credit percentage. It’s a handy tool that will keep you up-to-date and also give you a good idea of how your credit appears to possible creditors.

Bookmark this page!

1 thought on “Mortgage Calculators”

Hey Colin, I need to create a spreadsheet for an amortized loan that has the ability to accept input for any payment along the way (from 0-payoff) and re-amortize accordingly. Kind of a combination of the old ‘extra-payment’ loan amort template that was and is widely available and a neg am version.

do you have anything like this already so I don’t have recreate it?

Model loan amortization with easy-to-use templates

How to create a mortgage calculator with microsoft excel

How to create a mortgage calculator with microsoft excel

How to create a mortgage calculator with microsoft excel

Spreadsheets are powerful tools that help you understand how a loan works. They make it easy to see important details about your loan, and the calculations are more or less automated. You can even use pre-built loan amortization templates that allow you to simply enter a few details about your loan.

Spreadsheets are available from several popular providers, and the instructions here will work with Microsoft Excel, Google Sheets, Apple Numbers, Apache OpenOffice, and many others.

DIY vs. Calculator Templates

There’s no need to do it all yourself—unless you really want to. Templates allow you to plug in a few details about your loan and be done with it, and those pre-built templates are easy to use.

To use a loan template in Excel, open the program and if “Loan Amortization” is not an immediate option, type those words into the search box.  

Once you’re in the template, fill in information related to your loan:

  • Loan amount: Enter the amount you are borrowing.
  • Annual interest rate: Use the interest rate on your loan (you can either use APR or a stated interest rate, if available). You should not need to convert to decimal format, but make sure the rate is displayed correctly.
  • The number of payments per year: How frequently do you pay? For monthly payments, for example, enter 12.
  • Start date of loan: This might be helpful for planning, but is not essential for accurate calculations.
  • Optional extra payments: If you’ll pay extra—or if you want to know how helpful it would be to pay extra—use this field. Pay attention to how much you save on cumulative interest, and how much more quickly the loan gets paid off when you pay extra.

How to Build an Excel Loan Model

Doing it yourself takes more time, but you’ll develop financial knowledge and spreadsheet skills you can’t get from a template. Plus, you can customize to your heart’s content. That said, after you’ve done this a few times, you might find it faster to use a template to start the process, then “unprotect” the template and make your modifications.

Let’s say you want a model that shows you every yearly or monthly payment. Start at the top row of your spreadsheet and add the following section titles to each row (where you’ll enter information about your loan):

  • Loan amount
  • Interest rate
  • Number of periods (or loan term)

Give those cells a green fill color, which tells you that you can change those values as you compare loans and run what-if scenarios.

Now you’re ready to make your data table. Create a row with these column names:

  • Period number
  • Starting loan balance
  • Payment
  • Amount applied to interest
  • Amount applied to the principal
  • Remaining loan balance

You can also add additional columns (such as cumulative interest paid). It’s up to you.

Run Your Calculations

Next, you’ll need a row for each payment as part of your data table. In the far left column of your spreadsheet (below the “Period number” column described above), add one number on each row: The first row is “1,” then move down a row for “2,” and so on. Each row is one payment. For a 30-year loan, you’d have 360 monthly payments—for large numbers like that it’s easiest to fill in the first few periods and use Excel’s “fill handle” to fill in all of the remaining rows.

Now, have Excel fill in and calculate values for you. Remember to use the “$” before any row number in a formula in your calculations except the Period—otherwise, Excel will look in the wrong row.

  1. Use the PMT function to calculate your monthly payment (using information from your “input area”). This payment generally does not change over the life of the loan, so this function would be the same all the way down. (The Excel function is: “=PMT(‘Loan amount,’ ‘Interest Rate,’ ‘Periods’)”)
  2. Use the IPMT function to show the amount of each payment that goes to interest. (Same formula as above, just with IPMT at the beginning)
  3. Subtract the interest amount from the total payment to calculate how much the principal you paid in that month.
  4. Subtract the principal you paid from your loan balance to arrive at your new loan balance.
  5. Repeat for each period (or month).

Note that after the first row of your data table, you’ll refer to the previous row to get your loan balance.

If your loan uses monthly payments, make sure you set up each period correctly in the formulas. For example, a 30-year loan has 360 total periods (or monthly payments). Likewise, if you’re paying an annual rate of 6% (0.06), you should make the periodic interest rate of 0.5% (or 6% divided by 12 months).

If you don’t want to do all the work of working in spreadsheets, there’s an easier way. Use an online Loan Amortization Calculator. These are also helpful for double-checking your spreadsheet’s output.

What You Can Do With Your Spreadsheet

Once you’ve got your loan modeled, you can learn a lot about your loan:

Amortization table: Your spreadsheet shows an amortization table, which you can use to create a variety of line charts. See how your loan gets paid off over time, or how much you’ll owe on your loan at any given date in the future.

Principal and interest: The spreadsheet also shows how each payment is broken into principal and interest. You’ll understand how much it costs to borrow, and how those costs change over time. Your payment stays the same, but you’ll pay less interest with each monthly payment.

Monthly payment: Your spreadsheet will perform simple calculations as well. For example, you’ll need to calculate the monthly payment. Changing the loan amount (if you consider buying something less expensive, for example) will affect your required monthly payment.

“What if” scenarios: The benefit of using spreadsheets is that you have the computing power to make as many changes to the model as you want. Check to see what would happen if you make additional payments on your loan. Then see what happens if you borrow less (or more). With a spreadsheet, you can update the inputs and get instant answers.

Keeping up with one’s financial calculations can get messy, especially when it comes to interest rates and other banking matters. You can always hire an expert consultant to explain everything and run your matters, but sometimes, especially when it comes to simpler loan calculations, it pays off to learn how it works and use some tools to streamline the job.

In this week’s free MS Excel template, we intend to show you how easily Excel manages to handle these calculations with its intrinsic formulas.

The Simple Loan Calculator for Microsoft Excel uses built-in financial formulas to calculate either the interest rate (using the RATE formula), the loan amount (using the PV formula), the payment (using the PMT formula), or the number of payments (using the NPER formula).

Have a peek at the Simple Loan Calculator Excel Template:

How to create a mortgage calculator with microsoft excel

How to use the Simple Loan Calculator Excel Template

You can calculate 4 different types of loans with this free Excel template. A quick description of these types of loan calculations is provided below:

Please note that Periods Per Year is the number of payments per year. Enter 12 for Monthly, 52 for Weekly or 1 for Annual payments.

1- Loan Amount: This is the amount that you have borrowed. You can also enter your current balance if you also adjust the Term of Loan to be the number of years left to pay off the loan.

2- Annual Interest Rate: This calculator assumes a fixed interest rate, and the interest is compounded each period.

3- Payment (Per Period): This is the amount that is paid each period, including both principal and interest (PI).

4- Term of Loan (in Years): Mortgage loans usually have 15 or 30-year terms. Auto loans are usually between 2 and 5 years. For a 6-month term, enter =6/12 or 0.5. If you entered your current balance in the Loan Amount, then for the Term enter the number of years you have left until your loan is paid off.

As you can see, this Excel template is quite useful and easy to learn.

Download your free Excel template “Simple Loan Calculator” here

  • Simple Loan Calculator in Excel Document format (xlsx)
  • Simple Loan Calculator Excel Document in zip file format (zip)

More interest rate calculator Excel templates online:

I made a quick search for interest rate calculators and found some helpful URLs. Hope you find them useful too:

We’ve got some good news for you!

Recently we just released a new version of our Compound Interest Loan Calculator in Excel.

It’s often called “Amortization Schedule with Principal and Interest Amounts” which is basically the same thing as simply “Loan Calculator”.

The enhancements are:

  1. Added Compounding Interest Frequency (daily, weekly, fortnightly and monthly)
  2. Possibility to change Repayment Dates
  3. Option to update Repayment Amount
  4. Password unprotected worksheet (Paid version only)

The above changes make this Excel Calculator even better and far more flexible!

To download Demo version: click here or on the Excel icon.

To buy Full version: click here.

Now we would like to tell you a bit more about what these enhancements will mean for you.

1. Interest Compounding Frequency (daily, weekly, fortnightly and monthly)

As you probably know, Payment Frequency and Interest Compounding frequency are not the same things.

Payment Frequency means how often you make your repayments.

Interest Compounding Frequency means how often your Bank calculates and ads Interest to your Loan Balance. It’s not the same thing if they do it let’s say monthly or daily. With mortgages the difference can be few thousands of dollars over many years.

Here is the logic:

Let’s say your Loan interest is 6% per year (p.a.). If you make monthly repayments and also your Interest is compounded monthly then the total interest paid per year will be slightly higher than 6%.

Because the banks like to make money and they do so even by not disclosing the whole story (or disclosing it only in the small print).

Let’s have a look how it works.

If your yearly Interest rate is 6.00% then your monthly rate is 6/12 = 0.5%

The first month the bank will calculate Interest as 0.5% of your outstanding Loan Balance and add it to your Loan Balance. The second month the bank will do the same. But this time it will also be the interest on top of interest from the previous month.

Are you beginning to see the picture?

If they do it every month then your effective interest rate per year will be like this:

Effective Interest rate = (1 + 0.06/12)^12 – 1 = 0.0617 = 6.1679%

So in fact you are not paying 6% interest rate but 6.17%. That’s why the banks make so much money!

And what’s the worst part?

Well, the true is that most banks don’t compound the interest monthly. They push it even more. They do it daily! Buggers…

So, if they compound it daily, let’s have a look what the result is now:

Effective Interest Rate = (1 + 0.06/365)^365 – 1 = 6.1831%

You see, the difference between monthly and daily compounding may not look big, but if your loan is couple of hundred thousand dollars then over time the amount can be significant.

The problem is even more apparent with let’s say credit cards because the base interest rate is usually much higher than mortgages.

If your credit card yearly interest rate is 20% then daily compounding will push it to 22.13%.

The moral of the story:

Be aware of the compounding interest and ask your bank upfront how they calculate it.

If the bank employee plays dumb or pretend he or she doesn’t know what you are talking about, ask their manager. Don’t sign up for any loan before you know exactly what method they use.

To help you calculate the exact Interest Amount for each method we added an option to choose monthly, fortnightly, weekly and daily interest compounding rate or frequency.

Now, let’s have a look at other adjustments we made to our Loan Amortization Schedule Calculator.

2. Possibility to change Repayment Dates

It’s nice to calculate ideal Amortization Schedule and assume that you pay your repayments exactly on due day, every time. The reality is that it’s often not the case.

Sometime you miss payment for just couple of days because you forgot about it or had insufficient funds in your bank account.

As you probably know, the banks are unforgiving. If that happens, the bank will simply add Interest to your Loan Balance for every single day overdue.

After some time the actual Loan balance will differ significantly from the original amortization schedule.

To help you modelling your loan based on real life scenarios, we added possibility to adjust or change actual Repayment Date. This way you will be able to predict actual Loan Balance more accurately.

One thing to remember:

Once you change the Repayment Date, the next Repayment will also be adjusted to align with your Repayment Frequency. To get it back to your original schedule, adjust the next Payment Date back to what it ought to be.

3. Option to update Repayment Amount

Similarly as missing your repayments or paying a bit late, you can also experience paying different amount.

If this happens, simply overwrite the Repayment amount for that particular period.

You can also use the field Extra Payment for that purpose. It’s possible to add both positive and negative amounts into this field.

4. Password unprotected worksheet (Premium version only)

Our previous version of the calculator was password protected. The main reason was to avoid accidentally deleting or changing formulas. This can make the calculator to work incorrectly.

However, we found out that this safeguard put in place was restricting some of you. We have therefore decided to remove all protection.

You can now freely change everything in the calculator. As a safety measure we recommend following:

After downloading the calculator; save the original file somewhere on your PC. Then create a copy of the calculator and work with the copy. If you accidentally screw something, simply make a new fresh copy from the original file and start again.

We hope you will enjoy the new version of our Loan Amortization Schedule Calculator!

If you have any questions or suggestions to improve it even further, we would love to hear from you! Send us an email or leave comment under this article.

This is a mortgage payment calculator that will easily give you an estimation about your monthly mortgage payments and find out how much you can afford before you borrow. This calculator is created using calc built-in PMT function. This function become a built-in financial function because this is a basic formula that financial people should know when they learn basic finance. You can modify the formula to calculate other mortgage information. For example, You have to modify the formula if you want to know how many years you can pay your mortgage if your monthly budget is limited. But, if you just want to know the amount of money you have to pay monthly within your loan period, you can use this tool directly. Just type mortgage amount, interest rate and loan period in corresponding cells, and the formula will give you the results.

Leave a Reply Cancel reply

Search

Categories

  • Calc
  • Description
  • Writer

Recent Templates

  • 2017 Calendar Template
  • 2016 Calendar Templates
  • 2015 Calendar Templates
  • Grocery List Template
  • Net Present Value Calculator
  • Periodic Table of Elements
  • Bowling Scoresheet
  • Biorhythm Chart
  • Issue Log
  • Warehouse Inventory List

Excel is an indispensable productivity tool. Users across all industries and job functions embrace it as a tool for storing, tracking, and manipulating all kinds of data. It’s used for everything from simple task tracking and data management, to complex calculations and professional reporting. You can use the Excel REST API in Microsoft Graph to extend the value of your data, calculations, reporting, and dashboards.

Why integrate with Excel?

You can use Microsoft Graph to allow web and mobile applications to read and modify Excel workbooks stored in OneDrive, SharePoint, or other supported storage platforms.

Perform calculations

Users love the ease with which they can perform deep and complex calculations within Excel. You can now access Excel’s powerful calculation engine with instant results. For example, a mortgage calculator can take advantage of the PMT function from Excel by making a simple API call that includes principal, rate and number of payments. Excel does all the difficult work and returns the monthly payment instantly. With more than 300 Excel worksheet functions currently available, you have full access to the breadth of formulas supported by Excel today. Complex business models don’t need to be rebuilt repeatedly. Developers can program Excel to perform those calculations instantly and retrieve the results with simple API calls.

Generate reports and analyze results

Excel is a flexible reporting and analysis tool, from simple data tables to complex professional dashboards. Today, we give you full access to all of Excel’s reporting features, making Excel an online reporting service within Microsoft 365. Imagine any of the reporting scenarios users create and rely on today pulled into a custom app to create professional charts or analyze large sets of data intelligently, seamlessly blending Excel into those customized experiences.

Store and track data

Excel is also a great tool to store and track data. If your information is stored in a workbook, that data is available to any app that integrates with Microsoft 365. Its contents are available to read from custom solutions, and those solutions can use Excel for data storage.

Note: The Excel REST API supports only Office Open XML file formatted workbooks (files with the .xlsx extension). The .xls extension workbooks are not supported.

Using the Excel REST API

You can use Microsoft Graph to allow web and mobile applications to read and modify Excel workbooks stored in OneDrive, SharePoint, or other supported storage platforms. The Workbook (or Excel file) resource contains all the other Excel resources through relationships. You can access a workbook through the Drive API by identifying the location of the file in the URL. For example:

You can access a set of Excel objects (such as Table, Range, or Chart) by using standard REST APIs to perform create, read, update, and delete (CRUD) operations on the workbook.

API reference

Looking for the API reference for this service?

How to create a mortgage calculator with microsoft excel

How to create a mortgage calculator with microsoft excel

Overview of How to Create an Excel Spreadsheet

If you are a fresher, then it is important to know how you can create and start the spreadsheet with Excel. Over the years, spreadsheets are playing a vital role in maintaining a large database with excel. Data Analysis and Number Crunching are the main purposes we are using spreadsheet day in day out. In fact, many people use this spreadsheet to maintain their business needs and personal things as well.

Using spreadsheets, I have seen many people manage their family budgets, mortgage loans, and various other things for their fitting needs on a daily basis. In this article, we will show you how to create an excel spreadsheet, tools available with the spreadsheet, and many other things.

How to create a mortgage calculator with microsoft excel

You are free to use this image on your website, templates etc, Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For eg:
Source: How to Create an Excel Spreadsheet? (wallstreetmojo.com)

Understanding Excel Workbook Screen

When we open the excel screen, we could see the below features in front of us.

#1 – Ribbon

These menu options are called “ribbon” in excel. In the ribbon, we have several tabs to work with. Going forward, we will explore each one of them in detail.

How to create a mortgage calculator with microsoft excel

#2 – Formula Bar

The formula bar in excel is the platform to view the formula or the value of the selected cell or active cell. For example, if we have 5 in the cell A1, if the A1 cell is selected, we can see the same value in the formula bar.

#3 – Column Header

As you can see, each column has its own heading with alphabet characters representing each column separately.

How to create a mortgage calculator with microsoft excel

#4 – Row Header

Column headers are represented by alphabets, and similarly, row headers Row Headers Excel Row Header is the grey column on the left side of column 1 in the worksheet that contains the numbers (1, 2, 3, etc.). To hide or reveal row and column headers, press ALT + W + V + H. read more are represented by numbers starting from 1. In recent versions of Excel, we have more than 1 million rows.

How to create a mortgage calculator with microsoft excel

#5 – Spreadsheet Area

This is where we do the work. As you can see in the above overview image, we have small rectangular boxes, which are plenty. The combination of column and row forms a cell, i.e., a rectangular box. Each cell was identified by a unique cell address consisting of a column header followed by a row header. For the first cell, the column header is A, and the row header is 1, so the first cell address is A1.

How to create a mortgage calculator with microsoft excel

This is the general overview of the excel spreadsheet. Now we will see how to work with this spreadsheet.

How to Work with Excel Spreadsheet?

Let’s look at the example given below.

  1. To work with a spreadsheet, first, we need to select the cell we are looking to work with. For example, if you want the word “Name” in the cell A1, then select the cell and type “Name” in the cell.

How to create a mortgage calculator with microsoft excel

Select the cell B1 and type Price.

How to create a mortgage calculator with microsoft excel

Now come back to cell A2 and type some fruit names.

How to create a mortgage calculator with microsoft excel

In the associated column, enter the price of each fruit.

How to create a mortgage calculator with microsoft excel

This is the simple table we have created with excel.

Steps to Format Excel Spreadsheet

Step #1

Select the header and make the font, Bold. The excel shortcut key Excel Shortcut Key An Excel shortcut is a technique of performing a manual task in a quicker way. read more to apply bold formatting is Ctrl + B.

How to create a mortgage calculator with microsoft excel

Step #2

Make center alignment.

How to create a mortgage calculator with microsoft excel

Step #3

Now fill the background color for the selected cells.

How to create a mortgage calculator with microsoft excel

Step #4

Change the font color to white.

How to create a mortgage calculator with microsoft excel

Step #5

Now apply borders to the data. Select the entire data range to apply borders.

How to create a mortgage calculator with microsoft excel

Now the data looks like an organized one. Like this, we can create a spreadsheet and work with it.

This is the basic level introduction to excel spreadsheet. Excel has a wide variety of tools to work with. We will see each of the tool explanation in separate dedicated articles which expose you to the advanced features.

Recommended Articles

This has been a guide to Create an Excel Spreadsheet. Here we will show you how to create a spreadsheet through excel, general overview tools available along with examples, and a downloadable excel template. You may learn more about excel from the following articles –