Schedule Loan Repayments With Excel Formulas

Loan Repayment Loan repayment refers to the process of returning money borrowed from a lender. This is usually done through regular payments that include both the principal amount borrowed and the interest. Interestingly, you can use the software program Excel to easily calculate your loan repayments.

In this article, we will provide you with a step-by-step guide to help you set up loan calculations using Excel. By following these instructions, you can efficiently manage your loan repayments.

Schedule Loan Repayments With Excel Formulas

Understanding Your Mortgage

With the help of Excel, you can easily gain insights into your mortgage by following three simple steps. Firstly, you can determine your monthly payment. Next, you can calculate the interest rate, and finally, you can establish the loan schedule.

By building a table in Excel, you can access essehttps://www.essentialinformation.org/ntial information, including the interest rate, loan calculations for the entire loan duration, loan breakdown, amortization details, and the monthly payment amount. Excel’s table will provide you with a comprehensive overview of your mortgage, making it easier to manage and understand your financial commitments.

Calculate the Monthly Payment

To calculate your monthly mortgage payment, you need three important pieces of information: the annual interest rate, the amount of money you borrowed (the principal), and the length of time you have to repay the loan (the duration). By using these factors, you can figure out the fixed amount you’ll need to pay back each month.

The formula shown in the screenshot above helps calculate the monthly payment for a loan, like a mortgage. It uses certain inputs to determine the payment amount. Let’s break down the formula step by step.

The formula starts with “=PMT,” and the minus sign before it is necessary because the result will be a negative number (representing outgoing payments).

The first three arguments required for the formula are:

1. “rate”: This is the interest rate for the loan, expressed as a monthly rate.
2. “length”: It refers to the number of periods (months) over which the loan will be repaid.
3. “present_value” (or principal): This is the initial amount borrowed.

The last two arguments are optional:

1. “future_value”: This represents the loan’s residual value, but it is not used in this specific calculation and is set to zero in this case.
2. “type”: This argument is optional as well and specifies whether the payment is due at the beginning (1) or end (0) of each period. It is not needed in this calculation.

In Excel, you can use this formula to calculate the monthly loan payment. For example, if the interest rate is 3.10% per year, the loan term is 10 years (120 months), and the borrowed principal is $120,000, the formula would be:
= PMT((1+3.10%)^(1/12)-1; 10*12; 120000)

By evaluating this formula, you will find that the monthly payment for this loan will be $1,161.88 over the 10-year period.

Calculate the Annual Interest Rate

We have already learned how to calculate the monthly payment for a mortgage. Now, we might be interested in determining the maximum monthly payment we can afford, along with the number of years required to repay the loan. To find out the corresponding annual interest rate, we can use the RATE function in Excel.

The RATE function is designed to help us with this calculation, and it has the following format: =RATE(Nper; pmt; present_value; [future_value]; [type]; [guess])

Let’s understand the arguments used in the formula:

1. “Nper”: This represents the total number of periods (months) needed to repay the loan.
2. “pmt”: It is the monthly payment we can afford to make.
3. “present_value” (or principal): This is the initial amount borrowed.
4. “future_value” (optional): This represents the loan’s residual value and defaults to zero.
5. “type” (optional): It specifies whether the payment is due at the beginning (1) or end (0) of each period.
6. “guess” (optional): This argument provides an initial estimate of the rate.

To use the RATE function in Excel, we can use the following format: =RATE(12 * total_number_of_years; -monthly_payment_amount; principal_amount)

For instance, if the loan term is 13 years (156 months), the maximum monthly payment we can afford is $960, and the borrowed principal is $120,000, the Excel formula would be: =RATE(12 * 13; -960; 120000)

Upon evaluating this formula, we find that the rate period is 0.294%. To obtain the annual rate of our loan, we use the formula =(1 + rate_period) ^ 12 – 1, which results in an annual rate of 3.58%. This means that to borrow $120,000 over 13 years and make monthly payments of $960, we should aim for a maximum annual interest rate of 3.58% while negotiating the loan.

Determining the Length of a Loan

Let’s understand how to calculate the length of a loan when we know the annual interest rate, the borrowed principal, and the monthly payment needed for repayment. In other words, we want to determine how long it will take to repay a $120,000 mortgage with an interest rate of 3.10% and a monthly payment of $1,100.

To perform this calculation, we’ll use the NPER function in Excel, which is shown in the screenshot above and has the following format:

=NPER(rate; pmt; present_value; [future_value]; [type])

Here’s a breakdown of the arguments used in the formula:

1. “rate”: This represents the annual interest rate of the loan, converted to a monthly rate.
2. “pmt”: It is the fixed monthly payment needed to repay the loan.
3. “present_value” (or principal): This is the initial amount borrowed.

The last two arguments are optional:

1. “future_value”: This represents the loan’s residual value and defaults to zero.
2. “type”: It specifies whether the payment is due at the beginning (1) or end (0) of each period.

To calculate the loan duration in Excel, the formula would be:
=NPER((1+monthly_interest_rate)^(1/12)-1; -monthly_payment_amount; principal_amount)

For our example, where the annual interest rate is 3.10% per year, the borrowed principal is $120,000, and the monthly payment is $1,100, the Excel formula would be:
=NPER((1+3.10%)^(1/12)-1; -1100; 120000)

Upon evaluating this formula, we find that the result is 127.97 months. To convert this into years, we use the formula = result / 12, which gives us 10 years and eight months. This means that to borrow $120,000 with an annual interest rate of 3.10% and make monthly payments of $1,100, it would take approximately 10 years and eight months to repay the loan in full.

