April 2021 Paper 1

[Trevor]

Hi,

I found the April 2021 paper particularly challenging, given the complexity of the modelling requirements. This paper had flagged up two major questions I have:

1. Is it necessary to build the Excel model exactly in the same order as the questions asked?
For example, part(iv) asked us to model the cumulative battery charge level, allowing for increase or decreases calculated in the previous parts.
To do this, I needed to understand (and visualise) the entire process. So I modelled the source of electricity from:

• Generated from solar
• From battery
• Purchased from provider,

in order to arrive to the cumulative battery charge.
In this process, I answered the next question (1 part(v) ) without realizing it, asking for the amount of electricity purchased.

Do I have to duplicate another column just to show the result for part(v) specifically?
This will be clear to the examiner, but it doesn't make sense when anyone reviews the model - what is this extra column for?
Reading the model solution, I see how they completed part(iv) without first doing part(v). However I find it difficult to follow their model although it is technically correct, I find my version more intuitive.

There are other instances where I built the model independently, without referring to the guide in question 1, to arrive to the final result asked for in the "Background" document (ie: what the client asked for).
Will I be penalised for not following the instructions exactly, even if my model contained everything asked for in the guide?



2. NPV & IRR calculation
In most past paper questions, the model solutions calculate NPV and IRR using the first principles - iteratively calculate: NPV[t] = CF[t] + NPV[t+1]/(1+r).
I know the existence of the NPV() and IRR() function in Excel, but I thought we are not allowed to use them in CP2 exams.

Part 1(viii) and (ix) awards 6 marks in total for the NPV and IRR calculation. I thought NPV() and IRR() formulas are forbidden (it won't be worth 6 marks just to type the formula).
In fact, part (ix) quotes "...by determining the discount rate that equates the net present value...". I thought they are expecting the use of Goal-Seek including checks for it to be worth 3 marks.
Therefore I went through the full process of re-projecting the NPV with a different dummy rate & build the goal seek checks. This costed me a lot of time, which affected the quality of my audit trail.

In general, is there any rule specifically forbids us to use the NPV() and IRR() function?
If allowed, is it okay to mention in the paper 2 summary:
"The net present value of the cashflow is calculated" without stating the calculation formula?



3. Use of standard Microsoft Office functions
We are told not to use any functions only available in Microsoft Office 2013 and after.

Is there a complete list anywhere stating which functions falls under this category?
I can find the new function list for each Excel release from the official documentation, but I might miss out some versions that I am not aware of.
A few examples I used in CP2 practices:

• DATEDIF() - Not included in the suggestion in Excel, but documented in Microsoft webpage.
• VLOOKUP(value,table,column, TRUE) - I think this is a very efficient way to find values that fall within an interval (non-exact match). However this is not commonly done in practice. Is this considered non-standard?

Thanks,
Trevor

 

[Steve Hales]

1. Is it necessary to build the Excel model exactly in the same order as the questions asked?

Not strictly, no. Exam questions are written in a way which usually makes sense to answer them sequentially. If you want to deviate from that it's up to you.

Do I have to duplicate another column just to show the result for part(v) specifically?

No, that would be unnecessary.

In general, is there any rule specifically forbids us to use the NPV() and IRR() function?

No.

"The net present value of the cashflow is calculated" without stating the calculation formula?

The summary document should explain how the mathematical model works, not how the spreadsheet works. So you wouldn't need to say that NPV() was used, but you should explain the mathematical calculation it's performing.

We are told not to use any functions only available in Microsoft Office 2013 and after.

That's not strictly true, the Examination Handbook currently says: "You cannot use functions only available in versions of Microsoft Office released after 2013" - April 2022.

Is there a complete list anywhere stating which functions falls under this category?

You could google it. This looks like a good list as it details when the functions were introduced.

A few examples I used in CP2 practices:

DATEDIF and VLOOKUP definitely work in Excel 2013, and so are therefore acceptable.

 

[Trevor]

Hi Steve,

Thank you for answering my queries above and the comprehensive list of Excel functions.

but you should explain the mathematical calculation it's performing.

For the NPV calculation, did you mean writing out the notation "NPV = sigma( t=0..n, CF[t]/(1+r)^t )"?
The reader of the summary will be a junior and senior actuary, I thought both groups should be very familiar with the calculation of a net present value.

 

[Steve Hales]

A wordy description would be fine. At some point you'd want to mention the discount rate used and when the cashflows occurred. I'm just nervous able your phrase "The net present value of the cashflow is calculated". Maybe the context and surrounding sentences would expand on that

 

[Trevor]

A wordy description would be fine. At some point you'd want to mention the discount rate used and when the cashflows occurred. I'm just nervous able your phrase "The net present value of the cashflow is calculated". Maybe the context and surrounding sentences would expand on that

I see. I would have described the calculations of the cashflows, including their timing before arriving to the NPV part.

In such case, I suppose it is okay to mention
“The net present value of the net cashflow on <start date> is calculated, using the discount rate of x%”?

 

[Trevor]

Hi Steve,

I have an additional question too relating to the "Data" section of the audit trail / summary.
We do list the input data provided, but do we need to quote the values?
For example, can we just say:
"We are given the
Fixed expense per year
Variable expense per policy"

Or we have to quote the values:
"We are given the
Fixed expense per year - £100
Variable expense per policy - £20"

In most sample solutions, values are quoted. The marking scheme doesn't specify marks for including the value.
Is this actually needed? I am inclined not to write them because it costs quite awhile to write all of them.

 

[Steve Hales]

The values are not generally required. There's more discussion here.