How To Calculate Loan Payments In Excel

Loan Amortizationwith Microsoft Excel

Are you a student? Did you know that Amazon is offering 6 months of Amazon Prime – free two-day shipping, free movies, and other benefits – to students?

This is the first of a two-part tutorial on amortization schedules. In this tutorial we will see how to create an amortization schedule for a fixed-rate loan using Microsoft Excel and other spreadsheets . Almost all of this tutorial also applies to virtually all other spreadsheet programs such as Open Office Calc and . Spreadsheets have many advantages over financial calculators for this purpose, including flexibility, ease of use, and formatting capabilities.

You can download the example spreadsheet or follow the example and create your own.

Fully amortizing loans are quite common. Examples include home mortgages, car loans, etc. Typically, but not always, a fully amortizing loan is one that calls for equal payments throughout the life of the loan. The loan balance is fully retired after the last payment is made. Each payment in this type of loan consists of interest and principal payments. It is the presence of the principal payment that slowly reduces the loan balance, eventually to $0. If extra principal payments are made, then the remaining balance will decline more quickly than the loan contract originally anticipated.

Things To Remember About Excel Mortgage Calculator

• The Excel shows the monthly payment for the mortgage as a negative figure. This is because this is the money being spent. However, if you want, you can make it positive also by adding sign before the loan amount.
• One of the common errors that we often make when using the PMT function is that we dont close the parenthesis, and hence we get the error message.
• Be careful in adjusting the interest rate as per monthly basis and loan time period from years to no. of months .

Example : Payment Date Calculations

In this example, the PMT function is used at the top of the worksheet, to calculate the monthly payment amount. Here is the formula in cell E2, which is named LoanPmt.

• =-PMT

Payment Date Table

The first payment date is also entered at the top of the sheet, in cell A2, and a payment table calculates all the payment days, plus the interest and principal amounts each month.

NOTE: There are 48 rows in the table, and you can add more rows if needed. The formulas should fill in automatically.

Table Formulas

Here are the formulas used in row 7 of the payment date table:

• Pay Date: =IF, MONTH+G7-1, DAY))
• Outstanding: =IF)
• Total Princ Paid: =IF)
• Pmt Num: =IF< LoanMths,SUM,””)

In the table, the latest payment row is highlighted, based on a Conditional Formatting rule

• =$A7=INDEX,$A$7:$A$54,1))

In the Conditional Formatting rule:

• MATCH looks for the current date in the payment date list
• If the current date is not found, MATCH returns the location of the latest date before the current date
• Then, the INDEX function returns the date from that location in the list of payment dates
• If the date in the current row matches that date, the row is highlighted with light orange

You May Like: Amortization Schedule For Auto Loan

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before Encrypt Cells with password Create Mailing List and send emails…
• Super Formula Bar Reading Layout Paste to Filtered Range…
• Merge Cells/Rows/Columns without losing Data Split Cells Content Combine Duplicate Rows/Columns… Prevent Duplicate Cells Compare Ranges…
• Select Duplicate or Unique Rows Select Blank Rows Super Find and Fuzzy Find in Many Workbooks Random Select…
• Exact Copy Multiple Cells without changing formula reference Auto Create References to Multiple Sheets Insert Bullets, Check Boxes and more…
• Extract Text, Add Text, Remove by Position, Remove Space Create and Print Paging Subtotals Convert Between Cells Content and Comments…
• Super Filter Advanced Sort by month/week/day, frequency and more Special Filter by bold, italic…
• Combine Workbooks and WorkSheets Merge Tables based on key columns Split Data into Multiple Sheets Batch Convert xls, xlsx and PDF…
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Calculate Weekly Monthly Quarterly And Semi

Depending on the payment frequency, you need to use the following calculations for rate and nper arguments:

• For rate, divide the annual interest rate by the number of payments per year .
• For nper, multiply the number of years by the number of payments per year.

The below table provides the details:

For instance, to find the amount of a periodic payment on a $5,000 loan with an 8% annual interest rate and a duration of 3 years, use one of the below formulas.

Weekly payment:

=PMT

Monthly payment:

=PMT

Quarterly payment:

=PMT

Semi-annual payment:

=PMT

In all cases, the balance after the last payment is assumed to be $0, and the payments are due at the end of each period.

The screenshot below shows the results of these formulas:

Recommended Reading: Is Bayview Loan Servicing Legitimate

Loan Payment Schedule Table Columns

There are 7 columns in the payment details table. Here are the column names and purpose:

