About Calculation Logic

The IRR is designed to:

Set known calculation limits that return null results if violated
Support periods greater than one month
Support both cumulative and annual IRR calculations
Enable you to verify all IRR results in Microsoft Excel

Bisection is used as the sole IRR calculation method. A null is returned for IRRs outside of the range 1,000,000,000 percent and 100 percent. Using an upper limit that is not a function of the number of days in the period and periods greater than a month are supported. An additional option in the IRR calculation supports annual IRRs. All returns calculated using IRR can be tied out in Excel by using Goal Seek, Solver, and XIRR.

On this page

IRR Formula

The formula for the basic IRR calculation is:
MVE = MVB x (1 + IRR) + CF₁ x (1 + IRR) ^W1 … + CF_n x (1+ IRR) ^Wn
where:
MVE is market value ending
MVB is market value beginning
IRR is cumulative Internal Rate of Return
CF is cash flow amount
W is weight (Number of Days Cash Flow is Present) / (Num of Days in Period)
As always, the time-of-day assumption is factored into the number of days present.

Input Validation

Some cases cannot be solved using IRR and as a result, return null values. A check of calculation inputs returns a null value in the following scenarios:

No data found
BMV and EMV are null and there is not at least one positive and one negative cash flow
Positive BMV, positive cash flows, and negative EMV
Negative BMV, negative cash flows, and positive EMV
Positive BMV, no cash flows, and negative EMV
Negative BMV, no cash flows, and positive EMV
No BMV, positive cash flows, and no EMV
No BMV, negative cash flows, and no EMV

An input validation in the IRR calculation avoids solving for a zero return. If the beginning market value plus sum of cash flows is equal to the ending market value, the Bisection algorithm is bypassed and a return of zero is returned.

Calculation Options

Two options provide different weighting methods and support annualized IRRs. The Partial Period Option field has three options: None, Calculate Partial Periods, and Null Partial Periods. In addition, the Annualize check box controls annualization.

Calculate Partial Periods

When the basic IRR is calculated, the number of days in the period is determined by the report begin and end dates. This creates consistent day weights for all the calculations on the report. For example, if security A is held for the entire 30-day period and has a return of 10 percent, the report IRR is 10 percent. However, if security B is held for three days and has a 10 percent return, the report IRR is much higher than 10 percent due to the day weighting of the cash flows.

While this basic IRR is widely accepted, analysts also need the ability to look at the return of securities based on the amount of time that each was held. For example, if security A has a 10 percent return over 30 days and security B is held for three days and has a 10 percent return, the report IRR is 10 percent in both cases.

The Calculate Partial Periods option defines the number of days for each security based on the availability of BMV, EMV, and cash flows for each security. If one security has BMV and EMV, then they are used as begin and end dates for the weighting. However if the BMV or EMV are missing, the weighting uses the first or last cash flow dates for the weighting.

The basic logic is:

If BMV = Null, Then BeginDate = FirstCashflowDate - 1, Else BeginDate = ReportBeginDate
If EMV = Null, Then EndDate = LastCashflowDate, Else EndDate = ReportEndDate

You can select this option from the Partial Period Option field in the IRR field dialog box. See the following figure.

Option to Calculate Partial Periods in the IRR Field

Note that if the beginning position is not found, the Beginning of Day assumption does not adjust the day weight by adding one day and the Middle of Day assumption does not add half a day. Without this additional logic, the first cash flow has a weight greater than 1.

Null Partial Periods

Analysts need the ability to easily identify and exclude securities that are not held for the entire reporting period from the report. This option allow you to report IRRs only for securities that are held for the entire reporting period. For example, if security A is held for the entire 30-day period and has a return of 10 percent, the report IRR report is 10 percent. However, if security B is held for three days and has a 10 percent return, the report IRR is null.

The Null Partial Periods option returns a null whenever the security does not have a BMV or EMV. The basic logic is:

If (BMV = Null) or (EMV = Null), Then NULL, Else IRR

You can select this option from the Partial Period Option field in the IRR field dialog box. See the following figure.

Option to Null Partial Periods in the IRR field

Annualize

Analysts need the ability to calculate the annual IRR if the period is greater than 365 days. The Annualize option annualizes the IRR if the number of days in the reporting period is greater than 365. However, if used with the Calculate Partial Periods option in the Partial Period Option field, only securities that are held for more than 365 days are annualized. The basic logic changes the weighting factor in the IRR to:

W:Weight = (Number of Days Cash Flow is Present)/(365.25)

The IRR calculator allows annualization based on any number of days. The Annualize check box and Number of days in year field in the IRR field dialog box allow you to enter any number to be used in annualization. See the following figure.

Variable IRR Convergence

The general principle behind the IRR is to find a single rate that, when applied equally to the beginning market value and cash flows, equals the ending market value. PACE uses a bi-section algorithm to find the best rate through trial and error. The accuracy of the guess can be determined by comparing the future value (sum of the beginning market value multiplied by the rate and each cash flow multiplied by the rate) to the present value (the ending market value). If the difference is zero (0.00000000000), you have the exact rate.

You can lower the convergence threshold to anywhere from one (0.1) to six (0.000001) digits of convergence. Note that lowering the number of digits of convergence causes less accurate Internal Rates of Return to be calculated, so it is important to analyze the effect of any changes. See the following figure.

Variable Convergence in the IRR

Bisection

In addition to more strictly defined parameters, the enhanced Bisection algorithm continues to process if a guess causes an overflow, underflow, or numerical exception. Bisection needs to be started in the right direction and be able to change direction if necessary. The IRR calculation uses the midpoint of the upper and lower calculation limit to determine if it should find a positive or negative return. If the value is greater than the EMV, a positive return is assumed and if less than EMV, a negative return is assumed. Each guess should bring the IRR guess closer to converging with the real IRR. If the guess begins to diverge, the IRR automatically goes back to the last convergence and change direction. Employing this directional change allows bisection to solve for IRRs that are in the opposite direction of the initial guess.

The Bisection algorithm can find a result in cases where the difference in one guess is greater than zero and the difference in the next guess is less than zero. In this case, the Bisection algorithm adjusts automatically, both the upper and lower limits to search for a result between these two guesses. The Bisection parameters in IRR are:

Upper Limit. 1,000,000,000 percent
Lower Limit. -100 percent
Precision. Minimum = 0.000001 (6 digits), Maximum = 0.00000000001 (11 digits)
Iterations. 500
Guess. 0