What Is The Formula For Calculating Loan Payments

Can You Afford The Loan

Lenders tend to offer you the largest loan that theyll approve you for by using their standards for an acceptable debt-to-income ratio. However, you dont need to take the full amountand its often a good idea to borrow less than the maximum available.

Before you apply for loans or visit houses, review your income and your typical monthly expenses to determine how much youre comfortable spending on a mortgage payment. Once you know that number, you can start talking to lenders and looking at debt-to-income ratios. If you do it the other way around , you might start shopping for more expensive homes than you can afford, which affects your lifestyle and leaves you vulnerable to surprises.

Its safest to buy less and enjoy some wiggle room each month. Struggling to keep up with payments is stressful and risky, and it prevents you from saving for other goals.

What Is A Fixed

A fixed-rate mortgage is a home loan that has the same interest rate for the life of the loan. This means your monthly principal and interest payment will stay the same. The proportion of how much of your payment goes toward interest and principal will change each month due to amortization. Each month, a little more of your payment goes toward principal and a little less goes toward interest.

How Personal Loan Payments Work

In addition to your loans principal amount, youre on the hook for interest and any fees associated with a personal loan. Heres what you can expect to repay when you take out a personal loan:

• Principal: The amount you borrow that gets deposited into your account.
• Interest: What the lender charges you to lend you money. Your annual percentage rate includes your interest rate and costs that are paid up front, such as origination fees. For most personal loans, you have a fixed interest rate, which doesnt change over the life of the loan. Interest rates are determined by market forces, as well as your credit score and history the higher your credit score, the lower your interest rate.
• Fees: Additional costs of taking out a loan, such as origination fees, late fees, insufficient funds fees and more.

Your monthly payment is based on how much you owe and your repayment term. A $5,000 loan paid over five years will have lower monthly payments than a $5,000 loan paid over three years, since the payments are spread out over a longer period. However, keep in mind that your interest rate and any associated fees are also added into each loan payment.

You May Like: How Long Does An Sba Loan Take To Get Approved

Why Use A Simple Loan Calculator

A loan calculator isn’t an agreement or binding tool. In fact, usually when you use a loan calculator, it’s not associated with a specific loan at all. That means that the information you gather using a loan calculator is only an estimate.

However, that estimate can be helpful when you’re planning on applying for a loan. A calculator lets you look at what the monthly payment might be given various scenarios.

That helps you understand if you can afford a loanyou can see if the monthly payment amount would even fit in your personal budget.

You can also test different interest rate and loan term combinations to understand what might work for you. For example, you might find out using the calculator that you can’t afford a loan with a 12-month term. But maybe you could afford the same amount at the same interest rate over a longer period of timesince that would mean lower monthly payments.

Determining The Length Of A Loan

We will now see how to determine the length of a loan when you know the annual rate, the principal borrowed, and the monthly payment that is to be repaid. In other words, how long will we need to repay a $120,000 mortgage with a rate of 3.10% and a monthly payment of $1,100?

The formula we will use is NPER, as shown in the screenshot above, and it is written as follows:

=NPER

The first three arguments are the annual rate of the loan, the monthly payment needed to repay the loan, and the principal borrowed. The last two arguments are optional, the residual value defaults to zero. The term argument payable in advance or at the end is also optional.

=NPER^-1 -B4 B3) = NPER^-1 -1100 120000)

Don’t Miss: What Credit Score Is Needed For Usaa Auto Loan

How Do You Calculate A Loan Payment

The first step to calculating your monthly payment actually involves no math at all – it’s identifying your loan type, which will determine your loan payment schedule. Are you taking out an interest-only loan or an amortized loan? Once you know, you’ll then be able to figure out the types of loan payment calculations you’ll need to make.

With interest-only loan options, you only pay interest for the first few years, and nothing on the principal balance – the loan itself. While this does mean a smaller monthly payment, eventually you’ll be required to pay off the full loan in a lump sum or with a higher monthly payment. Most people choose these types of loan options for their mortgage to buy a more expensive property, have more cash flexibility, and to keep overall costs low if finances are tight.

The other kind of loan is an amortized loan. These loan options include both the interest and principal balance over a set length of time . In other words, an amortized loan term requires the borrower to make scheduled, periodic payments that are applied to both the principal and the interest. Any extra payments made on this loan will go toward the principal balance. Good examples of an amortized loan are an auto loan, a personal loan, a student loan, and a traditional fixed-rate mortgage.

Calculate The Number Of Payments

The most common term for a fixed-rate mortgage is 30 years or 15 years. To get the number of monthly payments you’re expected to make, multiply the number of years by 12 .

