Amortized cost calculation using VBA (Bond)

Question

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

Answer 1

I suggest you to use function instead of a Macro to do this.

The function will act as An Excel Function. As an example, if I do a function with the name TRIPLE that calcs 3 * x, where x is a cell number, I can use TRIPLE(A1) in excel to calc the triple in cell A1.

In your example, with your clarification, I tried to understand every step to do this code for you, however, the complexity didn't help me to do so.

But I started something. The idea for this function is to you specify everything you need (the cells starting with Hovedstol), the dates, the taxes and the index from result. If you need it, you can add anything using my instructions. The result for the function is the calculation of the Formel.

Example: for your first result, you should wright, in excel:

=LoanAmortization(B2,B3,B4,B5,B6,F2:F20,G2:G20,1)

For your second result:

=LoanAmortization(B2,B3,B4,B5,B6,F2:F20,G2:G20,1)

The taxes are organized in G2:G20.

So, the code needs to have this parameters at the beggining in vba to act as a Function:

Function LoanAmortization(A As Double, B As Double, C As Double, D As Double, E As Double, ByRef DatesRange As Excel.Range, ByRef TaxesRange As Excel.Range, MIndex As Integer) As Double
End Function

Now, you will need to work with arrays to do whatever you want, you won't need to format the cells, you'll be able to create your sheet whatever you want in multiple times and the code will still work. To create an array, you need first to specify the quantity of elements, in this example, you can create an matrix with 3 columns from 1 to 3 (starting with number 0 if not specified) and 2 lines starting with number 1 (same, if you not specify, the 0 number is the first one):

Dim ArrayExample(1 to 2, 1 to 3) As Double

You can also Redim your array, but you'll loose your data even if you use preserve, you can't change the variable type. If the size has a value from a variable, you NEED to use ReDim:

ReDim ArrayExample(1 to 4, 0 to 3)

To transform an excel.range to an array, just use this after your declaration:

ArrayExample = ArrayRange.Value

To use the matrix, just find the line and column you need, examples:

ArrayExample(3, 2) = 1
i = ArrayExample(1) 'Just one column (have to be specified in declaration)
ArrayExample(0, 0) = "test"

To use any excel function, like CountA function, just use this:

Application.WorksheetFunction.CountA

That's what I did:

Function LoanAmortization(A As Double, B As Double, C As Double, D As Double, E As Double, ByRef DatesRange As Excel.Range, ByRef TaxesRange As Excel.Range, MIndex As Integer) As Double

    Dim qtd As Integer
    Dim Dates(), Taxes(), DatesDifference() As Double 'If bug, use Variant variable type
    qtd = Application.WorksheetFunction.CountA(DatesRange)
    ReDim DatesRange(1 to qtd), Taxes(1 to qtd), DatesDifference(1 to qtd - 1)
    For 1 to qtd - 1
        DatesDifference(i) = DatesRange(i + 1) - DatesRange(i)
    Next

End Function

With this, you should be able to continue the code, sorry to not help more. If you have any doubt to how do something more specific, I'll try to help you.

Answer 2

In my early VBA days, I built a loan re-payment calculator/scheduler as a learning project. The program takes input parameters from a Userform and calculates the loan repayment schedule. I will attach the file below for you to take a look at. The primary algorithm for calculating the loan payment schedule, is the Bisection algorithm. It is the same one that Excel's Goal Seek uses.

Note: The code is bit elementary, because as I mentioned earlier, I was just starting out, so I didn't know that I could pass parameters to Subs instead of making variables public, my variable naming was atrocious, and so on. That being said, I don't have the time to go back and make it robust, however the procedures are heavily commented, so I still think that you could learn from it.

File: Loan Repayment Calculator

Edit: Financial Modeling Using Excel and VBA by Chandan Sengupta is an excellent resource as well. I used several ideas from it to build my loan repayment calculator.

Below is the main Code for the calculations. Note that Each of The following variables are set in a userform, but they can be set to cells instead: IntsRate, loanLife, PrcplBal, ymtFrqy, CompFrqy, IntvlLng, VariDateIntvl, UserDate