• Pay Date: Payment due date
• Outstanding: Outstanding loan amount, before the payment is made
• Mth Pmt: Payment amount, from the yellow cell at the top of the worksheet
• Interest: How much of the monthly payment is for interest?
• Principal: How much of the monthly payment goes toward the principal?
• Total Princ Paid: Running total of the principal amounts paid to date
• Pmt Num: Payment number

There are details on the loan payment schedule formulas, and how they work, further down the page.

Tip: Return Payments As Positive Numbers

Because a loan is paid out of your bank account, Excel functions return the payment, interest and principal as negative numbers. By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above.

If you prefer to have all the results as positive numbers, put a minus sign before the PMT, IPMT and PPMT functions.

For the Balance formulas, use subtraction instead of addition like shown in the screenshot below:

Read Also: Aer Scholarship For Spouses

What Is The Formula For Compound Interest

The formula for compound interest is similar to the one for Compounded Annual Growth Rate . For CAGR, you compute a rate which links the return over a number of periods. For compound interest, you know the rate already. You are simply calculating what the future value of the return might be.

To reach the formula for compound interest, you algebraically rearrange the formula for CAGR. You need the beginning value, interest rate and number of periods in years. The interest rate and number of periods need to be expressed in annual terms, since the length is presumed to be in years. From there you can solve for the future value. The equation reads:

Beginning Value x ^ = Future Value

This formula looks more complex than it really is, because of the requirement to express it in annual terms. Keep in mind, if it’s an annual rate, then the number of compounding periods per year is one, which means you’re dividing the interest rate by one and multiplying the years by one. If compounding occurs quarterly, you would divide the rate by four, and multiply the years by four.

Get The Remaining Balance

To calculate the remaining balance for each period, we’ll be using two different formulas.

To find the balance after the first payment in E8, add up the loan amount and the principal of the first period :

=C5+D8

Because the loan amount is a positive number and principal is a negative number, the latter is actually subtracted from the former.

For the second and all succeeding periods, add up the previous balance and this period’s principal:

=E8+D9

The above formula goes to E9, and then you copy it down the column. Due to the use of relative cell references, the formula adjusts correctly for each row.

That’s it! Our monthly loan amortization schedule is done:

Recommended Reading: How To Transfer Car Loan To Another Person

What Is The Formula To Calculate Payment Of A Loan

The payment on a loan can also be calculated by dividing the original loan amount by the present value interest factor of an annuity based on the term and interest rate of the loan. This formula is conceptually the same with only the PVIFA replacing the variables in the formula that PVIFA is comprised of.

What Are The Main Components Of A Loan Comparison Calculator

The fields and categories of this calculator may vary in the detailing of information as well as the number of loans it is able to compare. However, generally, the following elements are there in this tool:

• Loan amount.
• Amortization period and number of payments.
• Monthly payment amount.
• Any additional payments, such as initialization fee, processing fees, etc.
• Total interest.

Recommended Reading: Aer Loan Requirements

Calculating Interest And Principal In A Single Payment

Let’s start by reviewing the basics with an example loan :

Imagine that you are about to take out a 30-year fixed-rate mortgage. The terms of the loan specify an initial principal balance of $200,000 and an APR of 6.75%. Payments will be made monthly. What will be the monthly payment? How much of the first payment will be interest, and how much will be principal?

Our first priority is to calculate the monthly payment amount. We can do this most easily by using Excel’s PMT function. Note that since we are making monthly payments, we will need to adjust the number of periods and the interest rate to monthly values. We will do this within the PMT function itself. Open a new spreadsheet and enter the data as shown below:

Recall that the PMT function is defined as:

where Rate is the per period interest rate and NPer is the total number of periods. In this case, as shown in the picture, we calculate the Rate with B4/B5 , and NPer is B3*B5 . PV is entered as -B2 . You can see that the monthly payment is $1,297.20.

That answers our first question. So, we now need to separate that payment into its interest and principal components. We can do this using a couple of simple formulas :

Monthly Interest Payment = Principal Balance x Monthly Interest Rate

Monthly Principal Payment = Monthly Payment – Monthly Interest Payment

Using these formulas, we can see that the interest component of the first payment would be:

Interest in 1st Payment = 200,000 x 0.005625 = $1,125

What Is The Pmt Function

The PMT function is categorized under financial Excel functionsFunctionsList of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst. The function helps calculate the total payment required to settle a loan or an investment with a fixed interest rate over a specific time period.

=PMT

