...

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.

Anchor
_Toc521324342 _Toc521324342

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.

Anchor
_Toc521324343 _Toc521324343

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:

...

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.

Anchor
_Toc521324344 _Toc521324344

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.

Anchor
_Toc521324345 _Toc521324345

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.

Anchor
RTF310032003000330030003a00 RTF310032003000330030003a00

Anchor
_Toc337537792 _Toc337537792

Anchor
_Toc361231389 _Toc361231389

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.

Anchor
_Toc521324346 _Toc521324346

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.

Anchor
RTF310033003400390036003a00 RTF310033003400390036003a00

Anchor
_Toc337537793 _Toc337537793

Anchor
_Toc361231390 _Toc361231390

Option to Null Partial Periods in the IRR field

Anchor
_Toc521324347 _Toc521324347

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.

Anchor
RTF360031003000350033003a00 RTF360031003000350033003a00

Anchor
_Toc337537794 _Toc337537794

Anchor
_Toc361231391 _Toc361231391

Variable Annualization in the IRR

Anchor
_Toc521324348 _Toc521324348

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.

Anchor
RTF340033003800360030003a00 RTF340033003800360030003a00

Anchor
_Toc337537795 _Toc337537795

Anchor
_Toc361231392 _Toc361231392

Variable Convergence in the IRR

Anchor
_Toc521324349 _Toc521324349

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:

...