Schedule Loan Repayments With Excel Formulas
Loan repayment is the act of paying back money previously borrowed from a lender, typically through a series of periodic payments that include principal plus interest. Did you know you can use the software program Excel to calculate your loan repayments?
This article will walk you through all the steps needed to do set up these calculations.
Understanding Your Mortgage in Three Steps
Using Excel, you can get a better understanding of your mortgage in three simple steps. The first step is to determine the monthly payment. The second is to discover the interest rate, and the third is to find the loan schedule.
To do so, you can build a table in Excel that will tell you: the interest rates; the loan calculation for the duration; decomposing a loan, as well as amortization and calculation for monthly rent.
1. Loan Calculation for Monthly Rent
First, let's see how to implement the calculation of a monthly payment for a mortgage. In other words, 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:
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 (number of periods) and the principal borrowed. The last two arguments are optional, the residual value defaults to zero, payable in advance (for one) or at the end (for zero), 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 monthly rate (period of rate), then we calculate the number of periods (120 for ten years multiplied by 12 months) and finally we indicate the principal borrowed. Our monthly payment will be $1,161.88 over ten years.
2. Calculating the Annual Rate of Interest for a Loan
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 it. 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 (monthly, in our case), and then the annual rate. The formula used will be RATE, as shown in the screenshot above. It is written as follows:
The first three arguments are the length of the loan (number of periods), and the monthly payment to repay 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 (for one) or at the end (for zero) 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(12*B4;-B2;B3) = RATE(12*13;-960;120000)
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 percent.
We use the formula = (1 + B5) is 12-1 ^ = (1 + 0.294 %) ^ 12-1 to obtain the annual rate of our loan to be 3.58 percent. In other words, to borrow $120,000 over 13 years to pay $960 monthly, we should negotiate a loan at an annual 3.58 percent maximum rate.
3. Determining the Number of Repayments for a Loan
We will now see how to get 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 percent and monthly payment of $1,100?
The formula we will use is NPER, as shown in the screenshot above, and it is written as follows:
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 (for one) or at the end (for zero) 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 length is 127.97 periods (months in our case).
We will use the formula = B5 / 12 = 127.97 / 12 for the number of years to complete the loan repayment. In other words, to borrow $120,000, with an annual rate of 3.10 percent and to pay $1,100 monthly, we should repay maturities for 128 months or 10 years and 8 months.
Decomposing 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 ten years will give us 120 periods.
The table 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 broken down as follows:
The arguments are the same as for the PMT formula seen in the first part, except for "num_period" which is added to show the period over which to break down the loan, given the principal and 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 one shown in the screenshot "Loan Decomposition," over the period analyzed which is "one," so the first period, or the first month. For this one, we pay $1,161.88, broken down into $856.20 principal and $305.68 interest.
Loan Computation in Excel
Now, 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.
We find the arguments, rate, length, principal and term (which are mandatory) that we already saw in the first part with the formula PMT. But here, we need the "start_date" and "end_date" arguments, as well. The first indicates the beginning of the period to be analyzed and the second indicates the end.
Here's an example:
The result is the one shown in the screenshot "Cumul 1st year ," so the analyzed periods range from one to 12 of the first period (first month) to the twelfth (12th month). Over a year, we would pay $10,419.55 in principal and $ 3,522.99 for interest.
Amortization of the Loan
The prior formulas allow us to create our schedule period-by-period, how much we will pay monthly in principal and interest, and how much is left to pay.
How to Create a Loan Schedule in Excel
To create a loan schedule, we will use the different formulas discussed above and expand them over the number of periods.
In the first period column, simply enter "1" as the first period, then drag the cell down. In our case, we need 120 periods since a 10-year loan payment multiplied by 12 months equals 120.
The second column is the monthly amount we need to pay each month — which is constant over the entire loan schedule. To calculate it, insert the following formula in the cell of our first period:
The third column is the principal that will be repaid monthly. For example, for the 40th period, we will repay $945.51 in principal on our monthly total amount of $1,161.88.
To calculate the principal amount redeemed we are using the following formula:
The fourth column is the interest, for which we calculate the principal repaid on our monthly amount to discover how much interest is to be paid, using the formula:
The fifth column contains the amount left to pay. For example, after the 40th payment we will have to pay $83,994.69 on $120,000.
The formula is as follows:
It uses a combination of principal under a period ahead with the cell containing the principal borrowed. This period begins to change when we copy and drag the cell down. The table below shows that at the end of 120 periods our loan is repaid.