Probability of passing CFA exam - VBA Code

CFA Level II Ethics
#1

Hello all,

I made a code for estimating the probability of passing the CFA exam.

You put this code in your Excel - VBA (you open an Excel file, click F11 and put the code in a Modules)

Function Probability_Pass(Nb_passed_answers As Integer, Nb_sure_right_answers As Integer, Nb_sure_wrong_answers As Integer) As Double Dim Nb_educated_guesses As Integer Dim Nb_needed_for_passing As Integer Nb_total_questions = 120 Nb_educated_guesses = Nb_total_questions - Nb_sure_right_answers - Nb_sure_wrong_answers Nb_needed_for_passing = Nb_passed_answers - Nb_sure_right_answers Dim i As Integer Dim sum_fail As Double sum_fail = 0 For i = 0 To Nb_needed_for_passing - 1 sum_fail = sum_fail + Application.WorksheetFunction.Combin(Nb_educated_guesses, i) * (2 / 3) ^ Nb_educated_guesses * (1 / 2) ^ i Next i Probability_Pass = 1 - sum_fail End Function

In Excel, you enter this formula in bold below and replace

  • Nb_passed_answers: The MPS. You put 84 if you think you must have 70% to pass.
  • Nb_sure_right_answers : The number right questions in your estimation
  • Nb_sure_wrong_answers: The number wrong questions you found

=Probability_Pass(Nb_passed_answers;Nb_sure_right_answers;Nb_sure_wrong_answers)

Exemple : If you think the MPS is 84, your number right answers is 70 and your number wrong answers is 15, your probability of passing the CFA exam is

=Probability_Pass(84,70,15) = 25.21%

I postulate that you have 33.33% of success for each educated guess, the total educated guesses is _ Nb_educated_guesses = Nb_total_questions - Nb_sure_right_answers - Nb_sure_wrong_answers_

Enjoy yourself :-).

#2

Dude I don’t even want to think about this stuff now. I’m just enjoying my short-term memory unload. Feels good. Feels lighter.

#3

Interesting, but the answer is going to be conservative (70 % passing score is the upper limit imo). I also hope that we got more than 33.33 % of our educated guess, because that’s what we can expect from a 4 years old who just learned how to fill circles with a pen.

#4

Concerning the passing score, 70% is just an example, of course you can change it if you want (for example, if you think the passing score is only 65%, just replace the number 84 by 78).

Concerning the probability of 33.33%, I prefer 33.33% more than a probability of 50% or 80% because 33.33% is conservative.

#5

you’re crazy dude !

No way I want to play with your tool (dont want to get mad and worried :-s haha)

#6

Pierre, thank you. Could you recommend a good book for learning VBA? Thx!

#7

record a macro Excel populates code for you manipulate code here and there to tweak references profit people get spooked easily by a VBA copy paste whereas it’s not that hard.

#8

Sorry men. I had made a mistake in the code and I corrected it. You can now use the code in the first post.

@calvol: For VBA code, I have a preferred book but it is written in French. I don’t know any VBA book in English. But you can google, there are a lot of VBA books in the Internet.

#9

An educated guess should be 50% assuming you can eliminate 1 answer. 33.3% is for a random guess.

#10

I only agree with you that an educated guess should be greater than 33.3%. But we don’t know if it is 50%, 75% or even 33.4%. So, I choose 33.33% (or 1/3) because I need a conservative result.

If you think your educated guess is 50%, you can modify the code by replacing

sum_fail = sum_fail + Application.WorksheetFunction.Combin(Nb_educated_guesses, i) * (2 / 3) ^ Nb_educated_guesses * (1 / 2) ^ i

with

sum_fail = sum_fail + Application.WorksheetFunction.Combin(Nb_educated_guesses, i) * ( 0.5 ^ i ) * (1- 0.5 ) ^ (Nb_educated_guesses - i)

:-), you probability of passing is surely higher.

#11

I had to learn vba for my work and I used the wiseowl tutorial on youtube. Now I’m really the goto guy everytime someone want to make something special in excel.

#12

Pierre, I had an error in the Excel version (missing declaration, I added it below, and it works). Also, I get an error with the Google version.

Function Probability_Pass(Nb_passed_answers As Integer, Nb_sure_right_answers As Integer, Nb_sure_wrong_answers As Integer) As Double Dim Nb_educated_guesses As Integer Dim Nb_needed_for_passing As Integer Dim Nb_total_questions As Integer Nb_total_questions = 120 Nb_educated_guesses = Nb_total_questions - Nb_sure_right_answers - Nb_sure_wrong_answers Nb_needed_for_passing = Nb_passed_answers - Nb_sure_right_answers Dim i As Integer Dim sum_fail As Double sum_fail = 0 For i = 0 To Nb_needed_for_passing - 1 sum_fail = sum_fail + Application.WorksheetFunction.Combin(Nb_educated_guesses, i) * (2 / 3) ^ Nb_educated_guesses * (1 / 2) ^ i Next i Probability_Pass = 1 - sum_fail End Function

#13

Yes, Someone had changed the MPS from 60% to 60. I fixed the Google version.

Concerning the Excel VBA version, I think VBA configuration is different to mine and all variables must be explicitly declared(and yes, it’s better to declare all variables).