Functions to calculate loan with Excel can be found in very many Excel sites. I have just collected here the functions I have normally used.

	Function	Argum. 1	Argum.  2	Argum.  3
Calculating the loan amount	PV	Rate	Nper	Pmt
Calculating the interest rate	Rate	Nper	Pmt	Pv
Calculating the pay back period	Nper	Rate	Pmt	Pv
Calculating the monthly payment	Pmt	Rate	Nper	Pv

There are four arguments related to the loan calculation:

1. Monthly payment. (Pmt)
2. Interest. (Rate)
3. Number of periods when loan is paid back. (Nper)
4. Amount of loan. (Pv)

Some functions consist of additional arguments but basic values can be calculated with arguments above.

For the first case, if you want to calculate the loan amount, the function is PMT.

=PMT(Rate; Nper; Pv)

The function in A8 is =PMT(A9/12;A10*12;A11).

The interest rate is defined as annual rate, as we calculate the monthly payment, also the rate should be defined as monthly. Therefore the value in A9 should be divided by 12. Number of periods are years, meaning the loan is planned to be paid back by 25 years. Therefore the periods should be multiplied by 12 to get number of months. Present value is the value of the loan. We are planning to take the loan for 48 k.

To calculate the interest rate for the loan, we will use RATE function.

=RATE(NPER; PMT; PV)

The content in the cell C9 is =RATE(C10*12;-C8;C11)*12.

Number of periods are multiplied to 12 to get the periods by months. The payment in the cell C8 is taken as negative value. The Excel assumes that payments are negative values. As I enter payments as positive values, I will add minus in the formula. The present value, amount of loan, is taken from the cell C11.

Number of periods, meaning how long does it take to pay off the loan, is calculated with NPER function.

=NPER(RATE; PMT; PV)

The cell E10 holds the sentence =NPER(E9/12;-E8;E11)/12.  

First the annual rate is divided by 12. The payment is taken from E8 as negative value because the E8 holds positive value. The loan amount can be found in E11. As the pay back period is understood as years, the whole formula is divided by 12.

The present value, the amount of the loan, is calculated with PV function.

=PV(RATE; NPER; PMT)

The sentence in G11 is =PV(G9/12;G10*12;-G8).

The interest rate is chosen in the cell G9, that is divided 12 to get the monthly interest rate. The number of periods are take in G10 and multiplied by again to get number of months. Finally, payment is extracted in G8 with negative sign.

Please, note that when you calculate with loan functions, the results may vary. Like when you check my screen shot, the present value or loan amount calculated with PV is not exactly 48000 €. When you calculate loan values, the idea with these Excel functions is to give you some idea about monthly payments or interest rate. If you calculate interest rate for your loan by yourself and the check what does you bank say about interest, there might be slight difference.

A sales report is downloaded from ERP.

The dimensions are product, sales date and sales volume.

Sales volumes should be calculated for each product and for each month.

Formulas for copy and paste:

=SUMIFS($D$2:$D$9;$B$2:$B$9;G3;C2:C9;”>=1/11/2019″)

=SUMIFS($D$2:$D$9;$B$2:$B$9;G4;C2:C9;”>=1/11/2019″)

=SUMPRODUCT((B2:B9=G3)*(MONTH(C2:C9)=11)*D2:D9)

=SUMPRODUCT((B2:B9=G4)*(MONTH(C2:C9)=11)*D2:D9)

When using SUMIFS, you need to define first the sum range D2:D9, then product criteria B2:B9, the product is defined in the cell G3. The range for month is C2:C9 the criteria should be “>=1/11/2019”.

SUMPRODUCT is bit more complex, to my opinion. First product dimension is defined in SUMPRODUCT B2:B9 which should equal to G3. Then month in C2:C9 should equal to 11. Finally sum range is marked D2:D9. Multiplication sign is the separator.

Third bit easier option is to create an additional column month to sales report. You can extract month from C2 simply =MONTH(C2) which should return you the value 10.

SUMIFS sentence with additional MONTH column:
=SUMIFS(D2:D9;B2:B9;G3;E2:E9;G9)
=SUMIFS(D2:D9;B2:B9;G4;E2:E9;G10)

If we are looking for number for October, simply stating “>=1/10/2019” in SUMIFS does not work as the statement includes all the values higher or equal to Oct 1^st, including November values. The dates should be limited to higher or equal to Oct 1^st and lower or equal to Oct 31^st.

For SUMPRODUCT changing 11 to 10 is sufficient.

Formulas for copy and paste:

=SUMIFS($D$2:$D$9;$B$2:$B$9;O3;C2:C9;”>=1/10/2019″;C2:C9;”<=31/10/2019″)

=SUMIFS($D$2:$D$9;$B$2:$B$9;O4;C2:C9;”>=1/10/2019″;C2:C9;”<=31/10/2019″)

=SUMPRODUCT((B2:B9=O3)*(MONTH(C2:C9)=10)*D2:D9)

=SUMPRODUCT((B2:B9=O4)*(MONTH(C2:C9)=10)*D2:D9)

=SUMIFS(D2:D9;B2:B9;O3;E2:E9;O9)

=SUMIFS(D2:D9;B2:B9;O4;E2:E9;O10)

This is not really a part of the header, but the same things what was done with SUMIF and SUMIFS can be tackled with Pivot tables too.

First, we need header row.

Activate the data area B1:D9 and select insert – pivot table.