Monthly Archives: April 2026

Access, Excel

Unique values, comparing two data ranges

You have two data ranges, D1 and D2, which are nearly identical. How to check what are the differences between the two ranges.

The first time I had this issue, I used COUNTIF function. Range and criteria are the arguments for COUNTIF. How many times criteria exists in the range.

In G3 I have COUNTIF, which calculates how many times does “8287” exist in D1 data range. If the value is zero, then the value is not found in D1. If the value is one, then the value exists once in D1.

In the same way, I have COUNTIF in B column. How many times does a value in D1 data. The sentence in B3 is =COUNTIF($F$3:$F$34;C3).

I added the header for the B column and filtered the values with zero COUNTIF. Values 1290 and 7008 exist in D1 range but not in D2.

Values 9002 and 3812 exist in data range D2 but not in D1.

We had some manual steps to find out differences between data ranges. One way to automate the process, is to use FILTER function.

FILTER is doing the same thing as I did with manual filtering. We take the values from C3:C34 where B3:B34 is zero and from F3:F34 where G3:G34 is zero.

We can have COUNTIF as embedded function inside the FILTER. COUNTIF function can be used with conditional formatting.

Select the first data range and take home – conditional formatting – manage rules.

Rule type is “Use a formula to determine which cells to format”.

Write the rule:

=countif($F$3:$F$34;C3)=0

Note that Excel defaults absolute reference, but criteria needs to be relative reference C3.

The results.

Repeat the process for the D2 data range, activate F3:F34.

Now the rule is =countif($C$3:$C$34;F3)=0 .

Highlighted values are not existing in another data ranges.

If you have the data in Access, let us use the following SQL query:

We select D1 values with left join, or outer join, to D2 with condition that D2 is empty. So, we join D1 to D2 but only when D2 is empty. Then we get values which are only in D1 but not in D2.

To have the other way, the values which exist only in D2 but not in D1, we need to have a mirror from previous query.

We are selecting D2 values from D2. We join D2 to D1 but the condition is that we want only D2 values when D1 is NULL.

I am sure that there are other ways to compare two data ranges to check which values exist only in one data range but not in another one. For me most practical way is to use conditional formatting. That does not populate any extra cells, only unique values are highlighted.

Excel

CUMPRINC and CUMIPMT functions

CUMPRINC and CUMIPMT are less familiar financial functions compared to PMT, IPMT and PPMT, at least for me.

Let’s revise PMT, IPMT and PPMT first.

If you borrow 10 000 from a bank, interest is 4 % a year and your payback time is three years, then your monthly payment to the bank is 295,24. This we can calculate with PMT function.

PMT function returns a negative value, therefore I use minus with function. For me, it makes more sense to have a positive value. PMT holds three arguments: interest, number of periods and debt capital. As we calculate monthly payment, I divided interest by twelve and multiplied number of periods by twelve.

The monthly payment is divided into interest and principal.

Payment is 295,24 each month, but division between interest and principal is changing. At the start, interest is high and principal is low. At the start, relatively high part of payment is used for paying interest. Then paying the principal back to bank is correspondingly low. When time goes by, proportion of interest is decreasing, and higher part of principal will be paid back to the bank. IPMT defines the interest for each payment and PPMT the amount of principal.

CUMPRINC and CUMIPMT functions are used if you want to calculate how much either principal or interest you have paid during certain periods during payback time.

For example, during the first year, you will pay interest for 341,74 and principal 3201,14. As you see, I have simply calculated with SUM function. 341,74 plus 3201,14 is equal to 295,24 multiplied by 12.

Same value can be calculated with CUMPRINC and CUMIPMT.

CUMPRINC and CUMIPMT functions have the same arguments:

  • Rate, as we calculate monthly values, the rate is C3/12.
  • Number of payment periods, that is D3*12, 36 months.
  • Present value, debt capital B3.
  • Start period, from which period do we calculate principal or interest.
  • End period, till which period do we calculate principal or interest.
  • Type, is the payment at start of the month or at the end of the month. I have the payment at the end of the month, the value is 0.

CUMPRINC and CUMIPMT can be useful functions when you calculate the cumulative principle and income. However, it is useful to revise financial functions PMT, IPMT and PPMT before using CUMPRINC and CUMIPMT.

Please note that the values Excel is calculating might be slightly different than numbers from your bank. The use of calculating financial functions in Excel to have another view to loan calculations in addition to what your bank says.