Python in Excel: How to simulate IRR with Copilot

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:

Monte Carlo Simulation for IRR Calculation

Objective: Calculate the Internal Rate of Return (IRR) for a project over three years, incorporating uncertainty in cash flows through Monte Carlo simulation.

Inputs and Distributions:

1. Initial Investment:

   • Description: Fixed cash outflow at the start of the project (Year 0).
   • Value: -$100,000

2. Cash Flow Period 1 (Year 1):

   • Description: Expected cash flow with uncertainty.
   • Distribution: Uniform
   • Parameters: Min = $20,000, Max = $40,000

3. Cash Flow Period 2 (Year 2):

   • Description: Expected cash flow with uncertainty.
   • Distribution: Normal
   • Parameters: Mean = $50,000, Standard Deviation = $5,000

4. Cash Flow Period 3 (Year 3):

   • Description: Expected cash flow with uncertainty.
   • Distribution: Triangular
   • Parameters: Min = $40,000, Most Likely = $60,000, Max = $80,000

Simulation Settings:

• Number of Periods: 3 years
• Number of Simulations: 10,000 iterations
• Correlation: Include a slight positive correlation (0.3) between revenue and costs if applicable.

Constraints:

• Ensure all cash flows are non-negative (Cash flows ≥ $0).

Output and Visualization:

• Calculate the IRR for each simulation run and derive statistics (mean, median, confidence intervals) about the IRR from the simulation results.
• Visualizations:
  • Histogram: Display a histogram of the IRR results to visualize the distribution and identify the spread and skewness of IRR outcomes.
  • Box Plot: Use a box plot to highlight the median, quartiles, and any outliers in the IRR data.
  • Probability Density Plot: A smoothed probability density plot to see the density of different IRR ranges, giving a clear visual of the most probable IRR outcomes.
  • Cumulative Distribution Function (CDF) Plot: This plot will show the probability of achieving an IRR less than or equal to each value, helpful for risk assessment.

Procedure:

1. Generate cash flows for each year based on specified distributions.
2. Calculate IRR for each set of yearly cash flows across all simulations.
3. Aggregate the IRR results to analyze the distribution and assess the risk and return profile of the investment.
4. Visualize the results using the described plots to provide a comprehensive overview of the potential IRR outcomes.

Analysis:

• Review the distribution of the IRR results to understand the likelihood of achieving various IRR thresholds.
• Determine the probability of achieving an IRR above a specific target (e.g., the company's cost of capital).
• Use visualizations to present findings in a clear and accessible manner to stakeholders.

view raw copilot-irr-monte-carlo-prompt.md hosted with by GitHub

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.