Plan Loan Payments with Excel Formulas

Did you know that you can use Excel to calculate your loan repayments? This article will guide you through all the steps needed to do this. 

With Excel you can better understand your mortgage in three simple steps. The first step is to determine the monthly payment. The second is to discover the interest, and the third is to find the loan schedule. For this you can create a table in Excel with the following data: The lowest rates; the loan calculation for the duration; cancellation of a loan, as well as repayment and calculation for monthly rent.

Loan calculation for monthly rent

First, let’s see how we can perform the calculation of a monthly payment for a mortgage. In other words, using the annual Iinterest rate, the principal and the duration, we can determine the amount that must be repaid monthly.

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

= – PMT (speed; length; current value; [future_value]; [type])

The minus sign before PMT is necessary because the formula returns a negative number. The first three arguments are the percentage of the loan, the length of the loan (number of periods) and the borrowed capital. The last two arguments are optional, the residual value is 0 by default, to be paid in advance (for 1) or at the end (for 0), is also optional.

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

= – PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Explanation: For the rate we use the period of the rate, which is the monthly rate, then we calculate the number of periods (months here 120 for 10 years multiplied by 12 months) and finally we indicate that the principal sum has been borrowed. Our monthly payment is $ 1, 161, 88 over 10 years.

Mortgage calculation interest rates

We have seen how the calculation of a monthly payment for a mortgage can be set up. But we may want to set a maximum monthly payment that we can afford and that also indicates the number of years that we should pay back. For that reason we would like to know the corresponding annual interest.

 

Calculate the interest rate for a loan

As shown in the screenshot above, we first calculate the period rate (monthly in our case) and then the annual rate. The formula used is RATE, as shown in the screenshot above. It is written as follows:

= RATE (Nper; pmt; present_value; [future_value]; [type])

The first three arguments are the length of the loan (number of periods) and the monthly payment to repay the principal loan. The last three arguments are optional and the residual value is set to 0 by default, the term argument for managing the term beforehand (for 1) or at the end (for 0) is also optional, and finally the estimation argument is optional, but can give a first estimate of the rate.

The Excel formula used to calculate the lending ratio is:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Note: the corresponding data in the monthly payment must receive a negative sign. This is why a minus sign is before the formula. Our interest period is 0.294%.

We use the formula = (1 + B5) is 12-1 ^ = (1 + 0.294%) ^ 12-1 to increase the annual rate of our loan to 3. 58%. In other words, to borrow $ 120,000 over 13 years to pay $ 960 per month, we have to negotiate a loan at a maximum interest rate of up to 3. 58%.

Mortgage compensation for the length of a loan

We will now look at how you can get the length of a loan if you know how much interest you have to pay annually, what is the principal amount that has been borrowed and how much the loan must be repaid each month. In other words, how long will we have to repay a $ 120,000 mortgage with a rate of 3.10% and a monthly payment of $ 1.00?

Number of repayments for a loan

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

= NPER (rate; pmt; present value; [future value]; [type])

The first three arguments are the annual percentage of the loan, the monthly payment required to repay the loan and the principal sum borrowed. The last two arguments are optional, the residual value is set to 0 by default, the term argument paid in advance (for 1) or at the end (for 0) is also optional.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Note: the corresponding data in the monthly payment must be given a negative sign. This is why we have a minus sign before the formula. The reimbursement duration is 127.97 periods (months in our case).

We use the formula = B5 / 12 = 127. 97/12 for the number of years that the loan repayment has been completed. In other words, to borrow $ 120,000, with an annual rate of 3.10% and to pay $ 1, 100 per month, we have to pay back the due dates for 128 months or 10 years and 8 months.

Cancel the loan

A loan payment consists of two things, the principal and interest. The interest is calculated for each period, for example the monthly repayments over 10 years, gives us 120 periods.

The screenshot above shows the breakdown of a loan (a total period equal to 120) using the PPMT and IPMT formulas. The arguments of the two formulas are the same and are subdivided as follows:

= – PPMT (speed; number_period; length; command; [remaining]; [terme])

= – INTPER (speed; number_period; length; principal; [residual]; [terme])

The arguments are the same as those for the PMT formula in the first part, except for num_period that has been added to show the period when the loan can be canceled, which means the most important and the interest for it. Let’s take an example:

= – PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)

= – INTPER ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = INTPER ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)

The result is the result that is displayed in the screenshot “Loan analysis” during the analyzed period, which is “1”, so the first period or the first month. For this we pay $ 1161.88, subdivided into $ 856, 20 principal and $ 305.68 interest.

Excel Loan Computation

Now it is also possible to calculate the principal and the interest payment for different periods, such as the first 12 months or the first 15 months.

= – CUMPRINC (speed; length; principal; start date; end date; type)

= – CUMIPMT (speed; length; principal; start date; end date; type)

We find the arguments, speed, length, principal and term (that are mandatory) that we already saw in the first part with the PMT formula. But here we also need the arguments start_date and end_date. The first indicates the beginning of the period to be analyzed and the second the end. Let’s take an example:

= – CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

= – CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

= – CUMIPMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

= – CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

The result is the one displayed in the “Cumul 1st year” screenshot, so the analyzed periods range from 1 to 12, from the first period (first month) to the twelfth (12th month). In a year we would pay $ 10 419, 55 Principal and $ 3 522. 99 Interest.

Repayment of the loan

With the above formulas we can set our planning period per period, how much we will pay monthly in principal and interest and how much still needs to be paid.

 

Make a loan scheme in Excel

To make a loan scheme, we use different formulas discussed above and we extend this over the number of periods.

In the first period column, simply enter ‘1’ as the first period and then drag the cell down. In our case we need 120 periods since a 10-year payment of the loan multiplied by 12 months = 120.

The second column is the monthly amount that we have to pay every month, which is constant over the entire loan schedule. To calculate it, place the following formula in the cell of our first period:

= – PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

The third column is the principal sum that will be repaid monthly. For example, for the 40th period we will refund $ 945. 51 in principle on our monthly total amount of $ 1, 161. 88. To collect the principal, we use the following formula:

= – PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

The fourth column is the interest, for which we calculate that the principal is repaid on our monthly amount to discover how much interest must be paid, using the formula:

= – INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = – INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

The fifth column contains the amount that still has to be paid. For example, after the 40th payment, we have to pay $ 83,994.69 for $ 120,000. The formula is as follows:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

The formula uses a combination of principal in a coming period with the cell with the principal borrowed. This period begins to change when we copy and drag the cell. The screenshot below shows that our loan was repaid at the end of 120 periods.

 

 

Companies that offer zero rate loans present them as propositions in which the borrower cannot lose. An important purchase that could otherwise cost a large cash expense can be spread 12 or 24 months free of charge, creating a much more pleasant cash flow situation. There are, however, several pitfalls with these loans, including a greater chance of making an impulse purchase, the tendency to have surpluses and large fees for exceeding the loan term.

Zero rate loan Basic information

A loan with an interest subsidy is exactly what it sounds like: a loan where only the principal sum has to be repaid, provided that the borrower meets the conditions of the agreement. These terms and conditions usually include a strict time limit by which the full balance must be repaid. Exceeding this deadline not only involves a hefty fine, but in many cases the lender solves the zero-percent clause and applies the interest to the loan retroactively.

Zero-interest loans are common at car dealerships and at stores that sell electronics and household appliances. The loans are offered through external lenders, not the stores themselves, and to be eligible, an excellent FICO score, such as 720 or higher, is usually required. In most cases, the zero-interest bonus comes only for shorter loans, such as 24 months or less at a store or 36 months or less at a car dealer.

Loans with zero interest and buying incentives

Often, car dealers often flood local radio waves with advertisements offering after-sales loans. Financing a new car at 0% interest rate instead of a higher rate is a smart decision, but only if the buyer actually needs a new car and is in a good financial position to purchase one. The problem is that these ads can be so tempting that buyers who don’t have to consider a new car descend on the property and make impulse purchases.

Promotions for zero-interest loans attract many more buyers than those who are actually eligible for such loans. The buyers who only show up to be told that their credit is not eligible for the 0% rate, still receive slick sales pitch that are designed to lead them to loans that are interesting. Even if the conditions are not favorable, it can be difficult to say no, especially if the buyer already has a mental picture of driving off the plot in his new car or installing his new flat screen TV.

Zero rate credits and too much spent

Loans without interest offer a handy excuse to spend more money on a new car or to make a luxury purchase that the buyer might not otherwise make. Instead of buying a fine $ 20,000 car, the buyer exchanges a $ 25,000 vehicle, rationalizing that he can still pay at least $ 5,000 in interest if he doesn’t receive such excellent loan conditions. as with an interest-bearing loan, and he has a vehicle with only marginally better usability.

Sellers use zero-interest loan promotions by using them to force customers into more expensive purchases that pay higher commissions. In addition, some dealers use zero interest deals as leverage during price negotiations. Since the buyer receives so much money for financing, the chance is less that the seller will have the purchase price less often. Buyers must ensure that they do not use interest savings to justify overpaying.

Zero rate loans and fees

 

Loans without interest can seem like a dream, but they can quickly become a nightmare for borrowers who don’t read the terms and fully understand them. Lenders quickly cancel post-payment provisions for non-compliance, sometimes for a violation that is as small as a one-off payment delay. This applies to revolving debt, such as 0% annual percent rate (APR) credit cards and to installment debt, such as a car loan. Borrowers who make use of such deals must understand the deadlines, as well as any fees or fines for late payment or exceeding the specified loan term.

Collateral is something that helps to secure a loan. When you borrow money, you agree (somewhere in the fine print) that your lender can take something and sell it to get your money back if you do not repay the loan. The guarantee makes it possible to get great loans and improves your chances of approval if you are having trouble getting a loan. When you promise collateral, the lender takes less risk, which means you have a better chance of getting a good rate. but is it a good or bad business /

How does a loan with property or car warranty work?

The collateral is usually necessary when the lender wants some assurance that he will not lose all the money. If you pledge an asset as collateral, the lender has the right to act (assuming you stop making loan payments): they take over the collateral, sell, and use the proceeds to pay off the loan.

This type and loan contrasts with an unsecured loan where all that a lender can do is reduce your credit score or take legal action against you.

Lenders prefer, above all, to recover the money. They do not want to take legal action against you, so they try to use collateral. They do not even want to deal with their warranties, but this is the easiest form of protection.

Types of credit with a guarantee

Any asset that your lender accepts as collateral can serve as collateral. In general, lenders prefer assets that are easy to evaluate and turn into cash. For example, money in a savings or investment account is great for collateral: lenders know how much it is worth and is easy to collect. Some common forms of warranty include:

Automobiles

Real estate (including equity in your home)

Savings Accounts

Machinery and equipment

Investments

Insurance policies

Value and collectible items

Future customer payments (receivables)

Even if you are making a commercial loan, you can pledge your personal assets (such as your family home) as part of a personal guarantee. Please note that retirement accounts generally can not be used as collateral.

Valuing your assets to make a secured loan

In general, the lender will offer you less than the value of your promised good. Some assets can have huge discounts. For example, a lender may recognize 50% of your investment portfolio for a secured loan. That way, they increase your chances of getting all your money back if investments lose value.

When applying for a loan, lenders often cite an acceptable loan in relation to the value. For example, if you borrow with a property as collateral, lenders can afford a loan of up to 80%. If your home is worth $ 100,000, you can borrow up to $ 80,000. It is worth remembering that this type of guarantee may not apply if the property is your only property.

If your pledged assets lose value for any reason, you may have to pledge additional assets to keep a secured loan. In the same way, you are responsible for the total value of your loan, even if the bank takes your assets and sells them for less than the amount you owe. The bank may file a lawsuit against you to collect the unpaid amount.

Types of secured loans

You can find secured loans in various places. They are commonly used for commercial and personal loans. Many new companies, because they do not have a long history of profitable operations, are required to pledge collateral including personal items owned by business owners.

A home financed purchase is a type of secured loan: the home secures the loan, and the lender can close the mortgage if you do not pay. Even if you are lending for fixed amounts, lenders want to use your investment property as collateral. When hiring real estate loans, the type of loan available will depend on the age of the home, the foundation system and other factors. The same goes for cars and vehicles financed.

And loan with the dirty name?

There are also some secured loans for people with bad credit and bad name. These loans are often expensive and should only be used as a last resort. Be careful with these loans: if you fail to pay, the lender can take your asset and sell it. That way, you can end up with even more debt and without your assets. Without financial planning for your debts, it is not recommended to make a secured loan.

Unsecured Loans

If you prefer not to pledge collateral, you will have to find a lender willing to transfer money based on your credit. Some of the options include:

Unsecured loans like personal loans and credit cards

Online loans are usually unsecured loans with good rates

Get a guarantor to apply for the loan with you

In some cases, such as buying a home, borrowing without using anything as collateral is probably not possible (unless you have a significant equity). In other situations it may be an option to make without collateral but you will have fewer options and you have to pay a higher rate for the loan.

 

 

The secured loan is an option that can be used by you when you need credit.

The secured loan guarantees better payment conditions, lower interest rates and better credit release.

But with so many advantages will it really be the best option when we need money?

Loan with collateral is a good one?

You probably already have heard of secured loan or secured personal loan.

The secured loan is almost always exemplified as the house’s notorious mortgage. When we speak of mortgage of the house we are talking about the loan with property guarantee.

That is, the applicant applied for a loan and used as collateral for payment of his property. In real life we ​​have the refinancing of vehicles or real estate.

The loan with guarantee is not very required in Brazil precisely because of this, the good conditions offered in this type of credit are linked to the submission of a guarantee with the financial institution that the amount requested will be fully paid.

For fear of losing the good used as collateral, this type of credit is usually avoided

In the secured loan it is possible to submit both your property and your car for evaluation of the financial institution to which you will apply for the loan.

How Guarantee Loans Work

The secured loan basically works as follows, your claimant uses a property previously yours (car or property) as collateral when applying for credit with a particular financial institution.

The financial institution evaluates the asset and determines its market value. The market value determines how much you can request. For example, some institutions release up to 50% of the market value of the asset used as collateral, another 70% or 90%.

You have a car valued at 100 thousand reais, this being your market value. The market value determined after the evaluation made taking into account among other things the year, the model, the brand and the state of conservation.

If you are seeking credit at a financial institution that offers up to 60% of the market value you may require up to 60 thousand reais.

Here the secured personal loan is known as vehicle refinancing once your claimant starts paying back a part of the vehicle in exchange for cash on hand.

Secured loan: what are the risks?

As we commented the loan with guarantee ends up not being much asked for the risk that its applicant has to lose the good used as collateral. But if well thought out and requested in a moment of real need secured loan can be an option for you to achieve dreams before unattainable.

We can not always control when we need a loan, but it is crucial that we are aware that we can repay the debt once we apply for a loan to repay a debt knowing that we can not repay the loan is to exchange six by half a dozen and in this case the good guaranteed will be at risk.

It is precisely for this reason, by using a good as collateral to repay that secured loan is so attractive.

The interest rates come out lower since the bank has a guarantee that you will repay the loan. And in case of default you can lose the guarantee used.

For the bank the use of a guarantee guarantees that the amount borrowed will not be lost which facilitates the applicant to obtain better payment terms and good interest rates.

Bank 

The Bank of Brasília has as its main mission to act for the full economic growth and sustainable development of the Federal District (DF)

The Bank of Brasília or BRB is a public bank created in 1966 and today has as one of the largest shareholders the government of the Federal District.

A bank that, as the institution itself defines itself, has evolved over time, having as main objective of its action the economic and sustainable development of the Federal District and regions of direct influence with the Federal District.

Banco de Brasília SA was created in 1964 through Federal Law 4,545, but was only authorized to carry on its activities in 1966.

It is considered a bank to operate by the modality of mixed economic society having the largest shareholder the Government of the Federal District with practically 97% of the shares of the Bank of Brasilia.

Services Offered

Although not a well-known institution in other regions of the country, Banco de Brasília SA is a complete institution.

Offering all the services that the client expects from a financial institution.

From Credit Consignment BRB, personal credit, salary anticipation, 13th, vacation anticipation, real estate credit, rural credit, vehicle financing and vehicle refinancing.

Vehicle Refinancing

With the Financial BRB it is possible to obtain in the release of credit up to 70% of the market value of the vehicle determined by the FIPE Table.

The FIPE table is from the Foundation Institute of Economic Research and is published monthly bringing the reference values ​​and average values ​​that should be worked for all cars within the national territory.

So that you can breathe a little relieved using your vehicle as a guarantee for release of credit will require that it has a maximum of ten years of manufacturing, that is, I have the manufacturing year dated 2008 (for loans requested later this year).

It is also necessary that your vehicle is not alienated. The alienation of the vehicle is a process where the automobile is given as collateral in a financing. While the applicant is paying the installments the vehicle is alienated from the bank, and the owner may lose the good in case of non payment of the remaining installments of his financing.

Just as the car can not be alienated, the vehicle must also be removed, that is, there are no installments to be paid from its financing.

To apply for the BRB loan, it is sufficient that the applicant for the loan go to a BRB or Financial BRB agency and look for the responsible manager to clear all their doubts and apply for their loan.

Banco de Brasília is recognized as a strong institution in the Center-West region of the country, with the main values ​​being the commitment to its results, the valuation of people and the ethics and transparency of action.

 

First of all, it is essential to know what a loan works. It is in fact a financing generally granted by any financial institution. The latter is indeed a loan earmarked for the purchase of a property or the repair of some or all of a property.

It should also be noted that a work loan does not distinguish between a principal residence and a second home. The fact is that it can always be allocated regardless of the nature of the housing concerned. Its principle?

Make available to the borrower a specific amount of money that he can use to finance various works. Of course, it is necessary that the lending organization agrees in the use of said sum.

The only requirement is that the loan must be used for the property specified in the loan application and not as one wants. This means that the nature of the loan must therefore be mentioned in the application.

That said, the completion of the repair work can be done by a professional company or the owner himself. This does not change the different conditions required to obtain the grant of a work loan.

A minimum sum to be refunded in advance

Early repayment of a work loan is possible in some cases. However, it should be noted that this possibility can be carried out partially or completely according to the present cases. Such a procedure is possible for example during a sudden and important return.

But while this type of repayment is not often beneficial to the borrower, many choose it in order to alleviate at least a portion of the debt.

Conditions to be present in the loan agreement must, for example, be taken into account as the minimum amount to be paid, for example. Feasible at any time, the amount to be refunded when a total repayment in part of a work loan must be decided. This option is especially valid in the case where the repayment is only partially done.

The sum is not fixed in advance although it is at least equal to at least 10% of the total amount of the loan. Of course, this point will also not be accepted if this sum is equal to the balance of the credit in question. Namely that the amount of the prepayment allowance is capped.

Early repayment compensation to be provided

The early repayment of a work loan is possible at any date of maturity, it is known that the borrower must prepare for an obligatory compensation. This compensation must be provided for in the loan agreement and is only required in a loan repayment in advance.

This fee is paid to the lender who is none other than the institution that granted the loan. However, it is necessary that this indemnity must not reach the 6 months of interest on the average capital of the loan and be of a reasonable rate.

It is also necessary that it must not go beyond the 3% of the capital remaining due before the prepayment. The fact is that it can also be raised because of the compensatory interest. There are, however, some exceptions that may exempt the borrower from this so-called indemnity.

This is notably the case of a work loan obtained in the late 1990s that is free from compensation. However, the persons concerned should for example sell their goods for a change of workplace. This exception would also apply where the borrower in question would be forced to stop working due to dismissal or to die.