“F*@#ing YEARFRAC.”

That was the only thing my MD mumbled as he exited his office after a not-so-nice call with one of our largest model audit clients.

Model audit is a rarefied specialty comprised of both software, human expertise, and oddly, insurance. I like to think of it as spell check for financial models. Our firm was regularly contracted to catch errors in large financial models before they became cemented into actual losses post financial close. If we missed anything, we passed through an insurance policy to our clients that made them whole for any losses up to $5 million suffered due to any mistakes we didn’t catch.

Why YEARFRAC stinks

In this instance, our client had deployed the YEARFRAC formula twice across two very different sections of their financial model, a model describing a public hospital transaction in Canada. On the construction tab, they’d used YEARFRAC to generate the percentage of a year between two construction periods so they could calculate the interest due to the bank. During operations, they’d used it again to calculate the percentage of the year associated with when they’d get paid on their offtake agreement.

Deep in the weeds…

The arguments for YEARFRAC are pretty straightforward; “=YEARFRAC(start_date, end_date, [basis]).” In Excel-speak, this means that to calculate the number of whole days between two dates, all one needs to do is provide a start and end date. But that third argument, [basis], is what made my MD curse so badly.

Whenever you see [ ] around an argument in Excel, it means that argument is [optional]. This means you CAN include more information, but you don’t need to. For example, you CAN add the height and width to the size of the array created by combining SUM with OFFSET. But you don’t have to; Excel can infer based on the inputs you’ve provided elsewhere. Or, you CAN add more conditions to a SUMIF formula, but you don’t NEED TO.

Day count conventions

With YEARFRAC however, the variation between the outcomes implied by choosing among optional arguments accrued to a very real loss for our client, one they’d just threatened to take to our insurance agency, an almost certain death-nell for our model audit practice.

The optional arguments for YEARFRAC pertain to day count conventions, specifically:

[0] means 30 / 360 (US), a convention roughly assuming 30 days in each month and 360 days in each year;
[1] means Actual / Actual, or the actual number of days in a month over 365 or 366 (leap year);
[2] means Actual / 360, or the actual number of days in a month over 360 days in a year;
[3] means Actual / 365, a slight variation on [1] which simply excludes leap years; and
[4] means 30 / 360 (EUR), a slight variation on [0].

The range of outcomes stemming from “optional arguments” can add substantial variability to your formulas, as seen in the “formula result” column of the table above.

Meanwhile, back at MSFT…

When Microsoft was compiling its YEARFRAC options, they likely assumed their formula might be used for all types of international financial arrangements, much like the one our team had audited. Namely, an Australian private equity fund, with offices in Los Angeles, modeling a lending agreement in US dollars for a project to be based in Canada.

That team at Microsoft likely failed to envision the confusion stemming from all these options. There are plenty of more transparent ways to count days by being more explicit and not hiding ones day count conventions in the arguments of a formula. We like to make our day count assumptions explicit in the formulas of our models using a process like this:
Much like Microsoft Word’s spell check, the job of a model auditor is to make certain that the formula (including its inputs) under their scrutiny is operating properly and is correctly applied. That last part is extra hard.

For example, spell check can‘t tell you that John indeed does not attend a “pubic” school, but a public one. And in this instance, our auditor neglected to tell our client that using YEARFRAC with a [1] for its revenue-generating agreement was fine, and made sense as revenue needs to be calculated over the actual number of days in a period and year.

But when the original modeler ported that YEARFRAC assumption over to the construction period tab and applied it to a 32-month construction period with a base cost over $200 million, our model auditor failed to tell him that using YEARFRAC with [1] was going to cost them around $30,000 in missed interest during construction.

Model review > Spell check

The model audit game involves a brutal combination of diving deep into the details of a deal and then stepping back to relate the formula construction to the deal documents. When executed properly, auditors can help catch silly and costly modeling mistakes that can ruin months or even years of work.

We’ve created a macro-driven download that enables you to view some critical statistics on your model. Specifically, the file counts your model’s unique formulas (UFs) and maps the model to check for inconsistent formulas across rows and columns as well as hard coded values embedded in cells.

At Orbis, our auditors have spent decades catching these types of embarrassing errors. And we all HATE f*@#ing YEARFRAC.