A Custom Excel Function To Calculate RF(M) Scores

To accompany a guest post with Gravyty, I want to share an Excel function we wrote to take your entire constituency and generate a Recency-Frequency score for each individual.

This function is like other built-in functions you may have used in Excel, except that we've programmed it to do exactly what we want: determine the Recency-Frequency score for each individual donor record.

Recency-Frequency Score = Recency Score + Frequency Score

  • Recency-score function requires the last gift date
  • Frequency-score function requires a person's year of graduation and how many years they have contributed

Then, just add these two numbers together.

It might sound complicated, but once you get through it it's fairly simple. Once you get the hang of it, you'll be able to run these any time you wish. For more information, check out these links from Microsoft's Office support pages:

I am happy to help where I can. Whether you need other custom functions or better understanding how these may benefit your organization, let me know.


Excel Function: recencyScore

Public Function recencyScore(r As Range) As Integer

    Dim lastdonate As Date

    Dim donatefiscal As Integer

    Dim currentfiscal As Integer

    Dim d As Integer

    

    lastdonate = CDate(r.Value)

    

    ' we assume fiscal years run from July to June. If that changes,

    ' this next line will need to be modified

    donatefiscal = IIf(Month(lastdonate) <= 6, Year(lastdonate) - 1, Year(lastdonate))

    currentfiscal = IIf(Month(Now()) <= 6, Year(Now()) - 1, Year(Now()))

    d = currentfiscal - donatefiscal

    

    'Has never made a gift

    If Not IsDate(r.Value) Then

        recencyScore = 0

    'Has made an FY15 gift

    ElseIf d = 0 Then

        recencyScore = 20

    'Gave last year

    ElseIf d = 1 Then

        recencyScore = 20

    'Gave 2 year ago

    ElseIf d = 2 Then

        recencyScore = 15

    'Gave 3 years ago

    ElseIf d = 3 Then

        recencyScore = 10

    'Gave 4 years ago

    ElseIf d = 4 Then

        recencyScore = 5

    'Gave 5 years ago

    ElseIf d = 5 Then

        recencyScore = 2

    'Gave more than 5 years ago

    ElseIf d > 5 Then

        recencyScore = 1

    'Error score

    Else

        recencyScore = -1

    End If

 

End Function

 

Excel Function: freqScore

Public Function freqScore(giftCount As Integer, classYear As Integer) As Integer

    Dim freqP As Double

    

    'Calculate the frequency ratio

    '# years a gift has been made divided by the total # years possible

    freqP = giftCount / (2014 - classYear)

 

    'Check to see if they have ever made a gift

    'If no gifts, then it's zero

    'otherwise, continue

    If giftCount = 0 Then

        freqScore = 0

        

    ElseIf freqP >= 1 Then

        freqScore = 30

 

    ElseIf freqP >= 0.9 And freqP < 1 Then

        freqScore = 24

        

    ElseIf freqP >= 0.8 And freqP < 0.9 Then

        freqScore = 18

 

    ElseIf freqP >= 0.7 And freqP < 0.8 Then

        freqScore = 12

 

    ElseIf freqP >= 0.6 And freqP < 0.7 Then

        freqScore = 6

 

    ElseIf freqP < 0.6 Then

        freqScore = 3

        

    'Error score

    Else

        freqScore = -1

    End If

 

End Function