A 30-year mortgage would require 360 monthly payments, while a 15-year mortgage would require exactly half that number of monthly payments, or 180. Again, you only need these more specific figures if you’re plugging the numbers into the formula an online calculator will do the math itself once you select your loan type from the list of options.

Read Also: Excel Loan Payment

Calculate The Annual Interest Rate

We have seen how to set up the calculation of a monthly payment for a mortgage. But we may want to set a maximum monthly payment that we can afford that also displays the number of years over which we would have to repay the loan. For that reason, we would like to know the corresponding annual interest rate.

As shown in the screenshot above, we first calculate the period rate , and then the annual rate. The formula used will be RATE, as shown in the screenshot above. It is written as follows:

=RATE

The first three arguments are the length of the loan , the monthly payment to repay the loan, and the principal borrowed. The last three arguments are optional, and the residual value defaults to zero the term argument for managing the maturity in advance or at the end is also optional. Finally, the estimate argument is optional but can give an initial estimate of the rate.

The Excel formula used to calculate the lending rate is:

=RATE = RATE

Note: the corresponding data in the monthly payment must be given a negative sign. This is why there’s a minus sign before the formula. The rate period is 0.294%.

We use the formula = is 12-1 ^ = ^ 12-1 to obtain the annual rate of our loan, which is 3.58%. In other words, to borrow $120,000 over 13 years to pay $960 monthly, we should negotiate a loan at an annual 3.58% maximum rate.

Using Excel is a great way of keeping track of what you owe and coming up with a schedule for repayment that minimizes any fees that you might end up owing.

Consider Alternatives Before Signing Your Name

There are alternatives to commercial personal loans that are worth considering before taking on this kind of debt. If possible, borrow money from a friend or relative who is willing to issue a short-term loan at zero or low interest. Alternatively, if you have high-interest credit card debt that you want to eliminate you may be able to perform a.

What’s a balance transfer, you ask? Some on new purchases and on your old, transferred balance for a year. If you can get one of these deals and manage to pay off your balance while you have the introductory interest rate you may be better off opting for a balance transfer than for a personal loan. It’s important to pay off your balance before your APR jumps from the introductory rate to a new, higher rate.

Loan calculators can help you figure out whether a personal loan is the best fit for your needs. For example, a calculator can help you figure out whether you’re better off with a lower-interest rate over a lengthy term or a higher interest rate over a shorter term. You should be able to see your monthly payments with different loan interest rates, amounts and terms. Then, you can decide on a monthly payment size that fits into your budget.

Read Also: How To Transfer Car Loan To Someone Else

Calculate The Monthly Payment

First, here’s how to calculate the monthly payment for a mortgage. Using the annual interest rate, the principal, and the duration, we can determine the amount to be repaid monthly.

The formula, as shown in the screenshot above, is written as follows:

=-PMT

The minus sign in front of PMT is necessary as the formula returns a negative number. The first three arguments are the rate of the loan, the length of the loan , and the principal borrowed. The last two arguments are optional, the residual value defaults to zero payable in advance or at the end is also optional.

The Excel formula used to calculate the monthly payment of the loan is:

= PMT^-1 B4*12 B3)=PMT^-1 10*12 120000)

Explanation: For the rate, we use the monthly rate , then we calculate the number of periods and, finally, we indicate the principal borrowed. Our monthly payment will be $1,161.88 over 10 years.

What Is The Debt

The debt-service coverage ratio applies to corporate, government, and personal finance. In the context of corporate finance, the debt-service coverage ratio is a measurement of a firm’s available cash flow to pay current debt obligations. The DSCR shows investors whether a company has enough income to pay its debts.

Read Also: Does Va Loan Work For Manufactured Homes

L1 Assignment: Calculate Loan Payments

Create a properly formatted spreadsheet that calculates the payments on a business loan.

Many businesses need to take out loans to cover startup costs. This exercise allows you to create a loan payment calculator and perform a sensitivity analysisAn analysis of how the calculation results vary with changes in the initial assumptions. on the terms of the loan.

Setup

Start Excel and properly title your spreadsheet. Because there are so few numbers the assumptions area and the calculations are combined.

Content and Style

• Name each number in the As-Is scenario. Use those names in calculations.
• Follow best practice design techniques.
• Include a copyright symbol with your name at the bottom.
• Perform a sensitivity analysis to see how payments change as a function of interest rates and loan amount.

Deliverables

Electronic submission: Submit the workbook electronically.

Paper submission:

• The worksheet grid lines will not appear on the printout.
• Print out both the results and formulas. The formulas printout shows the formulas in each column. Reveal the formulas by typing CTRL+ ~. Adjust the column widths to closely crop the formulas by dragging the separator between each column in the gray header area.
• Both printouts should use landscape orientation, which may be accessed from Page Layout > Page Setup > Orientation > Landscape. Each printout should fit on one page. Choose Page Layout > Scale to Fit > Height: 1 page Width: 1 page.

Calculate The Monthly Interest Rate

The interest rate is essentially the fee a bank charges you to borrow money, expressed as a percentage. Typically, a buyer with a high , high down payment, and low debt-to-income ratio will secure a lower interest rate the risk of loaning that person money is lower than it would be for someone with a less stable financial situation.

Lenders provide an annual interest rate for mortgages. If you want to do the monthly mortgage payment calculation by hand, you’ll need the monthly interest rate just divide the annual interest rate by 12 . For example, if the annual interest rate is 4%, the monthly interest rate would be 0.33% .

Also Check: How To Calculate Amortization Schedule For Car Loan

New Topic Same Formula

Mathematical formulas sometimes overlap, applying to more than one application. All the exercises and examples in this section use the same formula and techniques that youve already seen.

One great thing about loans is that they use exactly the same formula as a payout annuity. To see why, imagine that you had $10,000 invested at a bank, and started taking out payments while earning interest as part of a payout annuity, and after 5 years your balance was zero. Flip that around, and imagine that you are acting as the bank, and a car lender is acting as you. The car lender invests $10,000 in you. Since youre acting as the bank, you pay interest. The car lender takes payments until the balance is zero.

Loan Computation In Excel

It is also possible to calculate the principal and interest repayment for several periods, such as the first 12 months or the first 15 months.

=-CUMPRINC

We find the arguments, rate, length, principal, and term that we already saw in the first part with the formula PMT. But here, we need the “start_date” and “end_date” arguments also. The “start_date” indicates the beginning of the period to be analyzed, and the “end_date” indicates the end of the period to be analyzed.

Here’s an example:

=-CUMPRINC^-1 B4*12 B3 1 12 0)

The result is shown in the screenshot “Cumul 1st year,” so the analyzed periods range from one to 12 of the first period to the twelfth . Over a year, we would pay $10,419.55 in principal and $ 3,522.99 in interest.

Also Check: Va Manufactured Home Loan Requirements

Finding Other Loan Numbers

Equation 1 showed how to find the currentbalance, or payoff amount, on a loan. But more likely you want to knowwhat the payment amount will be for acertain number of payments, or how many paymentsof a certain amount will be required. The mostobvious times when youd ask these questions are when youre buying acar on a 3-year or 4-year loan , or a house on a 30-yearloan.

The next two sections show how you can find both the number ofpayments N and the payment amount P by doing some algebra onequation 1. When the loan is paid off, the remainingbalance B_n = 0, so set B_N to 0 in equation 1and solve for either P or N.

Why You Can Trust Bankrate

Founded in 1976, Bankrate has a long track record of helping people make smart financial choices. Weve maintained this reputation for over four decades by demystifying the financial decision-making process and giving people confidence in which actions to take next.

Bankrate follows a strict editorial policy, so you can trust that were putting your interests first. All of our content is authored by highly qualified professionals and edited by subject matter experts, who ensure everything we publish is objective, accurate and trustworthy.

Our loans reporters and editors focus on the points consumers care about most the different types of lending options, the best rates, the best lenders, how to pay off debt and more so you can feel confident when investing your money.

You May Like: What To Do If Lender Rejects Your Loan Application

Using Excel Formulas To Figure Out Payments And Savings

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

=PMT

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

Using the function PMT

Using the function PMT

=PMT

How To Calculate Amortizing Loan Payments

Use the following formula for how to calculate loan payments on an amortizing loan.

A = P

Where:

A is the periodic payment amount

P is the principal or the original loan balance, less any down-payments

i is the periodic interest rate. To calculate i, divide the nominal annual interest rate as a percentage by 100. Divide that figure by the number of payment periods in a year.

n is the total number of periods. To calculate n, multiply the loan duration in years by the number of payment periods in a year.

Let’s take a look at an example of an amortizing loan repaid on a monthly schedule. Say you want to take out a 5-year, $25,000 loan at a nominal annual interest rate of 6 percent.

P is $25,000

i is .005

n is 60 months

Your monthly loan payment would be approximately $483.

Here’s the math:

A = 25,000

A = 25,000

Recommended Reading: What Is An Rv Loan