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

irr logo
msofficeshare logo

Requirement:  Immediate to power Excel user, Visual Basis Knowledge

File to download:  Bond Yield Calculator.xlsm

Related Articles:  Use Goal Seek command to reverse engineer desired resultsCreate a Dropdown List by using Data Validation Command

Internal Rate of Return (abbreviated as “IRR”, also called “Yield”).  In this article, “Internal Rate of Return” “IRR”, and “Yield” will be used interchangeably.  IRR is one of the most important tools in the financial world.  It measures how well an investment or a project performs over time.  It is an essential part of benefit/cost analysis.  When combining with scenario analysis, it helps determine if a project should carry out.  It also allows the comparison among different investment opportunities.  In today’s world, many deals involve billion dollars investment and a 0.1% difference in the IRR already means a difference of million dollars.

Bond market is essential to the economy and investors.  Bonds are important investment tools for multi-billion dollars companies such as pension fund companies, insurance companies, mutual funds, hedge funds, private equities, and investment trusts.  Bonds are also appealing more and more to private investors who deem bonds are relatively safer investments than stocks, especially for aging populations.

The calculation of bond yields is extremely important for financial reporting and tax reporting.  Some corporate tax reporting, especially the Real Estate Mortgage Investment Conduits (REMIC) tax reporting requires such yield calculations to be very precise because it affects the calculations of Original Issue Discount (OID) and Premium amortizations.  This in turn affects the periodic taxable income calculations and eventually how much tax the taxpayers must pay to IRS. 

The difficulties of IRR calculation increase substantially because bonds have different accrual basis.  Most bonds are paid monthly.  Some are paid quarterly, semi-annually, or annually.  If the bonds are paid monthly, there are six types of accrual basis:

  1. 30/360
  2. 30/365
  3. 30/Actual
  4. Actual/360
  5. Actual/365
  6. Actual/Actual

Similar accrual basis may apply to bonds that are paid quarterly, semi-annually, or annually.  The most popular types of bonds that are paid monthly are 30/360, Actual/Actual, and surprisingly, Actual/360 which is a hybrid type of accrual basis.

Excel offers two functions for the calculation of Internal Rate of Return: IRR and XIRR.  The IRR function is primarily for investment vehicle with annual payments and 30/360 accrual basis.  It has very limited use.  The syntax is:

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.

