The Internal Rate of Return (IRR) is a financial metric used to evaluate the profitability of an investment by calculating the discount rate at which the net present value (NPV) of a series of cash flows equals zero. In finance, IRR helps investors compare the potential returns of different projects, guiding decisions on whether to pursue or abandon an investment. Typically, if the IRR exceeds the cost of capital, the project is considered viable.

Simulating IRR is valuable because it allows analysts to model various scenarios, adjusting cash flow assumptions to assess risk and sensitivity, which enhances decision-making. Historically, this was cumbersome due to the iterative, trial-and-error nature of IRR calculations, requiring intensive manual calculations or help from expensive software.

Today, with Python integrated into Excel and tools like Copilot, complex simulations are streamlined. Python handles iterative calculations and data manipulation efficiently, while Copilot assists with coding, making the process faster and more accessible. Let’s check it out with the simulation below:

If you’ve never used the Advanced Analysis features of Copilot before, please take a moment to explore them before following this post:

I’m assuming you already know how to set up your workbook for use with Copilot.

Typically, when using Copilot and Advanced Analysis, we’d start with a dataset or table as the foundation for our analysis or visualization.

However, in this example, we’re going to take a different approach and define the entire analysis using simple prompts. With just our natural language, we’ll specify the initial investment, the number of cash flow periods, the distribution of returns, and other details. We’ll ask Copilot to provide visualizations, along with some additional parameters and rules of thumb.

The more detailed our input, the closer the results might reflect real-world scenarios—though keep in mind this is a simulation, so we’re modeling some inherent uncertainty. Feel free to copy the prompt below into the Copilot box and tweak any of the parameters to fit your own project:

From this point, Copilot will guide you through the full simulation, converting your prompt into Python code. This is where having a basic understanding of Python and its code becomes really useful.

If you open the Python editor by navigating to the ribbon and selecting Formulas > Editor, you’ll be able to examine the code, understand it, and ensure it aligns with your goals:

Most of these inputs are hard coded at the top into variables in Python, and everything flows all the way down into some interesting visualizations. But what if we wanted our users to stress test this thing a little bit and change the inputs and understand some of the assumptions in the workbooks?

Fair enough. I’m going to make a copy of this worksheet and redo the inputs so they’re user driven by specific cells in Excel. This way it’s a little more tactile and user interactive. I could even add data validation and so forth here. The sky’s the limit to what you and the user can co-create in Excel:

A useful next step could be to lock in the axes on each chart and apply conditional formatting to IRR values, highlighting those above or below a specific threshold (typically the cost of capital). For tips on how to do that, along with other examples of using Monte Carlo simulation with Python in Excel, check out this post:

What questions do you have about Monte Carlo simulations using Python and Copilot in Excel, or about this powerful trio in general? Drop them in the comments below.

The post Python in Excel: How to simulate IRR with Copilot first appeared on Stringfest Analytics.