Decomposing the Loan

In a loan payment, two components make up the total amount: the principal and the interest. Each period, such as monthly repayments over a 10-year loan, constitutes one period for each month, resulting in a total of 120 periods.

To analyze the loan breakdown, we use the PPMT and IPMT formulas, as shown in the table above.

The arguments for both formulas are quite similar, and they are explained as follows:

=-PPMT(rate; num_period; length; principal; [residual]; [term])

The arguments are similar to what we’ve seen in the PMT formula earlier, with one addition: “num_period.” This new argument specifies the particular period for which we want to determine the loan breakdown into principal and interest, based on the given principal and interest rates.

Let’s consider an example:

=-PPMT((1+monthly_interest_rate)^(1/12)-1; 1; total_number_of_periods; principal_amount)

For instance, if the monthly interest rate is 3.10%, and the loan term is 10 years (120 months), with a borrowed principal of $120,000, the Excel formula would be:
=-PPMT((1+3.10%)^(1/12)-1; 1; 10*12; 120000)

Evaluating this formula for the first period (or the first month), the result shown in the “Loan Decomposition” screenshot indicates that for this period, we will pay $1,161.88 in total. This payment is broken down into $856.20 for the principal and $305.68 for the interest.

Loan Computation in Excel

You can also calculate the repayment for both principal and interest over specific periods, like the first 12 months or the first 15 months, using the CUMPRINC function.

The CUMPRINC function requires the same mandatory arguments as the PMT formula we discussed earlier: rate, length, principal, and term. Additionally, it requires two more arguments: “start_date” and “end_date.” The “start_date” specifies the beginning of the period you want to analyze, and the “end_date” indicates the end of the period to be analyzed.

Here’s an example:

=-CUMPRINC((1+monthly_interest_rate)^(1/12)-1; total_number_of_periods; principal_amount; 1; 12; 0)

For instance, if the monthly interest rate is 3.10%, the loan term is 10 years (120 months), and the borrowed principal is $120,000, the Excel formula would be: =-CUMPRINC((1+3.10%)^(1/12)-1; 10*12; 120000; 1; 12; 0)

Evaluating this formula for the first year (from the first month to the twelfth month), the result displayed in the “Cumul 1st year” screenshot indicates that over this period, we would pay $10,419.55 in principal and $3,522.99 in interest. This means that during the first year, $10,419.55 of the total payment will go towards reducing the principal, while $3,522.99 will cover the interest.

Amortization of the Loan

The prior formulas allow us to create our schedule period by period, to know how much we will pay monthly in principal and interest, and to know how much is left to pay.

Creating a Loan Schedule

To create a loan schedule, we will apply the formulas discussed earlier and extend them over the desired number of periods.

To begin, in the first period column, we enter “1” as the first period and then drag the cell down to populate the subsequent periods. Since we have a 10-year loan with monthly payments, we need 120 periods (10 years multiplied by 12 months).

Next, in the second column, we calculate the fixed monthly payment we need to make throughout the loan schedule. This amount remains the same for every period. To calculate this, we use the following formula in the cell of the first period:

=-PMT(monthly_interest_rate; total_number_of_periods; principal_amount)

For example, if the monthly interest rate is 3.10%, the loan term is 10 years (120 months), and the borrowed principal is $120,000, the Excel formula would be:
=-PMT((1+3.10%)^(1/12)-1; 10*12; 120000)

Moving on to the third column, we determine the portion of the principal that will be repaid monthly. For instance, for the 40th period, we will repay $945.51 of the principal from our total monthly payment of $1,161.88.

To calculate the principal amount repaid, we use the following formula:

=-PPMT(monthly_interest_rate; current_period; total_number_of_periods; principal_amount)

For example, if the monthly interest rate is 3.10%, and we are in the 40th period, the Excel formula would be:
=-PPMT((1+3.10%)^(1/12); 40; 10*12; 120000)

In the fourth column, we calculate the interest amount for each period. This involves determining the interest paid on the monthly amount after deducting the principal.

To calculate the interest amount, we use the following formula:

=-INTPER(monthly_interest_rate; current_period; total_number_of_periods; principal_amount)

For example, if the monthly interest rate is 3.10%, and we are in the 40th period, the Excel formula would be:
=-INTPER((1+3.10%)^(1/12); 40; 10*12; 120000)

In the fifth column, we determine the remaining amount to be paid after each period. For example, after the 40th payment, we will have $83,994.69 left to pay out of the initial $120,000.

To calculate the remaining amount, we use the following formula:

=principal_amount + CUMPRINC(monthly_interest_rate; total_number_of_periods; principal_amount; 1; current_period; 0)

For instance, if the monthly interest rate is 3.10%, we have a 10-year loan (120 months), and the borrowed principal is $120,000, and we are in the 40th period, the Excel formula would be:
=120000 + CUMPRINC((1+3.10%)^(1/12); 10*12; 120000; 1; 40; 0)

By following these calculations and extending them over 120 periods, we can see that at the end of the 120th period, our loan will be fully repaid.

Trade on the Go. Anywhere, Anytime

You can join one of the world’s biggest crypto-asset exchanges at Binance. Experience competitive fees and receive dedicated customer support while trading with utmost security. Binance provides useful tools to help you track your trade history, manage auto-investments, view price charts, and make conversions without any fees. Signing up for an account is free, allowing you to join millions of traders and investors participating in the global cryptocurrency market.