Thanks once again to all who took part in the modelling challenge. I’m sorry it’s taken me a few weeks to respond: I had to break for some Spanish sunshine over half-term.
Of 45 responses submitted, 39 matched our solution. I don’t think I will need a graph this time to show the results.
I’d like to defer to Sally Brierley, who posted a great explanation of the approach with her solution:
“I have 11.24% as the result using two methods:
1) Using XIRR and adjusting the range to remove the first zero cell (not really a FAST approach but excel has its downsides occasionally!!) This resulted in 11.24%
2) Using the NPV calculation but changing the 8% into an input cell and using goal seek to change the 8% so that the NPV equals zero”
Sally mentions the need to adjust the range of the XIRR calculation to remove the first zero.
There is an issue with the XIRR function and its treatment of zero when zero is its first cash flow. I resolve the issue by having a very small negative and hard-coded number in that first column – suitably marked as a placeholder, of course.
To calculate the IRR without using complex excel functions, the easiest way is to set up an NPV calculation and use an iterative approach to find the discount rate that gives an NPV of zero. Sally did this using the goal seek function within Excel.
Francesco Smeragliuolo, another respondent, solved it graphically by plotting the NPV for a range of different discount rates to find the rate at which the NPV is zero. I thought that was a pretty cool approach.
One approach that I was happy not to see, was the use of the IRR function rather than XIRR.
I would prefer to use the XIRR function since it matches cashflows with actual dates. Compare that with the IRR function that assumes equal periods between cashflows. The IRR function returns a periodic answer and does not care whether cashflows are annual, semi-annual, monthly etc. Use of the IRR function in the example model would have given a semi-annual periodic rate which would then need to be converted to an annual rate.
I have uploaded a model solution here. You will see that we have calculated the IRR using both XIRR and from first principles, pleasingly they both give the same result.
Anyway, to more important matters: the winner.
Based upon our complex selection algorithm, the winner is Antoine Blayau of Solairedirect. Congratulations Antoine, you are awarded access to FAST Financial Statement Modelling Online and a limited edition F1F9 mug.
Thank you all for your participation and please look out for the next modelling challenge in a few weeks time. I am planning for it to be a ‘hunt the error’ challenge.