I am trying to make an "accounting" model for Amortized cost. I will make one array with the actual payment dates, one array with the "amortized cost" and another array showing values at reporting day (e.g. 31.12). I have already made this manually, but want this to perform these actions by "one click", by just changing input data. I am pretty new to VBA (just a couple of days in) and am struggling so far with the "payment date"-array, showing the cash flow on the bond.

So far I have the following code

```
Sub LoanAmortization()

'----------------------------------------------------------------------------------------------------------------------------------------------
'1)Define the arrays and variables that will be used along the process
'----------------------------------------------------------------------------------------------------------------------------------------------

'Dim Trends As Workbook                         'Variable to refer to the workbook

    Dim initLoanBal As Double         'Initial bond amount
    Dim DayCountBasis As Double       'Day count convention
    Dim BegDate As Date               'Date of bond repayment
    Dim MaturityDate As Date          'Date of bond repayment
    Dim TransCost As Double           'Transactioncosts on bonds
    Dim PayFreq As Double             'Frequency of coupon payments on bond (e.g. quarterly)
    Dim initRate As Double            'Interest rate on bond
    Dim CashFlowArray() As Integer    'Array of Cash flows on bond
    Dim CouponFreqString As String
    Dim NomRate As Double             'Rate used for cash flow calculation

    Dim i As Long
''----------------------------------------------------------------------------------------------------------------------------------------------
''2)Set variables for the calculation
''----------------------------------------------------------------------------------------------------------------------------------------------

    initLoanBal = ThisWorkbook.Worksheets("Amortisering").Range("D3").Value
    TransCost = Worksheets("Amortisering").Range("D4").Value
    initRate = Worksheets("Amortisering").Range("D5").Value
    Spread = Worksheets("Amortisering").Range("D6").Value
    DayCountBasis = Worksheets("Amortisering").Range("D7").Value
    CouponFreq = Worksheets("Amortisering").Range("E8").Value
    CouponFreqString = Worksheets("Amortisering").Range("D8").Value
    BegDate = Worksheets("Amortisering").Range("D9").Value
    MaturityDate = Worksheets("Amortisering").Range("D10").Value
    NomRate = initRate + Spread   

    '----------------------------------
    'Format variables for the calculation
    '----------------------------------
    Cells(5, 4).Select
    Selection.Value = initRate
    Selection.NumberFormat = "0.00%"

    Cells(6, 4).Select
    Selection.NumberFormat = "0.00%"

'-----------------------------------------------------------
'Set cash flows dates
'-----------------------------------------------------------
NoPeriods = DateDiff(CouponFreqString, BegDate, MaturityDate, vbMonday) 
' Number of periods ("payments") on the bond
    Range("G29") = BegDate
    Range("F31") = BegDate
    Range("G31").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

                For i = 1 To NoPeriods
                    Cells(29, 7 + i) = DateAdd(CouponFreqString, i, BegDate)
                    Cells(31 + i, 6) = DateAdd(CouponFreqString, i, BegDate)
                Next i
'----------------------------------------------
'Set number of days dager
'----------------------------------------------

    For i = 1 To NoPeriods  ' No. days between payments (daycount convention)
           Cells(30, 7 + i) = WorksheetFunction.YearFrac(Cells(29, 6 + i), Cells(29, 7 + i), DayCountBasis)
    Next i
'----------------------------------------------
'Cash flow array
'----------------------------------------------
    For c = 1 To NoPeriods
        For i = 1 To NoPeriods
                Cells(30 + i, 7 + c) = initLoanBal * NomRate * Cells(30, 7 + c)
               Next i
    Next c

Range("G31") = -initLoanBal + TransCost

End Sub

```

**GOAL**

So the problem appears in the "cash flow array" part.
1. The ultimate goal is to use XIRR to calculate the Effective interest rate for each period based on NomRate.

2. I want NomRate to vary for each period, as there is a change in the floating rate.
3. I want the final payment in each row to be equal to the interest payment and the repayment of the loan (i.e. initLoanBal).
4. I want the first cash flow to be equal to previous period calculated Amortized cost.
5. I want the Array to reduce by 1 per iteration

Please see image for an illustration (the green values are the "amortized cost values" from next array which is the amortized cost values) of how I want it to look

[![enter image description here](https://i.stack.imgur.com/XzUo8.png)](https://i.stack.imgur.com/XzUo8.png)