Creating a bond coupon calendar

[Anonymous]

Hello,

I would like to make a coupon calendar for bond payments in power BI.  Below is an example of the data I'm working with, and what kind of visual I would like to create (clustered column, with monthly buckets).

• The maturity data gives the information required on when we should receive coupons.  Since all the bonds we buy are semi-annual, every coupon will be either the day and month of the maturity, or 6 months before/after it.  e.g. for the TGT bond below that matures 9/15/23 it's coupon payments would be on 3/15 and 9/15 any given year.  Please note this is not accounting for weekends or holidays as we do not need that kind of precision
• Continuing with the TGT example, from today (5/20/22) we should expect 3 more coupon payments.  is there a way to program this in power BI using only the single row with the maturity date?  I'm struggling to understand how this can be done with only one row of information and today's date
• The coupon payment would be amount*(coupon/2)

CP/FI	Issuer	Amount	Coupon	Maturity
FI	GOOG	$         1,000,000	2.63%	2/15/2023
FI	APPL	$         5,000,000	1.13%	5/1/2023
FI	APPl	$         5,000,000	2.70%	5/18/2023
FI	X	$         2,000,000	2.00%	8/8/2023
FI	TGT	$         5,000,000	2.40%	9/15/2023
FI	VZ	$       10,000,000	0.75%	3/22/2024
FI	WMT	$         2,000,000	2.85%	7/8/2024
FI	WMT	$         4,900,000	2.85%	7/8/2024
FI	BAC	$         1,000,000	4.20%	8/26/2024
FI	GS	$         3,000,000	4.00%	1/22/2025
FI	BAC	$         5,642,000	3.95%	4/21/2025
FI	PM	$         2,000,000	1.50%	5/1/2025
FI	MSFT	$         4,170,000	2.65%	11/3/2022

Thank you

[Anonymous]

@DataInsights  I have used this calendar for some time now succesfuly and wanted to add some functionality to it.

Right now it creates a table for everything that matures in the future, but I was hoping to add a slicer to look back in time at things that have already matured.  Instead of VAR vToday =TODAY() I have tried putting the following into it:

VAR vToday =

    (TODAY()+SELECTEDVALUE(Days[Days]))

This is just a table that looks up to 14 days back and is controlled by a slicer on the page.  It doesn't cause an error with the calendar but it also isn't updating the data.  Below it shows -3 but the first value is still 8/18/22 when I was hoping to have it be 8/15/22

Thanks in advance

[DataInsights]

@Anonymous,

Glad to hear it's been working successfully. Calculated tables and calculated columns don't recognize user-specified filters in Report view. Thus, when you select -3 days in the slider, it doesn't have any impact on the calculated table. Hopefully, this feature will become available soon.

[DataInsights]

@Anonymous,

This solution uses the GENERATE function to create a calculated table. The BondCoupon table is your sample data. The concept is to create a virtual date table that contains the payment dates for each bond. In the TGT example, three rows are generated since three more payments are expected. Let me know if leap year logic is required (i.e. a maturity date of February 29 in a leap year).

BondCouponCalendar = 
VAR vToday =
    TODAY ()
VAR vTable =
    GENERATE (
        BondCoupon,
        VAR vMaturityDate = BondCoupon[Maturity]
        VAR vMaturityMonthOffset =
            MONTH ( EDATE ( vMaturityDate, -6 ) )
        VAR vDates =
            CALENDAR ( vToday, vMaturityDate )
        VAR vPaymentDates =
            FILTER (
                vDates,
                DAY ( [Date] ) = DAY ( vMaturityDate )
                    && (
                        MONTH ( [Date] ) = MONTH ( vMaturityDate )
                            || MONTH ( [Date] ) = vMaturityMonthOffset
                    )
            )
        RETURN
            vPaymentDates
    )
VAR vResult =
    ADDCOLUMNS (
        vTable,
        "Coupon Payment", BondCoupon[Amount] * DIVIDE ( BondCoupon[Coupon], 2 )
    )
RETURN
    vResult

[Anonymous]

First off, Thank you for this, this is kind of lbowing my mind.  there is a lot in here that I have to learn. 

I entered this and adjusted it for the actual table names and got an error "The start date in Calendar function cannot be later than the end date"

I am assuming this is because there are entries in my data that have already matured but do not get removed from the data set.  So there are some with maturity dates in the past.  Is there a way to modify this to only evaluate future maturity dates?

[DataInsights · Accepted solution]

@Anonymous,

I added a FILTER function to BondCoupon to exclude past maturity dates. Let me know if this resolves the issue.

BondCouponCalendar = 
VAR vToday =
    TODAY ()
VAR vTable =
    GENERATE (
        FILTER ( BondCoupon, BondCoupon[Maturity] >= vToday ),
        VAR vMaturityDate = BondCoupon[Maturity]
        VAR vMaturityMonthOffset =
            MONTH ( EDATE ( vMaturityDate, -6 ) )
        VAR vDates =
            CALENDAR ( vToday, vMaturityDate )
        VAR vPaymentDates =
            FILTER (
                vDates,
                DAY ( [Date] ) = DAY ( vMaturityDate )
                    && (
                        MONTH ( [Date] ) = MONTH ( vMaturityDate )
                            || MONTH ( [Date] ) = vMaturityMonthOffset
                    )
            )
        RETURN
            vPaymentDates
    )
VAR vResult =
    ADDCOLUMNS (
        vTable,
        "Coupon Payment", BondCoupon[Amount] * DIVIDE ( BondCoupon[Coupon], 2 )
    )
RETURN
    vResult

[Anonymous]

So far this does what I need it to do, thank you so much!