Create the description of the income or outcome stream.

Sometimes, a descriptions of the payment stream needs to be generated in the contracts or other legal documents.

For example,
We have the following payment stream in the excel.

We would like to generate the payment description as the following;

3 Monthly payments of $ 76.83 per month, beginning November 6, 2018 through and including January 6, 2019;  12 Monthly payments of $ 300.00 per month, beginning February 6, 2019 through and including January 6, 2020;  12 Monthly payments of $ 500.00 per month, beginning February 6, 2020 through and including January 6, 2021;  12 Monthly payments of $ 656.00 per month, beginning February 6, 2021 through and including January 6, 2022; 1 payment in the amount of $ 5,000.00 due on February 6, 2021;

You can type in the description. It would not take long time to do one. However, it would be a terrible task if we have to do it more than 10 times.

One method is to use the formula

Step 1: In the Cell I5, Formula = IF(E5>1,CONCATENATE(E5," monthly payments of $",TEXT(D5,"#,###.00")," per month, beginning ",TEXT(C5,"mmmm d,yyyy")," through and including ",TEXT(C5,"mmmm d,yyyy")),CONCATENATE("1 payment in the amount of $ ",TEXT(D5,"#,###.00")," due on ",TEXT(C5,"mmmm d,yyyy")))

Step 2: Copy the formula to from Cell I6 to I9.

Step 3: In Cell J6, Formula= I5&"; "&I6

Step 4: Copy the formula to from Cell J7 to J9.

Step 5: J9 is the final description we need. It can be copied to the word document.

Another method is to use VBA Code.

Sub Description()

Dim PStream As String

Dim i As Integer

Set wk1 = ActiveSheet

PStream = ""

wk1.Range("H1").ClearContents

i = 12

Do While wk1.Cells(i, 1) <> ""

   

    If wk1.Cells(i, 5).Value = 1 Then

        Des = "1 payment of $" & Format(wk1.Cells(i, 4), "#,##0.00") _

        & " due on " & Format(wk1.Cells(i, 3), "mmmm d, yyyy") & "; "

    Else

        Des = Format(wk1.Cells(i, 5), "#,##0") & " " & "monthly payments of $" & Format(wk1.Cells(i, 4), "#,##0.00") _

        & " per month, beginning " & Format(wk1.Cells(i, 3), "mmmm d, yyyy") & " through and including " & Format(wk1.Cells(i, 7), "mmmm d, yyyy") & "; "

    End If

   

    i = i + 1

    PStream = PStream & Des

Loop

wk1.Range("H1") = PStream

End Sub