Lump-Sum Investing (LSI) vs. Dollar-Cost Averaging (DCA): How Should Investors Enter the Market?

Varut WISSANUWATNAKIT

In this article, Varut WISSANUWATNAKIT (ESSEC Business School, Global Bachelor in Business Administration (GBBA), 2026-2027) examines the debate between Lump-Sum Investing (LSI) and Dollar-Cost Averaging (DCA) when deploying capital into financial markets.

Introduction

When looking at how to put money into financial markets, investors usually face a simple but critical question: do you invest everything all at once, or spread it out over time? This brings up the classic choice between Lump-Sum Investing (LSI) and Dollar-Cost Averaging (DCA).

While financial theory often argues that LSI gives you the highest expected return, real-life investing is rarely that straightforward. Fear of bad timing, market volatility, and the stress of watching a portfolio drop right after buying lead many investors to behave very differently from what pure theory suggests.

Defining the Strategies

Lump-Sum Investing (LSI)

With lump-sum investing, you put all your available capital to work on day one rather than holding back cash. Whether you have just received an annual bonus, an inheritance, or built up savings, you immediately allocate 100% of those funds into your target portfolio such as an index ETF or a basket of equities.

From that very first day, your money is fully exposed to market risk. You capture every upward movement immediately, but you also absorb the full blow of any immediate market drops. There is no waiting on the sidelines: you accept whatever the current market price happens to be, believing that being invested in the market as long as possible outweighs the risk of short-term volatility.

Dollar-Cost Averaging (DCA)

Dollar-Cost Averaging (DCA) is a mechanical investment strategy where an investor divides a fixed sum of capital into equal parts and commits them to the market on regular, predetermined dates—such as monthly or quarterly—regardless of current market conditions. As Meir Statman (1995) highlights, DCA acts as a behavioral framework that automates buying decisions, taking away the emotional urge to time market peaks and valleys.

Mathematical Formulation

Suppose an investor holds a total lump sum of capital C that they plan to deploy systematically across N equal time periods. At each period t (where t = 1, 2, …, N), the amount invested is fixed:

It = C / N

Because the cash invested at every interval is identical, the quantity of shares Qt purchased at date t depends directly on the market price Pt:

Qt = It / Pt

Over the entire investment horizon of N periods, the total number of accumulated shares Qtotal is the sum of all individual purchases:

Qtotal = Q1 + Q2 + … + QN = (I1 / P1) + (I2 / P2) + … + (IN / PN)

The effective average cost per share paid by the investor, denoted as PDCA, is the total capital deployed divided by the total shares owned:

PDCA = C / Qtotal = N / [ (1 / P1) + (1 / P2) + … + (1 / PN) ]

Mathematically, this equation shows that PDCA is the harmonic mean of the asset prices across all purchase periods. Because the harmonic mean is always less than or equal to the standard arithmetic average of those same prices when volatility occurs, DCA mechanically ensures that an investor purchases more shares when prices are depressed and fewer shares when prices are high.

Numerical Example

Consider an investor allocating €3,000 across 3 consecutive months (€1,000 per month) into an equity index:

  • Month 1: Share price is €100 → Buys €1,000 / €100 = 10.00 shares.
  • Month 2: Share price falls to €80 → Buys €1,000 / €80 = 12.50 shares.
  • Month 3: Share price rallies to €120 → Buys €1,000 / €120 = 8.33 shares.

Over the 3-month period:

  • Total shares acquired: 10.00 + 12.50 + 8.33 = 30.83 shares.
  • Effective average price paid (PDCA): €3,000 / 30.83 = €97.31 per share.
  • Arithmetic average market price: (€100 + €80 + €120) / 3 = €100.00 per share.

Because more shares were bought at the lower price (€80) than at the peak (€120), the investor’s average entry cost (€97.31) ended up lower than the arithmetic average of market quotes (€100.00).

The Theoretical and Empirical Evidence: Why LSI Wins on Return

From a classical financial theory standpoint, capital markets are expected to compensate investors for bearing systematic market risk over time. As formalized in Modern Portfolio Theory and dynamic asset allocation models (Brennan, Schwartz, & Lagnado, 1997), risk assets such as equities carry a positive expected risk premium over the risk-free rate:

E[Requity] > Rf

Because expected returns are positive and financial markets historically trend upward over long horizons, holding cash entails an opportunity cost. Under a Dollar-Cost Averaging strategy, an investor deliberately leaves an uninvested portion of their capital sitting in cash or cash equivalents earning only the risk-free rate Rf while waiting for future allocation dates. This creates an immediate “cash drag” that reduces the expected ending wealth of the portfolio compared to investing the entire amount right away.

This theoretical advantage of immediate market exposure is supported by extensive historical data. In their seminal research paper on capital deployment, Vanguard researchers (Scott et al., 2012) analyzed historical rolling returns across major developed markets—including the United States, the United Kingdom, and Australia:

  • Frequency of Outperformance: Across rolling 12-month periods, Lump-Sum Investing outperformed Dollar-Cost Averaging approximately 66% to 70% of the time across the historical datasets.
  • Magnitude of Excess Return: On average, allocating capital as a lump sum generated an outperformance margin of roughly 2.0% to 2.5% compared to spreading purchases over a 12-month interval.
  • Mechanism of the Return Gap: The performance difference is not driven by market timing skill; it is driven strictly by asset exposure. Because equity markets trend upward more often than they decline, the delayed exposure inherent in DCA means an investor purchases shares at progressively higher average prices in typical bull markets.

Empirically and theoretically, whenever an investor’s sole objective is maximizing expected total wealth, Lump-Sum Investing is the mathematically superior choice.

Simulation Analysis: Bullish, Bearish, and Theoretical Scenarios

To evaluate the practical dynamics between Lump-Sum Investing (LSI) and Dollar-Cost Averaging (DCA), we simulated portfolio evolutions over a 12-month horizon using an initial capital of €10,000, an expected annual equity return of 10% (monthly expected return of ~0.80%), and an annual risk-free cash rate of 3% (monthly rate of 0.25%).

Bullish Market Scenario

In a strongly rising market, immediate exposure allows Lump-Sum Investing to generate the highest ending wealth. Because equity prices trend upward, the delayed purchases inherent in DCA force the investor to acquire shares at progressively higher prices, dragging down overall portfolio performance.

Figure 1. LSI vs. DCA: Evolution of the Portfolio Value in a Bull Market Scenario
LSI vs. DCA: Evolution of the Portfolio Value in a Bull Market Scenario
Source: the author.

Bearish Market Scenario

In a declining market, Dollar-Cost Averaging provides valuable downside protection. By keeping unallocated capital in cash earning the risk-free rate and buying shares as market prices drop, DCA significantly cushions the portfolio’s maximum drawdown compared to immediate lump-sum exposure.

Figure 2. LSI vs. DCA: Evolution of the Portfolio Value in a Bear Market Scenario
LSI vs. DCA: Evolution of the Portfolio Value in a Bear Market Scenario
Source: the author.

Theoretical Expected Value Evolution

Under expected market conditions without random shocks, LSI delivers the highest expected ending wealth of €11,000. As the allocation is spread across more dates, the cash drag from uninvested capital progressively lowers the expected value: €10,822 for DCA (2 dates), €10,763 for DCA (3 dates), €10,705 for DCA (6 dates), and €10,669 for DCA (12 dates).

Figure 3: LSI vs. DCA: Expected Portfolio Values
LSI vs. DCA: Expected Portfolio Values
Source : the author.

Figure 4. LSI vs. DCA: Expected Portfolio Value at the End of Year 1
LSI vs DCA: Evolution of the Expected Portfolio Value at the end of Year 1
Source : the author.

The calculations for the figures can be found in the Excel file you can download below.

Download the Excel file for the LSI vs DCA

The Behavioral Role of Dollar-Cost Averaging

If lump-sum investing mathematically delivers higher expected returns, why do so many investors and financial advisors still prefer dollar-cost averaging? The answer comes down to behavioral finance: real investors are not emotionless spreadsheets, and managing risk often means managing psychology (Kahneman & Tversky, 1979; Statman, 1995).

Mitigating Short-Term Sequence Risk

Putting all your cash into the market at once exposes you completely to bad timing. If an investor deploys 100% of their capital right before a major crash—such as the 2008 Financial Crisis or the 2020 pandemic dip—the portfolio takes an immediate, painful hit that can take years to recover. Dollar-cost averaging softens this blow. By spreading purchases across several months, an investor buys subsequent shares at cheaper prices during a downturn, bringing down their average cost per share and helping the portfolio break even faster when the market rebounds.

Regret Aversion and Prospect Theory

According to Prospect Theory introduced by Daniel Kahneman and Amos Tversky (1979), people feel the pain of losing money about twice as intensely as they enjoy making the same amount. Meir Statman (1995) pointed out that DCA is essentially an insurance policy against emotional regret:

  • The pain of bad LSI timing: Investing everything at day one and watching prices plunge right away creates intense regret and self-blame.
  • The emotional hedge of DCA: If the market drops after starting DCA, you feel relieved because your remaining cash buys discounted shares. If the market goes up, you are still happy because your earlier purchases made a profit. In other words, DCA keeps you emotionally stable in both market directions.

Disciplined Execution Without Market Timing

Having a large pile of uninvested cash often leads to “analysis paralysis.” Investors frequently wait on the sidelines, trying to guess the absolute bottom of the market, only to watch prices climb higher and miss out entirely. As long-term market studies by the Schwab Center for Financial Research (2023) demonstrate, the cost of waiting for the perfect entry point consistently outweighs the risk of investing immediately. DCA eliminates this emotional guesswork. By turning investing into an automatic, scheduled habit, it forces you to stay disciplined and prevents panic selling or reckless market timing.

Comparative Framework: DCA vs. LSI

Dimension Lump-Sum Investing (LSI) Dollar-Cost Averaging (DCA)
Primary Objective Maximizing long-term expected total wealth Minimizing downside risk and behavioral regret
Expected Total Return Statistically higher (~67% historical outperformance) Lower (dragged down by uninvested cash allocations)
Market Risk Exposure Immediate 100% portfolio beta exposure Incremental beta exposure over time
Drawdown Protection Zero cushion against an immediate market drop Dampens maximum drawdown during early market downturns
Psychological Friction High emotional stress and elevated regret aversion High peace of mind and automated decision discipline
Execution Costs Single transaction fee and one-time bid-ask spread Multiple transaction fees and potential spread friction

Why should I be interested in this post?

For ESSEC business and finance students, the debate between Dollar-Cost Averaging and Lump-Sum Investing illustrates the critical gap between theoretical finance and practical asset management. While classical models such as Modern Portfolio Theory and the Capital Asset Pricing Model assume fully rational market participants who should logically invest all available capital immediately to capture the equity risk premium, real-world portfolio managers and wealth advisors must manage human psychology. Understanding how sequence risk, regret aversion, and cash drag interact enables students not only to make disciplined personal investment decisions, but also to design investment strategies that institutional and private wealth clients can actually stick with during turbulent markets.

Related posts on the SimTrade blog

   ▶ Hadrien PUCHE “October: this is one of the peculiarly dangerous months to speculate in stocks. The others are July, January, September, April, November, May, March, June, December, August and February.” – Mark Twain

   ▶ Lang Chin SHIU Time value of money

Useful resources

Academic research

Brennan, M. J., Schwartz, E. S., & Lagnado, R. (1997) Strategic Asset Allocation, Journal of Economic Dynamics and Control, 21(8-9), 1377–1403.

Kahneman, D., & Tversky, A. (1979) Prospect Theory: An Analysis of Decision under Risk, Econometrica, 47(2), 263–291.

Statman, M. (1995) A Behavioral Framework for Dollar-Cost Averaging, Journal of Portfolio Management, 22(1), 70–78.

Business resources

Scott, D. G., Balsamo, J. D., LaSalle, T. N., & Rinehart, B. L. (2012). Dollar-cost averaging just means taking risk later Vanguard Research.

Schwab Center for Financial Research. (2023). Does market timing work? Charles Schwab & Co.

About the author

The article was written in November 2026 by Varut WISSANUWATNAKIT (ESSEC Business School, Global Bachelor in Business Administration (GBBA), 2026–2027).

   ▶ Discover all posts by Varut WISSANUWATNAKIT.

The investment ecosystem

The investment ecosystem

Nithisha CHALLA

In this article, Nithisha CHALLA (ESSEC Business School, Grande Ecole Program – Master in Management, 2021-2023) explains the investment ecosystem in financial markets.

Introduction

In the investment ecosystem, there are several blocks to understand: market participants, market products, and market organization.

Market participants

Market participants are individuals, companies, financial institutions, and governments. Some of these participants may issue assets like companies (stocks, commercial paper, and bonds) and governments (bonds). Some of these participants may invest in these assets like individuals or pension funds.

Based on the amount they invest, market participants are segregated as big players and small players. Big players are mostly institutional investors which collect the funds and then invest them. Few examples of institutional investors are mutual funds, pension funds, hedge funds, trusts, charities etc. Big players may also be wealthy individuals (high net worth individuals or HNWI) or family offices. Small players are other individual investors.

Corporates run businesses, including manufacturing, service, and technology firms, and they need capital to expand and maintain their operations. On the other side, we have institutions that consist of fund managers that could be institutional investors but also retail investors as well. These are the people that have capital so the capital flows from the institutions or investment managers who have the money to the corporations that need that money to grow and run their business. The cycle between the two parties is completed when the firms issue back to the investor’s bonds, which are classified as debt, or shares, which are classified as equity.

In the middle of these two groups sit the investment banks they are often referred to as the sell side and they have contacts on both sides of these players. They have corporate clients, and they have institutional investor clients, their job is to match up the institutional investors with the corporates based on risk and return assessments and expectations and investment style to get the deal done. In addition, we have public accounting firms which are the fourth player in the market.

Market Products

Assets

What are assets? In financial language, an asset is that which has some economic value. And assuming that its value increases in the future market participants buy them and that is how it is a part of the investment ecosystem. Few examples for assets are fixed deposit, land, gold, stock, etc.

Asset classes are made up of those investments or securities whose characteristics are the same. Few major asset classes are equity, bonds (fixed income), commodities, and real estate.

Instruments

What are Instruments? Instruments are the ways through which we can invest in different asset classes.

Some of the major instruments we see in markets are direct investing, mutual funds, and exchange-traded funds (ETFs).

  • Direct investing is nothing but investing cash physically in different asset classes or we can digitally buy assets through our accounts
  • Mutual funds are the funds collected by multiple investors and then those are invested in different asset classes. To manage these mutual funds, we have fund managers who will invest on behalf of investors.
  • ETFs are nothing but a basket of securities just like mutual funds, but the only difference is they are traded on stock exchanges.

Market organization

Primary and secondary markets

The primary markets: the initial issuance of assets

The primary market is where new securities, including stocks, bonds, and other financial assets, are first issued by governments or corporations. The primary market is also referred to as the market for new issues.

Companies and governments raise money in the primary market by offering their securities to retail or institutional investors. The securities may be sold through a private placement or an initial public offering (IPO).

There are four main players in the primary market mainly for issuance of securities.
1) Corporates
2) Investors: institutional investors and individual investors
3) Corporate banks
4) Public accounting firms

The secondary markets: the exchange of assets

In the secondary market, fund managers and banks collaborate to trade securities between investors after they have already been issued. On one side, a fund manager may want to purchase securities of a public company, while on the other, a different fund manager may wish to sell those same securities. Investment bankers come between these clients to help facilitate these trades, and this trade is facilitated over the stock exchange. They provide equity research coverage to help fund managers make decisions about buying and selling those securities. And this secondary market trading makes markets liquid. This is what allows you to get in and out of security very easily.

Market infrastructure

Infrastructure providers are the companies which enable the transactions and functioning of different instruments. It means all the digital and physical infrastructure required for the investor is provided by the infrastructure provider. The few common examples of an infrastructure provider are the stock exchange, depositories, and registrar and transfer of agents.

  • Stock exchange: It is the platform where you can sell and buy securities. Here, with the help of a broker and the stock exchange two investors can buy and sell stocks without knowing each other. For example, The TSE is the largest stock exchange in Asia by market capitalization. It is located in Tokyo, Japan and has over 3,500 listed companies.
  • Depositories: These are the companies that store the stocks we buy in electronic form. We can store these stocks through our demat accounts. Depositories help you transfer stock and various other functions like checking the statements, portfolio holdings and transaction information etc. Generally investors directly do not interact with depositories but they approach through a broker who would invest on their behalf. For example: The DTC is one of the largest depositories in the world. It is located in New York City and holds over 3.5 million securities worth trillions of dollars.
  • Registrar and Transfer of Agents (RTA): just like depositories in case of stocks, RTA’s in case of mutual funds. All trades of mutual funds like subscription, redemption, and transfer, are recorded by an RTA. An RTA also helps mutual fund investors in providing their portfolio and statements to them.

Why should I be interested in this post?

As a student and prospective business management graduate, I think it is important to know the investment ecosystem. Firstly, investments play a vital role in the growth and success of companies. Companies need investments to fund their operations, expand their businesses, and create value for their shareholders. Therefore, understanding the investment ecosystem will enable management students to make informed decisions regarding investments that can help drive the growth of the companies they work for or manage in the future.

Related posts on the SimTrade blog

▶ All posts about financial techniques

   ▶ Marie POFF Film analysis: The Wolf of Wall Street

Useful resources

McKinsey (2017) Capital Markets Infrastructure: An Industry Reinventing Itself

Black rock The Investment Stewardship Ecosystem

About the author

The article was written in March 2023 by Nithisha CHALLA (ESSEC Business School, Grande Ecole Program – Master in Management, 2021-2023).

Active Investing

Youssef_Louraoui

In this article, Youssef LOURAOUI (Bayes Business School, MSc. Energy, Trade & Finance, 2021-2022) elaborates on the concept of active investing, which is a core investment strategy that relies heavily on market timing and stock picking as the two main drivers of financial performance.

This article is structured as follows: we introduce the concept of active investing in asset management. Next, we present an overview of the academic literature regarding active investing. We finish by presenting some basic principles on active investing.

Introduction

Active investing is an approach for going beyond matching a benchmark’s performance and instead aiming to outperform it. Alpha may be calculated using the CAPM framework, by comparing the fund manager’s expected return with the expected market return (Jensen, 1968). The search for alpha is done through two very different types of investment approaches: stock picking and market timing.

Stock picking

Stock picking is a method used by active managers to select assets based on a variety of variables such as their intrinsic value, the growth rate of dividends, and so on. Active managers use the fundamental analysis approach, which is based on the dissection of economic and financial data that may impact the asset price in the market.

Market timing

Market timing is a trading approach that involves entering and exiting the market at the right time. In other words, when rising outlooks are expected, investors will enter the market, and when downward outlooks are expected, investors will exit. For instance, technical analysis, which examines price and volume of transactions over time to forecast short-term future evolution, and fundamental analysis, which examines the macroeconomic and microeconomic data to forecast future asset prices, are the two techniques on which active managers base their decisions.

Review of academic literature on active investing

As fund managers tried strategies to beat the market, financial literature delved deeper into the mechanism to achieve this purpose. Jensen’s groundbreaking work in the early ’70s gave rise to the concept of alpha in the tracking of a fund’s performance to distinguish between the fund’s manager’s ability to generate abnormal returns and the part of the returns due to luck (Jensen, 1968).

Jensen develops a risk-adjusted measure of portfolio performance that quantifies the contribution of a manager’s forecasting ability to the fund’s returns. He used the measure to quantify the predictive ability of 115 mutual fund managers from 1945 to 1964—that is, their ability to produce returns above those expected given the risk level of each portfolio.

Not only does the evidence on mutual fund performance indicate that these 115 funds on average were unable to forecast security prices accurately enough to outperform a buy-and-hold strategy, but there is also very little evidence that any individual fund performed significantly better than what we would expect from mutual random chance. Additionally, it is critical to highlight that these conclusions hold even when fund returns are measured net of management expenses (that is assume their bookkeeping, research, and other expenses except brokerage commissions were obtained free). Thus, on average, the funds did not appear to be profitable enough in their trading activity to cover even their brokerage expenses.

Core principles of active investing

First principle: market efficiency varies between asset classes.

Investment information is not always readily available in all markets. For less efficient asset classes, an “active” management strategy offers a larger possibility to outperform the market, whereas a “passive” investment strategy may be more appropriate for highly efficient asset classes. In other words, there are compelling advantages for incorporating both active and passive techniques into an overall portfolio.

For example, Wall Street analysts cover a huge portion of US large size shares, making it harder to locate cheap companies. For this highly efficient asset class, a passive investment strategy may be more cost-effective in some cases. On the other side, emerging market equities are sometimes under-researched and difficult to appraise, providing an active manager with additional opportunities to identify mispriced companies. The critical point here is to notice the distinctions and then make the appropriate decisions.

Second principle: market efficiency varies across asset classes.

Within practically every asset class, active and passive management strategies can alternate as winners periodically. Even the most efficient asset classes can occasionally benefit from active management over passive. The reason is substantially distinct from the one stated in Principle One. Principle Two is related to the “Grossman-Stiglitz Paradox”: If markets are fully efficient, there is no reason to investigate them; yet markets can only be perfectly efficient for as long as they are regularly investigated. When investors run out of patience researching stocks in a highly efficient market, passive investment becomes appealing, reopening the door to opportunities for active research. This can result in an annual cycle of active/passive trends.

In some investing environments, active strategies have tended to benefit investors more, while passive strategies have tended to outperform in others. For instance, active managers may outperform more frequently than passive managers when the market is turbulent, or the economy is deteriorating. On the other way, when certain securities within the market move in lockstep or when stock valuations are more consistent, passive strategies may be preferable. Investors may gain from combining passive and active strategies in a way that exploits these insights, depending on the opportunity in various areas of the capital markets. Market conditions, on the other hand, vary constantly, and it frequently takes an intelligent eye to determine when and how much to skew toward passive rather than active investments (Morgan Stanley, 2021).

It’s worth noting that attaining consistently successful active management has historically been more challenging in some asset classes and segments of the market, such as large US company stocks. As a result, it may make sense to be more passive in certain areas and more active in asset classes and segments of the market where active investing has historically been more rewarding, such as overseas stocks in emerging markets and smaller U.S. corporations (Morgan Stanley, 2021).

Why should I be interested in this post?

If you are a business school or university undergraduate or graduate student, this content will help you in broadening your knowledge of finance.

Related posts on the SimTrade blog

   ▶ Youssef LOURAOUI Portfolio

   ▶ Youssef LOURAOUI Systematic and specific risk

   ▶ Youssef LOURAOUI Alpha

   ▶ Youssef LOURAOUI Factor Investing

   ▶ Youssef LOURAOUI Origin of factor investing

   ▶ Youssef LOURAOUI Markowitz Modern Portfolio Theory

   ▶ Jawati WALIA Capital Asset Pricing Model (CAPM)

Useful resources

Academic research

Grossman, S., Stiglitz, J., 1980. On the impossibility of Informationally efficient markets. The American Economic Review, 70(3), 393-408.

Lintner, J. 1965a. The Valuation of Risk Assets and the Selection of Risky Investments in Stock Portfolios and Capital Budgets. The Review of Economics and Statistics 47(1): 13-37.

Lintner, J. 1965b. Security Prices, Risk and Maximal Gains from Diversification. The Journal of Finance, 20(4): 587-615.

Mangram, M.E., 2013. A simplified perspective of the Markowitz Portfolio Theory. Global Journal of Business Research, 7(1): 59-70.

Markowitz, H., 1952. Portfolio Selection. The Journal of Finance, 7(1): 77-91.

Mossin, J. 1966. Equilibrium in a Capital Asset Market. Econometrica, 34(4): 768-783.

Sharpe, W.F. 1963. A Simplified Model for Portfolio Analysis. Management Science, 9(2): 277-293.

Sharpe, W.F. 1964. Capital Asset Prices: A Theory of Market Equilibrium under Conditions of Risk. The Journal of Finance, 19(3): 425-442.

Business analysis

Forbes, 2021. Active or Passive investing? Two principles provide the answer

JP Morgan Asset Management, 2021. Investing

Morgan Stanley, 2021. Active vs Passive management

About the author

The article was written in November 2021 by Youssef LOURAOUI (Bayes Business School, MSc. Energy, Trade & Finance, 2021-2022).

The IRR, XIRR and MIRR functions in Excel

The IRR function in Excel

Photo Léopoldine FOUQUES

In this article, Léopoldine FOUQUES (ESSEC Business School, Global Bachelor of Business Administration, 2017-2021) presents the IRR function in Excel to compute the internal rate of return of a series of cash flows.

About Excel

Excel is by far the most used financial modeling tool across the world to build models and perform analysis. Knowing which Excel function to use can help employees in the financial sector (financial analysts, fund managers, risk managers, traders, etc.) to work faster and build a more powerful model.

The internal rate of return (IRR)

Definition

The computation of the internal rate of return (IRR) is based on the net present value (NPV) of an investment. In financial modelling, an investment is represented by a series of cash flows: CF0, CF1, CF2, …, CFT. For a classic investment, the first cash flow, CF0, is negative (outflow) and the future cash flows, CF1, CF2, …, CFT are positive (inflows).

The net present value (NPV) of an investment is computed according to the following formula:

NPV formula

where r is the discount rate that takes into account the risk of the project.

The IRR corresponds to the value of the discount rate for which the NPV is equal to 0:

IRR

The IRR is the solution of a non-linear equation:

IRR

Use in finance

One of the most important functions is the Internal Rate of Return (IRR) function, as it’s an easy function to compare an investment’s return, based on a series of cash flows.

The function is very useful in financial modeling. Indeed, it’s frequently used to compare scenarios before deciding about a project. An example is when a company is presented with two opportunities: one is investing in a new factory and the second is expanding its existing factory.

By using IRR, we can estimate the IRR for each scenario and verify which one is higher than the average cost of capital of the business (the Weighted Average Cost of Capital or WACC) is a calculation of a firm’s cost of capital in which each category is proportionally weighted).

The Excel functions to compute the IRR

Building a math-based calculation is time-consuming and complicated, so Excel offers three functions for the calculation of the internal rate of return: IRR, MIRR, and XIRR.

You can download the Excel file below in which I provide an example to illustrate the use of the IRR to evaluate an investment.

Download the Excel file to compute the IRR of an investment

The IRR function

The IRR function uses one required argument and one optional:

  • The values: they represent the series of cash flows, including net income value and investments.
  • The guessed number for the expected internal rate of return. If omitted, the function will default to 0.1 (= 10%).

You can download the Excel file below in which I provide an example to illustrate the use of the IRR to evaluate an investment.

Download the Excel file to compute the IRR of an investment

Note that the IRR corresponds to a period rate. Monthly cash flows lead to a monthly IRR, quarterly cash flows lead to a quarterly IRR; and annual cash flows lead to an annual IRR. As, in practice, the standard is to work annual rates, monthly and quarterly IRR have to annualized.

Note that the use of the IRR function assumes that the period between each cash flow is the same (equal-size payment periods), for example one year.

From the IRR function to the XIRR function

If the period between each cash flow is not the same, the IRR function should not be used. It is the case with monthly cash flows as the months of the year may contain 28, 29, 30 or 31 days.

In this case, the XIRR function comes into play to calculate a correct internal rate of return, taking into consideration the periods of different sizes.

The XIRR function has three arguments:

  • The values
  • The dates for cash outflows and inflows.
  • The guessed number for the expected internal rate of return (optional argument).

You can download the Excel file below in which I provide an example to illustrate the use of the IRR and XIRR to evaluate an investment.

Download the Excel file to compute the IRR and XIRR of an investment

From the IRR function to the MIRR function (Modified Internal Rate of Return)

The MIRR function is quite the same as the IRR function, except that it takes into consideration both the cost of borrowing the initial investment funds (discount rate) and reinvestment rates for future cash flows.

In contrast to IRR, MIRR assumes that cash flows from a project are reinvested at the firm’s cost of capital (rate of return on a portfolio company’s existing securities).

To compute the MIRR, the Excel function uses the following parameters:

  • The values
  • The financial rate: the finance rate of interest paid
  • The reinvest rate: the interest rate earned from the reinvested profit
IRR

Where FV represents the Future Value of positive cash flows at the cost of capital for the company, PV represents the Present Value of negative cash flows at the financing cost of the company, and n represents the number of periods.

You can download the Excel file below in which I provide an example to illustrate the use of the IRR and MIRR to evaluate an investment.

Download the Excel file to compute the IRR and MIRR of an investment

Some of the accountants say that the MIRR function is less valid than the other because not all the flows are reinvested fully. Although we can use a less important interest rate to compensate the partial investment; but we think the best approach will be the inclusion of the three calculations (IRR, XIRR, and MIRR).

Limits of the IRR

The non-linear equation for obtaining the IRR may have one solution, several solutions or no solution according to the sequence of cash flows. These represent limits of the IRR as an investor would like one value when estimating its investment.

Another limit of the IRR as a decision criterion for investing is that the result is not in agreement with the decision criterion based on NPV, which represents the value created by the investment.

You can download the Excel file below in which I provide an example to illustrate the limit of the IRR when selecting investment when two projects are available.

Download the Excel file to select investments based on IRR and NPV

Related posts on the SimTrade blog

   ▶ Georges WAUBERT Bond valuation

   ▶ William LONGIN How to compute the present value of an asset?

   ▶ Jérémy PAULEN The IRR function in Excel

Useful resources

Mazars Excel IRR Function And Other Ways To Calculate IRR In Excel

About the author

The article was written in November 2021 by Léopoldine FOUQUES (ESSEC Business School, Global Bachelor of Business Administration, 2017-2021).