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.