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:
- 30/360
- 30/365
- 30/Actual
- Actual/360
- Actual/365
- 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.
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.
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.
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.
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.
