Loan repayment is the act of paying back money previously borrowed from a lender, typicallythrough a series of periodic payments thatinclude principal plus interest. Did you know you can use the software program Excel to calculate your loan repayments?
This article is a step-by-step guide to setting up loan calculations.
Key Takeaways:
- Use Excel to get a handle on your mortgage or loan by determining your monthly payment, your interest rate, and your loan schedule.
- You can take a more in-depth look at the breakdown of a loan with Excel and create a repayment schedule that works for you.
- There are calculations available for each step that you can tweak to meet your specific needs.
- Breaking down and examining your loan step-by-step can make the repayment process feel less overwhelming and more manageable.
Understanding Your Mortgage
Using Excel, you can get a better understanding of your mortgage in three simple steps. The first step determines the monthly payment.The second step calculates the interest rate, and thethird step determines the loan schedule.
You canbuild a table in Excel that will tell you the interest rate, the loan calculation for the duration of the loan, the decomposition of the loan, the amortization, and the monthly payment.
:max_bytes(150000):strip_icc()/pic28-5bfd8aa646e0fb002607b519 "How to Schedule Your Loan Repayments With Excel Formulas (1)")
Step 1: 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.
:max_bytes(150000):strip_icc()/pic2-5bfd8ab54cedfd002600c0ef "How to Schedule Your Loan Repayments With Excel Formulas (2)")
The formula, as shown in the screenshot above, is written as follows:
=-PMT(rate;length;present_value;[future_value];[type])
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, and 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)
For the rate, we use the monthly rate (period of rate), then we calculate the number of periods (120 for 10years multiplied by 12 months) and, finally, we indicatethe principal borrowed, which is $120,000. Our monthly payment will be $1,161.88 over 10years.
Step 2: 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, wewould like to know the corresponding annual interest rate.
:max_bytes(150000):strip_icc()/pic4-5bfd8ab346e0fb00264b0950 "How to Schedule Your Loan Repayments With Excel Formulas (3)")
As shown in the screenshot above, we first calculate the period rate (monthly, in our case), and thenthe annual rate. The formula used will be 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), the monthly payment to repay the loan, and the principal borrowed. The last three arguments are optional,andthe 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 that the corresponding data in the monthly payment must be given a negative sign. This is why there'sa minus sign before the formula. Therate period is 0.294%.
We use the formula = (1 + B5) is 12-1 ^ = (1 + 0.294 %) ^ 12-1 to obtain the annual rate of our loan, which is 3.58%. In other words, to borrow $120,000 over 13 years and 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.
Step 3: Determine 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?
:max_bytes(150000):strip_icc()/pic23-5bfd8ab14cedfd0026f2258e "How to Schedule Your Loan Repayments With Excel Formulas (4)")
The formula wewill use isNPER, 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 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)
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 reimbursem*nt 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%,and to pay $1,100 monthly, we should repay maturities for 128 months, or 10 years and eight months.
Step 4: Decomposing the Loan
A loan payment is composed of principal and interest. The interest is calculated for each period—for example, themonthly repayments over 10yearswill give us 120 periods.
:max_bytes(150000):strip_icc()/pic25-5bfd8aae46e0fb005184a3dd "How to Schedule Your Loan Repayments With Excel Formulas (5)")
The table above shows the breakdown of a loan (a totalperiod equal to120) usingthe PPMT and IPMT formulas. The arguments of the two formulas are the same and are broken down as follows:
=-PPMT(rate;num_period;length;principal;[residual];[term])
The arguments are the same as for the PMT formula already seen, except for "num_period," which is added to show the period over which to break down the loan given the principal and interest. Here's an example:
=-PPMT((1+B2)^(1/12)-1;1;B4*12;B3)=PPMT((1+3,10%)^(1/12)-1;1;10*12;120000)
The result is shown in the screenshot above "Loan Decomposition" over the period analyzed, which is "one;" that is, the first period or the first month. We pay $1,161.88 broken down into $856.20 principal and$305.68 interest.
:max_bytes(150000):strip_icc()/pic24-5bfd8aac46e0fb005184a38e "How to Schedule Your Loan Repayments With Excel Formulas (6)")
Step 5: 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(rate;length;principal;start_date;end_date;type)
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 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+B2)^(1/12)-1;B4*12;B3;1;12;0)
The result is shown in the screenshot "Cumul 1st year,"so the analyzed periods range from oneto 12of 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 in interest.
Step 6: Amortization of the Loan
The prior formulas allow us to create our scheduleperiod by period, to know how much we will pay monthly in principaland interest, and to know how much is left to pay.
:max_bytes(150000):strip_icc()/pic26-5bfd8aaa46e0fb00269bae3e "How to Schedule Your Loan Repayments With Excel Formulas (7)")
Step 7: Creating a Loan Schedule
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, enter "1" as the first period and then drag the cell down. Inour case, we need 120 periods since a 10-year loan payment multiplied by 12 months equals120.
The second columnis the monthly amount we need to pay each month—whichis constant over the entire loan schedule. To calculate the amount, insert the following formula in the cell of our first period:
=-PMT(TP;B4*12;B3)=-PMT((1+3,10%)^(1/12)-1;10*12;120000)
The third column is the principal that will be repaid monthly. For example, for the 40th period, wewill repay $945.51 in principal on our monthly totalamount of $1,161.88.
To calculate the principal amount redeemed, 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 columnis the interest,for whichwe use the formula to calculate the principal repaid on our monthly amount to discoverhow much interest is to be paid:
=-INTPER(TP;A18;$B$4*12;$B$3)=-INTPER((1+3,10%)^(1/12);1;10*12;120000)
The fifth column contains the amountleft to pay. For example, after the 40th payment,wewill have to pay $83,994.69 on $120,000.
The formula is as follows:
=$B$3+CUMPRINC(TP;$B$4*12;$B$3;1;A18;0)
The formula uses a combination of principal under a period ahead of 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.
:max_bytes(150000):strip_icc()/pic27-5bfd8aa846e0fb0026d263da "How to Schedule Your Loan Repayments With Excel Formulas (8)")
How Do I Create a Loan Repayment Schedule in Excel?
To create an amortization table or loan repayment schedule in Excel, you'll set up a table with the total loan periods in the first column, monthly payments in the second column, monthly principal in the third column, monthly interest in the fourth column, and amount remaining in the fifth column. Each column will use a different formula to calculate the appropriate amounts as divided over the number of repayment periods.
What Is the Formula for Monthly Payments in Excel?
Use the PMT function in Excel to create the formula: PMT(rate, nper, pv, [fv], [type]). This formula lets you calculate monthly payments when you divide the annual interest rate by 12, for the number of months in a year.
How do I create a PMT formula in Excel?
When you create a PMT formula, such as PMT(rate, nper, pv, [fv], [type]), you need several data points. In this formula, rate is the interest rate of the loan, nper is total number of payments, pv is present value, or the principal of the loan, fv is future value after the loan is paid off, and type is when the payments are due. As indicated by the brackets, fv and type are optional arguments.
The Bottom Line
The process of paying back a loan can be challenging, particularly in terms of organization and accountability. To help with that, Excel can calculate and schedule your loan repayments. You can build a table in Excel to keep track of the interest rate, the loan calculation for the loan's duration, the decomposition of the loan, the amortization of the loan, and the monthly payment. This may make the process of paying back the loan more achievable.
