VBA Code For Your Bridge Finance Spreadsheets

In this article collapse

VBA Code For Your Bridge Finance Spreadsheets

What is VBA?

VBA stands for Visual Basic for Applications. When used correctly, it’s the secret sauce that brings your Excel spreadsheets to life. Like a puppeteer pulling the strings, VBA allows you to control Excel's every move. The possibilities are endless.

Getting Started with VBA Code in Excel

Before we dive into the code, it's essential to know where to put it. If you're new to VBA, here's a step-by-step guide to get you started:

Accessing the VBA Editor: Open Excel and press ALT + F11. This shortcut will open the VBA Editor, a separate window where you'll input and run your VBA code.
Inserting a Module: In the VBA Editor, right-click on any of the items in the "Project - VBA Project" pane (usually on the left side). From the context menu, choose "Insert" and then "Module". This action will create a blank space, called a module, where you can type or paste the VBA code.
Pasting the Code: Once you have a module open, simply copy the VBA code from this blog (or any other source) and paste it into the white space in the module window.
Running the Code: After pasting the code, you can close the VBA Editor and go back to Excel. To run the code, press ALT + F8, select the relevant macro (the name of the Sub, e.g., GenerateAmortizationTableFromCells), and click "Run".
Save as Macro-Enabled Workbook: If you want to save your Excel file with the VBA code, remember to save it as a "Macro-Enabled Workbook" (with an .xlsm extension). This ensures that your VBA code remains intact and can be executed the next time you open the workbook.

Now you know the basics, let’s look at some possibilities.

3 Things You Can Do With VBA

1. The Magical Loan Amortization Tables

Ever wondered how each of your loan payments gets divided between interest and principal? Or how your outstanding loan amount dwindles (hopefully) over time? Enter the loan amortization table.

Sub GenerateAmortizationTableFromCells() 
    Dim loanAmount As Double 
    Dim annualRate As Double 
    Dim totalPeriods As Integer 
    Dim monthlyPayment As Double 
    Dim interestPayment As Double 
    Dim principalPayment As Double 
    Dim remainingBalance As Double 
    Dim totalPayment As Double 
 
    ' Retrieve values from specific cells in Sheet1 
    loanAmount = Sheets("Sheet1").Range("F2").Value  ' Assuming F2 has the loan amount 
    annualRate = Sheets("Sheet1").Range("F3").Value  ' Assuming F3 has the annual rate 
    totalPeriods = Sheets("Sheet1").Range("F4").Value  ' Assuming F4 has the total periods 
 
    remainingBalance = loanAmount 
    monthlyPayment = Abs(Application.WorksheetFunction.Pmt(annualRate / 12, totalPeriods, loanAmount)) ' Use Abs to get positive value 
     
    ' Headings for clarity 
    Sheets("Sheet1").Range("A1").Value = "Month" 
    Sheets("Sheet1").Range("B1").Value = "Total Payment" 
    Sheets("Sheet1").Range("C1").Value = "Principal Payment" 
    Sheets("Sheet1").Range("D1").Value = "Interest Payment" 
    Sheets("Sheet1").Range("E1").Value = "Remaining Balance" 
     
    ' Calculate the amortization table 
    For i = 1 To totalPeriods 
        interestPayment = remainingBalance * (annualRate / 12) 
        principalPayment = monthlyPayment - interestPayment 
        totalPayment = principalPayment + interestPayment 
        remainingBalance = remainingBalance - principalPayment 
         
        Sheets("Sheet1").Cells(i + 1, 1).Value = i 
        Sheets("Sheet1").Cells(i + 1, 2).Value = totalPayment 
        Sheets("Sheet1").Cells(i + 1, 3).Value = principalPayment 
        Sheets("Sheet1").Cells(i + 1, 4).Value = interestPayment 
        Sheets("Sheet1").Cells(i + 1, 5).Value = remainingBalance 
    Next i 
 
    ' Clear rows below the last generated row 
    Sheets("Sheet1").Rows(totalPeriods + 2 & ":" & Sheets("Sheet1").Rows.Count).Delete 
End Sub

Type the following values into your Excel in Sheet1:

F2: Loan Amount – e.g. 1000000
F3: Interest rate per annum, e.g. 36%
F4: Term of the loan in months, perhaps 24 months

Run the code and behold the magic.

2. Play Fortune Teller with Loan Scenario Analysis

Want to foresee the consequences of various financial decisions? By leveraging VBA, you can easily compare different loan scenarios side by side, granting you the clarity to make informed decisions.

