About Calculation Logic

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.

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.

Open

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.

Open

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. Eagle PACE uses two methods, a bi-section algorithm or a goal seeking interval 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.  

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

Directional Exponential Resolver

The Directional Exponential Resolver (Formerly known as Goal Seek Interval) solves by calculating the NPV of the beginning market value and cash flows with a methodical series of IRR input guesses. This result is compared with the current market values and results in an NPV difference. The correct IRR results in an NPV difference of 0.

The Directional Exponential Resolver begins by determining if the IRR solution is positive or negative. This is done by inputting a 0, 10, and -10. The NPV differences at 10 and -10 are compared to the NPV difference when using 0. This establishes the expected direction of the solution.

If a negative solution is established then each guess is decreased in increments of -10. When a guess results in the NPV difference having the opposite sign (- vs. +) from previous NPV difference than the solution IRR exists within those 2 guesses. Now a narrowing of the range begins. The IRR guesses reverse direction and the increment between each guess is the prior increment divided by 10. This process of seeking a sign change in the NPV difference with each guess, reversing the guess direction, and reducing the guess increment continues until the NPV difference is exactly 0. If -100 is reached without a solution or an NPV difference sign change then an underflow is declared and the process begins to seek a positive solution.

If a positive solution is established the guesses are first increased by increments of 10 up to 100, then 1000 up to 10,000 and by a factor of 10 up until the limit of 1,000,000,000. When a guess results in a sign change in the NPV difference from the previous NPV difference than the solution IRR exists within those 2 guesses. Now a narrowing of the range begins. The IRR guesses reverse direction and the increment between each guess is the prior increment divided by 10. This process of seeking a sign change with each guess, reversing the guess direction, and reducing the guess increment continues until the NPV difference is exactly 0. if 1,000,000,000 is reached with no sign change or a solution an overflow is declared.

• Upper Limit. 1,000,000,000 percent

• Lower Limit. -100 percent

• Precision. Minimum = 0.000001 (6 digits), Maximum = 0.00000000001 (11 digits)

• Iterations. 500

• Initial Guess. 0