Public IntsRate As Double, loanLife As String, PrcplBal As Double, PymtFrqy As String, CompFrqy As String, _
IntvlLng As Integer, VariDateIntvl As Integer
Public UserDate As Date
Option Explicit
Option Private Module
Public Sub LoanTableCalculations()

 Dim LR As Long, numOfIterations As Long, iCol As Long, pCol As Long, rNum As Long, outrow As Long
 Dim balTolerance As Double
 Dim yrBegBal() As Double, yrEndBal() As Double, ipPay() As Double, finalBal As Double 
 Dim annualPmnt As Double, aPmtOld As Double

    Application.ScreenUpdating = False

    '************************************************************
    ' User inputs
    '************************************************************
     'Read the date entered by user on the userform
    UserDate = LoanUserform.txtPymtBegn.Value 'start of payments

    'Conditionally set date interval and row headers _
    based on user input
    If PymtFrqy = "Annually" Then
       VariDateIntvl = 12
            Cells(8, 4).Value2 = "Year"
            Cells(8, 5).Value2 = "Year Beg-Balance"
            Cells(8, 6).Value2 = "Annual Payment"
            Cells(8, 9).Value2 = "Year End-Balance"

      ElseIf PymtFrqy = "Semi-Annually" Then
       VariDateIntvl = 6
            Cells(8, 4).Value2 = "Semi-Annual Periods"
            Cells(8, 5).Value2 = "Semi-Annual Beg-Balance"
            Cells(8, 6).Value2 = "Semi-Annual Payment"
            Cells(8, 9).Value2 = "Semi-Annual End-Balance"

      ElseIf PymtFrqy = "Quarterly" Then
       VariDateIntvl = 4
            Cells(8, 4).Value2 = "Quarters"
            Cells(8, 5).Value2 = "Quarter Beg-Balance"
            Cells(8, 6).Value2 = "Quarterly Payment"
            Cells(8, 9).Value2 = "Quarter End-Balance"

      ElseIf PymtFrqy = "Monthly" Then
       VariDateIntvl = 1
            Cells(8, 4).Value2 = "Month"
            Cells(8, 5).Value2 = "Month Beg-Balance"
            Cells(8, 6).Value2 = "Monthly Payment"
            Cells(8, 9).Value2 = "Month End-Balance"

    End If

    '************************************************************
    'My inputs
    '************************************************************
     balTolerance = 0.5 'Specifies desired accuracy
     iCol = 1
     pCol = 2
     outrow = 8 'sets row where data will be output to

      'finds last row of data in column 3
      LR = Worksheets("Loan Amortization").Cells(Rows.Count, 3).End(xlUp).Row

     'Clear previous data and format
     '*****************************
      'Data
      Rows(outrow + 1 & ":" & (outrow + LR + 6)).ClearContents
      'Table Borders
      Rows(outrow + 1 & ":" & (outrow + LR + 6)). _
      Borders.LineStyle = xlNone

          'Redimension the arrays
          ReDim yrBegBal(1 To IntvlLng + 1)
          ReDim ipPay(1 To IntvlLng + 1, 1 To 2)
          ReDim yrEndBal(1 To IntvlLng)

        '************************************************************
        ' Computations and output; bisection algorithm
        '************************************************************
         annualPmnt = PrcplBal * IntsRate

             'This Do loop controls the iteration
             Do While finalBal > balTolerance Or finalBal = 0

                 'Initialize balance at the beginning of year 1
                 yrBegBal(1) = PrcplBal

                'Loop to calculate and store year-by-year data
                For rNum = 1 To IntvlLng
                 ipPay(rNum, iCol) = yrBegBal(rNum) * IntsRate
                 ipPay(rNum, pCol) = annualPmnt - ipPay(rNum, iCol)
                 yrEndBal(rNum) = yrBegBal(rNum) - ipPay(rNum, pCol)

                 yrBegBal(rNum + 1) = yrEndBal(rNum)

                Next rNum

                    finalBal = yrEndBal(IntvlLng)
                    aPmtOld = annualPmnt

                    'Calculate the next annual payment to try
                    annualPmnt = annualPmnt + (finalBal * (1 + IntsRate) ^ _
                    (-IntvlLng)) / IntvlLng

                    'Count # of iterations
                    numOfIterations = numOfIterations + 1

             Loop

        'Note these calculations could be placed in an array and then _ 
        be sent to a worksheet in all at once 
        '************************************************************
        ' Output data to worksheet
        '************************************************************
        Cells(outrow + 1, 3).Value = UserDate

         For rNum = 1 To IntvlLng
            Cells(outrow + rNum + 1, 3).Value = WorksheetFunction.EDate(Cells(outrow + rNum, 3).Value, VariDateIntvl)
            Cells(outrow + rNum, 4).Value = rNum 'Year number
            Cells(outrow + rNum, 5).Value = yrBegBal(rNum)
            Cells(outrow + rNum, 6).Value = annualPmnt
            Cells(outrow + rNum, 7).Value = ipPay(rNum, iCol)
            Cells(outrow + rNum, 8).Value = ipPay(rNum, pCol)
            Cells(outrow + rNum, 9).Value = yrEndBal(rNum)
         Next rNum


        '************************************************************
        ' Format data in table
        '************************************************************
         'format as dollars
         Range(Cells(outrow + 1, 5), Cells(outrow + IntvlLng, 9)). _
         NumberFormat = "$#,##0"


         'format as dates
         Range("C9" & ":" & "C" & (IntvlLng + 8)).NumberFormat = "m/d/yy"
         Cells(outrow + IntvlLng + 1, 3).ClearContents


         'Add Borders
          Range(Cells(outrow, 3), Cells(outrow + IntvlLng, 9)).Borders.LineStyle = xlContinuous

    'Clear Variables
    IntsRate = Empty
    loanLife = Empty
    PrcplBal = Empty
    PymtFrqy = Empty
    CompFrqy = Empty
    IntvlLng = Empty
    VariDateIntvl = Empty
    UserDate = Empty

    Application.ScreenUpdating = True

End Sub