For more information on how to use IRR function, please refer to the Microsoft Support web site for more information. (https://support.office.com/en-us/article/IRR-function-b5752d46-e623-494b-9191-b8ca75cb9c76)

 

The XIRR is much more useful and powerful.  It does the calculation based on both cash flow and dates. It allows payments on daily, weekly, monthly, annual, and even with irregular dates.  It calculates the Internal Rate of Return based on Actual/365 accrual basis.  The formula that is equivalent to XIRR function is as below.

pic1 irr

The formula indicates that the Yield is raised to power of the date difference over 365 to calculate the present value of individual payments.  Therefore, there is no other choice for the user when he encounters bonds with different accrual basis.  The syntax of the XIRR function is as below:

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 that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values 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 may occur in any order. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. .
  • Guess    Optional. A number that you guess is close to the result of XIRR.

For more information on how to use XIRR function, please refer to the Microsoft Support web site for more information. (https://support.office.com/en-za/article/XIRR-function-2ecd4c99-7245-472a-93c2-60ab1ce60dd0)

 

Luckily, we are not totally hopeless if we need to calculate the IRR with different accrual basis.  We have two options: use the Goal Seek function in Excel or write a VB module to automate the calculations.  Please refer to another article that I wrote (Use Goal Seek command to reverse engineer desired results) for more information how to use goal seek function.

This article illustrates how to write a VB module to automate such process.  I prefer using a VB module in the calculations of IRR in this case because I used to calculate the IRR for tens of bonds at the same time.  I just added one more loop to the VB module to make it repeat such calculations in a row.  In addition, there are not too many people familiar with the Goal Seek function and it is time-consuming to teach every newly-hired person every time.  You just need to write a VB module and put a button in your Excel worksheet to activate such VB module and thus making life much easier for me.  My initial investment (i.e. the time to write such VB module) paid off quickly (i.e. the time I need to train every new person to use the Goal Seek functions).  The IRR of writing such VB module must be 10,000% compared with the initial investment.

Please download the attached file (Bond Yield Calculator.xlsm).  By downloading the file, you agreed to waive all legal liabilities against me that pertaining to the downloaded file.  The below illustrations are all based on this file.  Some formulas are advanced and a bit complicated.  You need to be an intermediate to power user of Excel to understand some of the formula.  It will be beyond the scope of this article to explain all the formulas to the readers.

 

Step 1:  Create the “Cash Flow” worksheet.  Use the Data Validation function in Excel to create a drop down menu.  Please refer to this article (Create a Dropdown List by using Data Validation Command) to see how to create such drop down menu.  Put in your projected data (dates and cash flow).  These projections must be done by you.  You may want to put in formulas and may need to split cash flow into interest and principal, depending on what you need.

pic2 irr

Step 2:  Create the “Calculator” worksheet.  This is where the main calculation is.  The formula works hand-in-hand with the VB module.  The VB module grabs the numbers from this worksheet and put back a calculated number to the worksheet.  This process will go through several iterations until the correct IRR is calculated.

pic3 irr

The key part of the calculation of the “Calculator” worksheet is the number of dates calculations based on the selected accrual basis.  For example, the 30/360 will have 30 as numerator and 360 as denominator.  If it is 30 as numerator, the number of days will be calculated using DAYS360 function in Excel, otherwise, the numbers of days will be the difference between the current date and the past date.  After the numbers of dates for both numerator and denominator are calculated, the periodic cash flow will be discounted based on something similar to the XIRR formula.  However, the portion that “raise-to-the-power-of” will depend on the selected accrual basis.

 

Step 3: Write the Visual Basic module to automate the iteration.  The VB module does exactly what XIRR function or the Goal Seek function does.  The Internal Rate of Return will be approximated by iterations.  Since 99.99% of bonds will have their Internal Rate of Return within 100% and negative IRR is not likely, the calculated yield will be ranged from 0% (lower limit) to 100% (upper limit).  If you want to modify this VB module for other investment purpose, you can change the lower or upper limit in the VB module.  The VB module will loop and test the yield to see if the sum of the calculated present values (PV) of all discounted cash flow will equal to the initial investment.  Below example explains how the iteration logic works:

  • The initial value to be tested will be 50% = (0% + 100%) / 2.
  • If 50% is too high (i.e. the sum of periodic PV < Initial Investment), 50% now becomes the upper limit.  The lower limit will be unchanged (0%).  So next value to be tested will be 25% (0% + 50%) / 2.
  • If 25% is still too high, 25% now becomes the upper limit.  The lower limit will be unchanged (0%).  So next value to be tested will be 12.5% (0% + 25%) / 2.
  • If 12.5% is too low (i.e. the sum of periodic PV > Initial Investment),, 12.5% will become the new lower limit.  The upper limit will stay the same (25%).  So next value to be tested will be 18.75% (12.5% + 25%) / 2.
  • If 18.75% is too high, 18.75% now becomes the upper limit.  The lower limit will be unchanged (12.5%).  So next value to be tested will be 15.625% (12.5% + 18.75%) / 2.
  • This iteration process will continue until the sum of the present value of the projected cash flow equals the initial investment (or within the error allowance).  However, if there is a problem with the data or the yield is larger than 100% or less than 0%, the iteration will stop at 50 loops and prompt you to look into the correctness of the data.
pic4 irr

Below is the entire codes of the VB module.

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

    Dim Program_Worksheet As String, Program_Cashflow As String, Program_Date As String

 

    Sheets("Calculator").Activate

    Program_Worksheet = ActiveSheet.Range("Worksheet_Name").Value

    Program_Cashflow = ActiveSheet.Range("Cashflow_Range").Value

    Program_Date = ActiveSheet.Range("Date_Range").Value

   

    Range("Date_Cleared").Select

    Selection.ClearContents

   

    Range("CF_Cleared").Select

    Selection.ClearContents

   

    Sheets(Program_Worksheet).Activate

    Range(Program_Cashflow).Select

    Selection.Copy

    Workbooks("Bond Yield Calculator.xlsm").Worksheets("Calculator").Activate

    ActiveSheet.Range("Cashflow").Select

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

 

    Sheets(Program_Worksheet).Activate

    Range(Program_Date).Select

    Selection.Copy

    Workbooks("Bond Yield Calculator.xlsm").Worksheets("Calculator").Activate

    ActiveSheet.Range("Accrual_Date").Select

    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _

        SkipBlanks:=False, Transpose:=False

 

    Counter = 1

    U_Limit = 1    'Calculated Yield should be ranged from 0% to 100%

    L_Limit = 0    'Calculated Yield should be ranged from 0% to 100%

    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

           

            Sheets(Program_Worksheet).Activate

            Range("A1").Select

            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 the VB module (Macro) to make the worksheet do the IRR calculations.  Please also make sure that the Excel calculation mode is “automatic” instead of “manual”.  If you pick the Actual/365 accrual basis, the calculated value by the VB module should be very close to the value calculated by XIRR, if not exactly the same.

The current model only applies to an investment on Time 0 (negative amount) because that’s the way for all the bonds work.  If you want to use the model for something else, you need to do a small modification.  The present value (PV) amount needs to be an input figure rather than a formula as in the current model.  If you understand how the model works, it will take less a minute to do so.