Sub LoanScenarioAnalysisOnSheet2() 
    Dim initialLoan As Double 
    Dim startingRate As Double 
    Dim rateIncrease As Double 
    Dim periods As Integer 
    Dim newRate As Double 
    Dim newPayment As Double 
    Dim scenarioRow As Integer 
 
    ' Retrieve values from specific cells in Sheet2 
    initialLoan = Sheets("Sheet2").Range("A1").Value    ' Assuming A1 in Sheet2 has the initial loan amount 
    startingRate = Sheets("Sheet2").Range("A2").Value   ' Assuming A2 in Sheet2 has the starting rate 
    rateIncrease = Sheets("Sheet2").Range("A3").Value   ' Assuming A3 in Sheet2 has the rate increase 
    periods = Sheets("Sheet2").Range("A4").Value        ' Assuming A4 in Sheet2 has the number of periods 
 
    ' Define the starting row for our data in Excel 
    scenarioRow = 6   ' Assuming row 6 to start output (to keep a gap between input and output) 
 
    ' Set headers for our output 
    Sheets("Sheet2").Cells(scenarioRow - 1, 1).Value = "Scenario" 
    Sheets("Sheet2").Cells(scenarioRow - 1, 2).Value = "Interest Rate Increase" 
    Sheets("Sheet2").Cells(scenarioRow - 1, 3).Value = "Total Interest Rate" 
    Sheets("Sheet2").Cells(scenarioRow - 1, 4).Value = "New Monthly Payment" 
     
     ' Loop through 5 scenarios 
    For i = 1 To 5 
        newRate = startingRate + (i - 1) * rateIncrease 
        newPayment = Application.WorksheetFunction.Pmt(newRate / 12, periods, -initialLoan) 
         
        ' Output the scenario details to Sheet2 
        Sheets("Sheet2").Cells(scenarioRow, 1).Value = "Scenario " & i 
        Sheets("Sheet2").Cells(scenarioRow, 2).Value = Format((i - 1) * rateIncrease, "0.00%") & " Increase" 
        Sheets("Sheet2").Cells(scenarioRow, 3).Value = Format(newRate, "0.00%") & " Total" 
        Sheets("Sheet2").Cells(scenarioRow, 4).Value = newPayment 
        scenarioRow = scenarioRow + 1 
    Next i 
End Sub

Create a sheet called Sheet2 (again, very important) and then put the following values in cells:

A1: Loan Amount
A2: Starting interest rate per year (say 24%)
A3: Interest rate increments (so perhaps 1% )
A4: The period of the loan, perhaps 36 months

Run the code (using the method described above) to compare monthly payments for a range of interest rates.

3. Become a Financial Artist with Advanced Graphical Representations

Who said numbers can't be visually captivating? With VBA, you can automate chart creation, making the tedious aspects of loan management aesthetically pleasing.

Sub CreateLoanChart() 
    Dim ChartRange As Range 
    Dim loanChart As ChartObject 
    Dim totalPeriods As Integer 
    Dim srs As Series 
     
    ' Retrieve total periods (months) from Sheet1 
    totalPeriods = Sheets("Sheet1").Range("F4").Value  ' Assuming F4 on Sheet1 has the total periods 
 
    ' Define range based on the total periods. Excluding months column. 
    Set ChartRange = Sheets("Sheet1").Range("c1:e" & totalPeriods + 1) ' +1 to include the header 
 
    ' Create the chart on Sheet1 
    Set loanChart = Sheets("Sheet1").ChartObjects.Add(Left:=250, Width:=500, Top:=10, Height:=250) 
    loanChart.Chart.SetSourceData Source:=ChartRange 
    loanChart.Chart.ChartType = xlLine 
 
    ' Move the 'Principal Payment' and 'Interest Payment' series to secondary axis 
    For Each srs In loanChart.Chart.SeriesCollection 
        If srs.Name = "Principal Payment" Or srs.Name = "Interest Payment" Then 
            srs.AxisGroup = xlSecondary 
        End If 
    Next srs 
 
    ' Style the chart 
    loanChart.Chart.HasTitle = True 
    loanChart.Chart.ChartTitle.Text = "Loan Amortization Overview" 
     
    ' Primary Axis (Remaining Balance) 
    loanChart.Chart.Axes(xlValue, xlPrimary).HasTitle = True 
    loanChart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Amount (Remaining Balance)" 
 
    ' Secondary Axis (Payments) 
    loanChart.Chart.Axes(xlValue, xlSecondary).HasTitle = True 
    loanChart.Chart.Axes(xlValue, xlSecondary).AxisTitle.Text = "Amount (Payments)" 
End Sub

This code will transform the data from your loan amortization table in Sheet1 into a sleek line chart, providing a visual overview of your loan's progress over time.

It’s Time to Start Automating

Hopefully this article has shown you that once you unlock its potential, VBA code is like giving your Excel spreadsheets superpowers.

Email our team if you run into any issues with the code, would like help creating some new code for a different need or to get your own copy of our VBA spreadsheet.