Bond Cashflow Dates

[felixschreibt]

Hi all,

I m looking for a code or formula that could estimate bond cash flow dates based on maturity date.

Mat Date = 29/03/2033
Start Date = 21/06/2024

Cashflow Frequency = 1 (annual)

Output: 29/03/2033, 29/03/2032...[...]...29/03/2025

Output varies with CF frequency (i.e. semiannally) and start date.

Any chance to get that into VBA?
Thx
F

 

[arun_eagertolearn]

Hi

Can you please let know the calculations to be done

 

[alansidman]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Maturity ", type date}, {"Start", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Generate(()=>[x=#date(2024,6,29),i=0], each [i]<105, each [i=[i]+1,x=Date.AddMonths([x],1)], each [x])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Nr Months", Int64.Type}}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Changed Type1", "Custom")
in
    #"Expanded Custom"

 

[felixschreibt]

Im actually looking for a vba code, sth like this:

Sub cashflows()

Dim start As Date
Dim maturitiy As Date
Dim num_cf As Integer

start = Cells(2, 1).Value
maturity = Cells(2, 2).Value
num_cf = DateDiff("yyyy", start, maturity)
Cells(2, 3) = num_cf

For i = 1 To num_cf
Cells(2, 3 + i) = DateAdd("yyyy", -1, maturity)

Next i

End Sub

 

[felixschreibt]

Im actually looking for a vba code, sth like this:

Sub cashflows()

Dim start As Date
Dim maturitiy As Date
Dim num_cf As Integer

start = Cells(2, 1).Value
maturity = Cells(2, 2).Value
num_cf = DateDiff("yyyy", start, maturity)
Cells(2, 3) = num_cf

For i = 1 To num_cf
Cells(2, 3 + i) = DateAdd("yyyy", -1, maturity)

Next i

End Sub

Problem here is that I dont know how to also construct the cashflow date. The code writes maturity date - 1 year in all cells. So I need a second loop or function for maturity date after the first loop has ended.

 

[felixschreibt]

Problem here is that I dont know how to also construct the cashflow date. The code writes maturity date - 1 year in all cells. So I need a second loop or function for maturity date after the first loop has ended.

I think I almost got it - what I dont get is why is Excel posting 01.06.1900 in cells (2,3) which has nothing to do with the mat or start date. In my example num_cf is 6. Any idea?

Sub cashflows()

Dim start As Date
Dim maturitiy As Date
Dim num_cf As Integer

start = Cells(2, 1).Value
maturity = Cells(2, 2).Value
num_cf = DateDiff("yyyy", start, maturity)
Cells(2, 3) = num_cf

For i = 1 To num_cf
Cells(2, 3 + i) = DateAdd("yyyy", -num_cf + i, maturity)
Next i

End Sub

 

[felixschreibt]

I think I almost got it - what I dont get is why is Excel posting 01.06.1900 in cells (2,3) which has nothing to do with the mat or start date. In my example num_cf is 6. Any idea?

Sub cashflows()

Dim start As Date
Dim maturitiy As Date
Dim num_cf As Integer

start = Cells(2, 1).Value
maturity = Cells(2, 2).Value
num_cf = DateDiff("yyyy", start, maturity)
Cells(2, 3) = num_cf

For i = 1 To num_cf
Cells(2, 3 + i) = DateAdd("yyyy", -num_cf + i, maturity)
Next i

End Sub

also clear, its the num_cf which excel was showing me as date