Get the recipe of your success

MORTGAGE


Turn Your Light
Business On!

Click here

Schedule Loan Repayments with Excel Formulas

Did you know you can use Excel to calculate your loan repayments? This article will walk you through all the steps needed to do so. (See also:Mortgage Calculators: How They Work .)

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 lnterest rates; the loan calculation for the duration; decomposing a loan, as well as amortization and calculation for monthly rent.

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 Iinterest 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 :

=-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 0, payable 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 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 the principal borrowed. Our monthly payment will be $1,161.88 over 10 years.

Mortgage Computation for Interest Rates

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.

Computing the Rate of Interest 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 will be RATE, as shown in 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 borrowed. The last three arguments are optional, and the residual value defaults to 0, the term argument for managing the maturity in advance (for 1) or at the end (for 0) is also optional, and 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 a minus sign before the formula. Our rate 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 to be 3.58%. In other words , to borrow 120,000 $ over 13 years to pay monthly 960 $ we should negotiate a loan at an annual 3.58 % maximum rate.

Mortgage Computation for the Length of 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% and monthly payment of $1,100?

Number of Repayments for a Loan

The formula 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 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 0, the term

argument payable 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 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 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 10 years, will give 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 broken down as follows:

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

=-INTPER(rate;num_period;length;principal;[residual];[terme])

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, giving 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 "1," so the first period, or the first month. For this one, we pay $1161.88, broken down into $856,20 principal and $305.68 interest.

Excel Loan Computation

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.

=-CUMPRINC(rate;length;principal;start_date;end_date;type)

=-CUMIPMT(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, as well. 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 shown in the screenshot "Cumul 1st year ," so the analyzed periods range from 1 to 12, of the first period (first month) to the twelfth (12th month). Over a year, we would pay $ 10 419,55 Principal and $ 3 522.99 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.

Create a Loan Schedule in Excel

To create a loan schedule, we will use 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 = 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:

=-PMT(TP-1;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, 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:

=-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 the principal repaid on our monthly amount to discover how much interest is to 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 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:

=$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 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 screenshot below shows that at the end of 120 periods our loan is repaid.


Category: Mortgage calculator

Similar articles: