How to calculate IRR without Excel

What is IRR?

The Internal Rate of Return [IRR] is defined as the compounded rate of return on an investment.

Given a specified range of dates, the IRR is the implied interest rate at which the initial capital investment must have grown to reach the ending value from the beginning value.

Table of Contents

How to Calculate IRR [Step-by-Step]
IRR Formula
IRR Excel Calculation: XIRR vs. IRR Function
IRR Limitations in Returns Analysis: Private Equity LBO Example
Positive and Negative IRR Levers
How to Increase [or Decrease] the Internal Rate of Return
IRR Calculator – Excel Model Template
Step 1. LBO Equity Investment Assumptions [Initial Cash Outflow]
Step 2. Track Cash Inflows / [Outflows] in LBO Holding Period
Step 3. Calculate Implied IRR Using XIRR Excel Function
Step 4. Calculate Multiple of Money [MoM]
Step 5. Complete LBO Returns Analysis [IRR vs. MoM]

How to Calculate IRR [Step-by-Step]

The internal rate of return [IRR] metric estimates the annualized rate of return that an investment is going to yield.

Unlike the multiple of money [MoM], another metric tracked by investors to measure their returns, the IRR is considered to be “time-weighted” because it accounts for the specific dates that the cash proceeds are received.

The higher the IRR, the more profitable a potential investment will likely be if undertaken all else being equal.

The manual calculation of the IRR metric involves the following steps:

Step 1 → The future value [FV] is divided by the present value [PV]
Step 2 → The amount is raised to the inverse power of the number of periods [i.e., 1 ÷ n]
Step 3 → From the resulting figure, one is subtracted

IRR Formula

The formula for calculating the internal rate of return [IRR] is as follows:

Internal Rate of Return [IRR] = [Future Value ÷ Present Value]^[1 ÷ Number of Periods] – 1

Conceptually, the IRR can also be thought of as the rate of return wherein the NPV of the project or investment equals zero.

The alternative formulas, most often taught in academia, involve backing out the IRR for the equation to hold true [and require using a financial calculator].

0 = CF t = 0 + [CF t = 1 ÷ [1 + IRR]] + [CF t = 2 ÷ [1 + IRR]^2] + … + [CF t = n ÷ [1 + IRR]^n]

Or, an alternative method to calculate the IRR is the following:

0 = NPV Σ CFn ÷ [1 + IRR]^n

IRR Excel Calculation: XIRR vs. IRR Function

The XIRR Excel function is preferable over the IRR function as it has more flexibility by not being restricted to annual periods. The drawback to the IRR function is that Excel assumes each cell is separated by precisely twelve months, which is not always the case.

Under XIRR, daily compounding is assumed, and the effective annual rate is returned. But for the IRR function, the interest rate is returned assuming a stream of equally spaced cash flows.

The XIRR function can handle complex scenarios that necessitate taking into account the timing of each cash inflow and outflow [i.e., the volatility of multiple cash flows].

IRR Limitations in Returns Analysis: Private Equity LBO Example

The internal rate of return [IRR] metric is imperfect and cannot be used as a standalone measure due to being highly sensitive to the timing of the cash flows.

Thus, the IRR can potentially be misleading in its portrayal of returns under certain circumstances wherein a greater proportion of the cash flows are received earlier.

For example, if a private equity firm were to issue itself a dividend soon after a leveraged buyout [LBO], i.e. a dividend recapitalization [or recap], the payment would increase the IRR to the fund regardless of whether the multiple-of-money [MoM] meets the required returns hurdles – which can cause the IRR to be potentially misleading here.

Therefore, the implied IRR from an investment could be impressively high, yet stem from the shorter holding period which caused the returns to be artificially inflated and unsustainable if the holding period were to be hypothetically extended longer.

With that said, the IRR cannot be singularly used to make an investment decision, as in the case of most financial metrics.

Let’s say an investment would yield a 30% IRR if sold on the present date, which at first glance sounds great. But from a more in-depth look, if the same investment achieves only 1.5x MoM, this makes the return far less impressive, and the high IRR is attributed more towards a quicker return of capital rather than substantial growth in the size of the investment.

Nonetheless, the IRR tends to be the most benchmarked marketing metric for the performance of investment funds and influential with regard to the ability for firms to meet [or surpass] their capital raising efforts for their next fund from existing and new limited partners [LPs].

Because of that factor, most firms pay a significant amount of attention to their fund’s IRR.

Positive and Negative IRR Levers

How to Increase [or Decrease] the Internal Rate of Return

The following factors are the main contributors which drive the internal rate of return [IRR]:

Positive IRR Levers	Negative IRR Levers
Earlier Extraction of Exit Proceeds [e.g., Dividend Recapitalization, Monitoring Fees]	Delayed Receipt of Exit Proceeds [e.g., Sale Delay Caused by Lack of Interested Buyers]
Increased Free Cash Flows from Strong Revenue and EBITDA Growth	Reduced Free Cash Flows [FCFs] and Profit Margins
Multiple Expansion [i.e. Exiting at a Higher Multiple than the Entry Multiple]	Multiple Contraction [i.e., Lower Exit Multiple than Purchase Multiple]

Regardless, the internal rate of return [IRR] and MoM are both different pieces of the same puzzle, and each comes with its respective shortcomings.

The takeaway is that the magnitude by which an investment grows matters, but the pace at which the growth was achieved is just as important.

IRR Calculator – Excel Model Template

We’ll now move to an internal rate of return [IRR] modeling exercise, which you can access by filling out the form below.

Step 1. LBO Equity Investment Assumptions [Initial Cash Outflow]

Suppose a private equity firm made an equity investment of $85 million in 2022 [Year 0].

Year 0 = –$85 million Cash Outflow

The value of the initial investment stays unchanged regardless of which year the firm exits the investment.

Since the investment represents an outflow of cash, we’ll place a negative sign in front of the figure in Excel.

Step 2. Track Cash Inflows / [Outflows] in LBO Holding Period

Afterward, the positive cash inflows related to the exit represent the proceeds distributed to the investor following the sale of the investment [i.e. realization at exit].

Here, our simple assumption is that the exit proceeds will increase by a fixed amount of $25 million each year, starting from the initial investment amount of $85 million.

Annual Growth in Sponsor Proceeds [$] = +$25 million per Year

Therefore, the exit proceeds in Year 1 are $110 million while in Year 3, the proceeds come out to $160 million.

Step 3. Calculate Implied IRR Using XIRR Excel Function

Once our table depicting the cash outflow in Year 0 [the initial investment] and the cash inflows [the exit proceeds] at different dates in the holding period is done, we can calculate the IRR and MoM metrics from this particular investment.

To determine the internal rate of return on an investment, follow the steps below.

Step 1 → Start by listing out the value of all of the cash inflows/[outflows] and the corresponding dates of the date of receipt
Step 2 → Use the XIRR Excel function [“= XIRR [Range of CFs, Range of Timing]”]; the first input requires you to drag the selection box across the range of cash inflows/[outflows]
Step 3 → For the second input, do the same across all of the corresponding dates.

To reiterate from earlier, the initial cash outflow [i.e. sponsor’s equity contribution at purchase] must be entered as a negative number since the investment is an “outflow” of cash.

Note that for the formula to work and be dragged down, the date selection must be anchored in Excel, i.e. fixed.

While the two main factors are the entry investment and exit sale proceeds, other inflows such as dividends or monitoring fees [i.e. the services related to portfolio company consulting] must be input as positives, as well as any additional equity injections later on in the holding period.

Step 4. Calculate Multiple of Money [MoM]

In order to calculate the multiple-on-money [MoM], we’ll calculate the sum of all the positive cash inflows from each holding period.

We must then divide that amount by the cash outflow in Year 0.

For instance, assuming a Year 5 exit, the exit proceeds of $210 million are divided by -$85 million to get an MoM of 2.5x.

MoM = $210 million ÷ [$85 million] = 2.5x

Step 5. Complete LBO Returns Analysis [IRR vs. MoM]

Based on the completed output for our exercise, we can see the implied Year 5 exit IRR is 19.8% whereas the MoM is 2.5x.

Exit Year 5 IRR = 19.8%

If we were to calculate the IRR using a calculator, the formula would take the future value [$210 million] and divide by the present value [-$85 million] and raise it to the inverse number of periods [1 ÷ 5 Years], and then subtract out one – which again gets us 19.8% for the Year 5 internal rate of return [IRR].

Master LBO Modeling Our Advanced LBO Modeling course will teach you how to build a comprehensive LBO model and give you the confidence to ace the finance interview.

Learn More

How do you manually calculate IRR?

It is calculated by taking the difference between the current or expected future value and the original beginning value, divided by the original value, and multiplied by 100.

How do you find IRR on normal calculator?

Calculating IRR with a Financial Calculator Example.

Step 1: Press the Cash Flow [CF] Button. This starts the Cash Flow Register when you enter your initial investment. ... .

Step 2: Press the Down Arrow Once. The calculator should show CF1. ... .

Step 3: Press the Down Arrow Twice. ... .

Step 4: Repeat. ... .

Step 5: Press the IRR Key..

How do you calculate IRR simple example?

Example: You invest $500 now, and get back $570 next year..

You invest $500 now, so PV = −$500.00. Money In: $570 next year. ... .

PV = $518.18 [to nearest cent] And the Net Amount is:.

Net Present Value = $518.18 − $500.00 = $18.18..

What can I use instead of IRR?

ROI is more common than IRR, as IRR tends to be more difficult to calculate—although software has made calculating IRR easier. ROI indicates total growth, start to finish, of an investment, while IRR identifies the annual growth rate.