The PMT function uses the following arguments:

Don’t Miss: How To Get An Aer Loan

Microsofts Free Loan Calculator Template

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 Download button.

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

Understanding Your Credit Score

A good credit score is important for your ability to qualify for a home loan. Good credit usually ranges from 630-730, but some lenders will offer a better rate of interest given a credit score of 680.

So given the wide selection of acceptable credit scores, it is vital to know what factors can increase or decrease your likelihood of being approved for a home equity credit.

You May Like: How Long For Sba Loan Approval

How Can You Resize The Column

To resize the column, you should change the width of one column and then drag the boundary on the right side of the column heading till the width you want. The other way of doing it is to select the Format from the home tab, and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this, the cell size will get formatted.

What Is The Pmt Function In Excel

The Excel PMT function is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods and the loan amount.

“PMT” stands for “payment”, hence the function’s name.

For example, if you are applying for a two-year car loan with an annual interest rate of 7% and the loan amount of $30,000, a PMT formula can tell you what your monthly payments will be.

For the PMT function to work correctly in your worksheets, please keep in mind these facts:

• To be in line with the general cash flow model, the payment amount is output as a negative number because it’s a cash outflow.
• The value returned by the PMT function includes principal and interest but does not include any fees, taxes, or reserve payments that may be associated with a loan.
• A PMT formula in Excel can compute a loan payment for different payment frequencies such as weekly, monthly, quarterly, or annually. This example shows how to do it correctly.

The PMT function is available in Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.

You May Like: How To Transfer Car Loan To Another Person

How To Use The Pv Function In Excel

To understand the uses of the PV function, let us consider a few examples:

Example 1

For this example, we have an annuity that pays periodic payments of $100.00 with a 5.5% annual interest rate. This annuity makes payments on a monthly basis and will do so for 5 years. The setup and formula for the PV function would be as shown below:

Using the PV function, we calculate that the fair present value, if you were to purchase this annuity today, would be $5,235.28.

Example 2

Alternatively, we have an annuity that makes periodic payments of $5000.00 every quarter for 5 years with a 10% interest rate. Payments are made at period beginning. The setup would be as follows:

Using this function, we calculate that the fair present value, if were to purchase this annuity today, would be $79,894.46.

How To Calculate How Much You Can Borrow Using Excel

• How to Make a Spreadsheet to Track a Loan

How much you can borrow is often determined by the bank based on internal qualifiers, such as credit score, debt-to-income ratio, interest rate and the type of loan you need. These qualifiers will vary between banks and may not be publicized. However, the qualifiers that have the biggest impact is how much you can afford and the interest rate. If you have assessed your finances and determined how much you can afford to pay each month, then you can calculate the maximum amount you can borrow, based on that monthly payment and a given interest rate. This complex calculation is greatly simplified by using Microsoft Excel’s loan formulas.

1

Enter the monthly interest rate, in decimal format, in cell A1. Most interest rates are expressed as annual rates, so enter “=Interest/12” and replace “Interest” with the annual interest rate, such as “=0.06/12”.

2

Enter the number of payments in cell A2. Loans may be expressed in months or years. If you know the number of month for the loan, enter that value. If you know the number of years, enter “=Years12″ and replace “Years” with the number of years, such as “=512″.

3

4

Enter “=PV” in cell A4 to calculate the maximum amount of the loan. Because this value expresses a debt, it appears red and parenthesized.

References

Also Check: How To Get Loan Originator License

Ipmt Formula For Different Payment Frequencies

To get the interest portion of a loan payment right, you should always convert the annual interest rate to the corresponding period’s rate and the number of years to the total number of payment periods:

• For the rate argument, divide the annual interest rate by the number of payments per year, assuming the latter is equal to the number of compounding periods per year.
• For the nper argument, multiply the number of years by the number of payments per year.

The following table shows the calculations:

As an example, let’s find the amount of interest you will have to pay on the same loan but in different payment frequencies:

• Annual interest rate: 6%
• Loan amount: $20,000
• Period: 1

The balance after the last payment is to be $0 , and the payments are due at the end of each period .

Weekly:

=IPMT

Monthly:

=IPMT

Quarterly:

=IPMT

Semi-annual:

=IPMT

Looking at the screenshot below, you can notice that the interest amount decreases with each subsequent period. This is because any payment contributes to reducing the loan principal, and this reduces the remaining balance on which interest is calculated.

Also, please notice that the total amount of interest payable on the same loan differs for annual, semi-annual and quarterly installments: