What Is the Loan Formula in Excel

April 17, 2022

Definitions vary. For example, here`s how different sources define a term loan: We`ll now look at how you can determine the duration of a loan if you know the annual payout, the principal amount borrowed, and the monthly payment to be repaid. In other words, how long do we have to pay off a $120,000 mortgage with an interest rate of 3.10% and a monthly payment of $1,100? To create a credit plan, we use the different formulas described above and expand them over the number of periods. Loan repayment is the act of repaying money previously borrowed from a lender, usually through a series of regular payments that include principal plus interest. Did you know that you can use Excel software to calculate your loan repayments? In cell C6, the PMT function calculates the monthly payment based on the monthly payment divided by 12 to get the monthly payment, the number of payments (periods) and the loan amount (current value): I assume that the interest rate is set. I will deal with variable rate loans at another time. Excel offers a variety of spreadsheet functions to work with depreciable loans: to create a general formula to calculate the cumulative interest rate, we must first find a way to calculate the sum of an arithmetic series as follows: The minus sign before PMT is necessary because the formula returns a negative number. The first three arguments are the interest rate of the loan, the duration of the loan (number of conditions) and the principal amount borrowed. The last two arguments are optional, the residual value is set to zero by default. payable in advance (for one) or at the end (for zero) is also optional. nper required. The total number of payments for the loan. We use the formula = (1 + B5) is 12-1 ^ = (1 + 0.294%) ^ 12-1 to get the annual payment of our loan, which is 3.58%.

In other words, to borrow $120,000 over 13 years to pay $960 per month, we would have to negotiate a loan at a maximum annual rate of 3.58%. With Excel, you can better understand your mortgage in three simple steps. The first step determines the monthly payment. The second step calculates the interest rate and the third step determines the loan plan. We will use the formula = B5 / 12 = 127.97 / 12 for the number of years to complete the repayment of the loan. In other words, to borrow $120,000 at an annual rate of 3.10% and pay $1,100 a month, we would have to repay the terms for 128 months or 10 years and eight months. To calculate the amount of the repurchased capital, we use the following formula: we find the arguments, the rate, the length, the principle and the duration (which are mandatory) that we have already seen in the first part with the PMT formula. But here we also need the “start_date” and “end_date” arguments.

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. We have seen how to set up the calculation of a monthly payment for a mortgage loan. But we may want to set a maximum monthly payment that we can afford, which also indicates how many years we would have to repay the loan. For this reason, we would like to know the corresponding annual interest rate. The formula, as shown in the screenshot above, is written as follows: In the column of the first point, type “1” as the first point, and then drag the cell down. In our case, we need 120 periods, because a loan payment of 10 years multiplied by 12 months equals 120. Depending on the frequency you select, cell E5 uses a VLOOKUP formula to calculate a number of payments per year. Until the final formula above, the calculations of the term loan were simple.

Let`s conclude this article by examining how this final formula was derived. The remaining loan balance is equal to the initial loan amount minus the amount of accumulated principal paid. Term loans can have a variety of repayment periods, interest rates, amortization methods, etc. Learn how to calculate amortization plans for the two most common types of amortization loans. The PMT feature allows you to refund a payment amount based on the credit information. In this example: Fv Optional. The future value or cash balance you want to reach after the last payment. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Make sure you are consistent with the units you use to specify the rate and NPR. If you make monthly payments for a four-year loan at an annual interest rate of 12%, use 12%/12 for the interest rate and 4*12 for the nper. If you make annual payments for the same loan, use 12% for the payout and 4% for the NPER. Use Excel Formula Coach to calculate a monthly loan payment. At the same time, you will learn how to use the PMT function in a formula. As explained in Creating Even and Straight-Line Amortization Tables in Excel, Excel provides the ISPMT function to return the amount of the interest payment for any period of a straight-line loan. I`m not aware of any other features that Excel provides specifically for four linear loans. “How do I calculate the cumulative amount of principal and interest on term loans? I searched the web for a function that will perform this task without success. “- Lake M. Managing personal finances can be challenging, especially if you`re 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 for you to determine how long it will take you to reach your goals. Use the following features: Note: The corresponding data in the monthly payment must be signed negatively.

For this reason, there is a minus sign before the formula. The interest period is 0.294%. My visitor asked me about cumulative principal and interest payments. In this article, I will explain how to calculate these amounts for both types of loans. And in How to Create Equal Payment and Straight-Line Depreciation Tables in Excel, I explain how to create the depreciation tables that I`m going to show you and how to calculate interest for any period of time. Tip To determine the total amount paid over the life of the loan, multiply the returned LMP value by nper. Copy the sample data into the following table and paste them into cell A1 of a new Excel worksheet. To have the formulas display the results, select them, press F2, and then press ENTER. If necessary, you can adjust the width of the columns to display all the data.

The amount of interest payment for a given period corresponds to the balance of the loan for the previous period, multiplied by the periodic interest rate. The loan balance for the previous period is equal to the amount of the initial loan multiplied by the current period minus 1 multiplied by the periodic payment of the principal. Therefore, we must use spreadsheet formulas to calculate the most important information about these loans: If you were to set up a repayment schedule in Excel, your loan would look like this: Monthly payment for a loan with conditions that are given as arguments in A2: A4, except for payments due at the beginning of the period. It would take 17 months and a few days to repay the loan. If you work with periodic cash flows and want to derive a general formula on those cash flows, it`s often helpful to show how each periodic amount is calculated. .