Internal Rate of Return (IRR) Calculations with different Accrual Basis

Internal Rate of Return (IRR, also called "Yield") is one of the most important tools in the financial world. It measures how well an investment or a project performs over time and is an essential part of benefit/cost analysis.

Excel's Built-in IRR Functions

Excel offers two functions for calculating Internal Rate of Return: IRR and XIRR.

IRR is primarily for investments with annual payments and 30/360 accrual basis and has very limited use:

IRR(values, [guess])

• values — Required. An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
• guess — Optional. A number that you guess is close to the result of IRR.

XIRR is much more useful and powerful. It calculates the Internal Rate of Return based on Actual/365 accrual basis and allows payments on daily, weekly, monthly, annual, and even irregular dates:

XIRR(values, dates, [guess])

• values — Required. A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment at the beginning of the investment. The series must contain at least one positive and one negative value.
• dates — Required. A schedule of payment dates that corresponds to the cash flow payments. Dates should be entered using the DATE function.
• guess — Optional. A number that you guess is close to the result of XIRR.

Handling Different Accrual Bases

The difficulties of IRR calculation increase substantially because bonds have different accrual bases. If the bonds are paid monthly, there are six types of accrual basis:

• 30/360
• 30/365
• 30/Actual
• Actual/360
• Actual/365
• Actual/Actual

Since XIRR only handles Actual/365, you need either the Goal Seek function or a VBA module to automate IRR calculations for other accrual bases.

VBA Iteration Module

The VBA module automates what XIRR or Goal Seek does — approximating IRR through iterations. The yield is ranged from 0% (lower limit) to 100% (upper limit). The module loops and tests the yield until the sum of the calculated present values of all discounted cash flow equals the initial investment.

How the iteration logic works:

1. The initial value to be tested will be 50% = (0% + 100%) / 2.
2. If 50% is too high (sum of periodic PV < initial investment), 25% becomes the next test value.
3. Each iteration narrows the range by half until the sum of present values equals the initial investment within the error allowance.
4. If the yield cannot be calculated within 50 loops, the module prompts you to check the data.

Sub Yield_Calculator()

Dim U_Limit As Double, L_Limit As Double, Err_Allowance As Double, Error As Double
Dim Discrepancy As Double, Yield As Double, Prev_Yield As Double
Dim Counter As Integer, Max_Loop As Integer

Sheets("Calculator").Activate

Counter = 1
U_Limit = 1
L_Limit = 0
Yield = (U_Limit + L_Limit) / 2
Err_Allowance = 0.000000001
Max_Loop = 50

Do
    Prev_Yield = Yield
    Range("Yield_Calc").Value = Yield
    Discrepancy = ActiveSheet.Range("Diff").Value
    Error = ActiveSheet.Range("Error").Value
    Counter = Counter + 1

    If Discrepancy > 0 Then
        Yield = (Yield + L_Limit) / 2
        U_Limit = Prev_Yield
    Else
        Yield = (Yield + U_Limit) / 2
        L_Limit = Prev_Yield
    End If

    If Error < Err_Allowance Then
        Range("Yield_Calc").Value = Yield
        Range("Counter").Value = Counter
        Exit Sub
    End If

    Range("Counter").Value = Counter
Loop Until Counter = Max_Loop

MsgBox ("The program could not calculate the Yield through 50 loops. Please check your numbers")

End Sub

You need to enable macros and ensure the Excel calculation mode is set to "Automatic" for the module to work correctly. If you select Actual/365 accrual basis, the calculated value should be very close to the value calculated by XIRR.