Another IRR in Excel? Why leaders prefer getting stuck in an elevator

(© studiostoks - Shutterstock)

The pace at which data is now being created and collected has transformed the world we live in. Legacy systems are rapidly becoming outdated, and the data explosion of the past decade, exacerbated by global events, has pushed organizations to re-think the way they use data. Financial services professionals are at the forefront of this shift. Pushed to make long-term decisions in short timeframes, many finance teams struggle to visualize, model and predict using only Excel.

In a Hakkoda-commissioned independent survey of 150 financial services leaders, 62% said Excel can’t handle data visualization, real-time insights or reporting — especially when it comes to complex financial calculations such as Internal Rate of Return (IRR). That’s probably why 42% of financial service professionals said they’d prefer to be stuck in an elevator for three hours before doing another IRR in Excel. In this post, we’ll explore why the modern data stack offers a more flexible approach to complex calculations when compared to Excel.

Why Excel falls short

The Internal Rate of Return is a key metric used by investors, business managers and many other decision-makers to evaluate project profitability and viability. In the past, an Internal Rate of Return was calculated using the XIRR or the MIRR function in Excel. Although Excel helps people avoid some manual calculations, teams calculating an IRR in Excel face a series of limitations.

First, Excel has difficulty processing large amounts of data, and dealing with thousands of spreadsheets can become a very time-consuming task. For instance, if Excel has to go through more than 20 iterations to find the IRR, it displays an error. Leaving data volume aside, the built-in IRR Excel function doesn’t perform periodic calculations. Instead, it expects data to be modeled in Excel on a monthly basis, creating internal time waste and project slowdowns across teams.

Excel also faces accuracy, consistency and manipulation issues at scale. What does this look like? Imagine your opening cash flow isn’t negative. When this occurs, your IRR can be calculated in multiple ways. However, Excel will only display the first one it figures out. To compound the issue, Excel will only use the first calculation each time the cash flow changes from negative to positive.

Another accuracy error occurs when spreadsheets have blank spaces. Instead of treating blank spaces as zero, Excel ignores them. Dragging data in the wrong range and other human errors can lead to significant miscalculations.

Because Excel has made the Internal Rate of Return a go-to formulaic calculation, some companies have made the mistake of using it as their default tool for measuring investment value. However, as the Modern Data Stack (MDS) gathers momentum, financial services professionals with an eye toward the future are starting to question its value as a core valuation tool. Excel may be familiar to everyone, but   let’s remember it was built in the eighties —and it shows.

What makes a modern data stack different

As the need for clear data strategy increases, many decision-makers and business leaders are looking for ways to turn the needs of customers into actionable data. A new approach to data integration is the modern data stack (MDS). Simply put, the MDS is a suite of tools for data integration. Functioning in tandem, these tools allow companies to eliminate data silos, make complex calculations in real time, and power data-driven decisions with confidence that their information is both accurate and timely.

However, the modern data stack is not an all-in-one solution. Instead, MDS is made out of composable blocks that allow businesses to combine different technologies and components into a tailored solution, perfectly outfitted for their business needs and growth.

A core data stack includes extract, load, transform and leverage blocks, as data scientist Marie Lefevre explained in a recent article on Medium. Data is first extracted using a variety of sources and the best solution depends on the type and amount of data you have. Extracted data is then loaded into an appropriate infrastructure and stored, before it is transformed.

The third element, data transformation, involves cleaning and processing data to make it usable for different purposes. Finally, leveraging data revolves around data outputs, which may include reports, interactive dashboards, data discovery tools, and many other modes of viewing and making data accessible.

Some of the most sought-after technological tools in recent years deal with specific aspects of the data stack. For instance, Snowflake, a data cloud-delivery tool, helps thousands of organizations load their data in the data cloud. Another good example is Sigma, a BI analytics platform that’s purpose-built for the cloud data warehouse.

Sigma and Snowflake offer software that can help companies build a customized data stack, going beyond what legacy tools offer. Once a company has their customized modern data stack in place, a calculation like IRR starts to look a little different.

Calculating an IRR using a modern data stack

Where Excel’s IRR calculations are capped at 20 iterations, a modern data stack can scale infinitely. A tool like Snowflake can recurse at multiple depths and automatically aggregate to overall portfolio views.

Snowflake also helps remove clunky Excel linkages and functions by using an ANSI SQL interface to create an easy means to calculate and query data. Pair Snowflake with Sigma and you can power an end-to-end user experience with the familiarity of Excel, but the power of high volume, high velocity, and high capability analysis.

A Snowflake and Sigma pairing also proves exceptionally useful for dynamic modeling of data. These tools allow experts to create cash flow order and assumptive logic as built-in memory functions, thus generating more accurate results.

A modern data stack can also help financial services companies pair the IRR with the Net Present Value (NPV), introduce weightings, and craft custom logical combinations of metrics to enable a more accurate reflection of value and return. An MDS drives holistic insights, guiding data-driven decision-making that can give your company the competitive edge.

The future of the modern data stack

Modern data stack tools such as Sigma and Snowflake, as well as many others we haven’t mentioned in this article, are quickly advancing, overtaking legacy systems to create more accurate results. The rise of cloud computing and the MDS has enabled a level of flexibility in terms of components and pricing that businesses have never seen before.

This flexibility steers away from end-to-end platform solutions, a former go-to for many businesses. Instead, the modern data stack allows organizations to modify their stack whenever their needs change. As a result, they can create game-changing solutions that adapt to their specific characteristics and speed up their digital transformation.

So long, Excel. Let’s say hello to the flexibility and power of the modern data stack.