Excel VBA Tips: A Macro to Generate Yearly Loan Schedule Report with Aggregated Monthly Re-Payments





In this video, to provide users with a summarized yearly loan schedule report, I guide viewers in creating a new VBA macro based on modifying my previous Video demonstrated VBA macro for monthly loan schedule report which reveals interest and principal payments over the long term. The process involves two vertical VBA project windows and adjusting variables, calculations, and formatting. The updated procedure is offering an aggregated yearly report for different loan terms. Ths VBA codes is provided as following:


Sub GenerateLoanReport_AggregatedYearlyPeriodReport()


' Created by MIKE GuangYuan YU on 8th of July , 2023 for M.Y BI & Analytics youtube channel

    ' the procedure is to create loan report at yearly repayment period

    ' Version 1.0

    

    'I. Define required variables

        Dim wsLoan, wsLoanReport As Worksheet

        Dim LoanAmount As Double

        Dim InterestRate As Double

        Dim LoanTerm As Integer

        Dim MonthlyPayment, YearlyPayment As Double

        Dim YearlyInterest, monthlyInterest As Double

        Dim YearlyPrincipal, monthlyPrincipal As Double

        Dim StartingBalance, RemainingBalance As Double

        Dim year, month As Integer

        'Dim MName As String

        Dim i, CurrentRow As Integer

    

    'II. Set laon input sheet and load loan details from it

    

        Set wsLoan = ThisWorkbook.Sheets("Loan") 'set sheet loan for variable

        CurrentRow = 1

        LoanAmount = wsLoan.Cells(CurrentRow + 6, "D").Value

        InterestRate = wsLoan.Cells(CurrentRow + 7, "D").Value

        LoanTerm = wsLoan.Cells(CurrentRow + 8, "D").Value

        year = wsLoan.Cells(CurrentRow + 9, "D").Value

    

    'III. Calculate the fixed yearly payment based on the fixed monthly payment

        MonthlyPayment = WorksheetFunction.Pmt(InterestRate / 12, LoanTerm * 12, -LoanAmount)

        YearlyPayment = MonthlyPayment * 12

    

    'IV-1 Clear existing data in report sheet (if any)

        Set wsLoanReport = ThisWorkbook.Sheets("Loan Report") ' set sheet report for variable

        wsLoanReport.UsedRange.Clear

    'IV-2 Set headers in report sheet

        With wsLoanReport

            .Range("A1").Value = "Year"

            .Range("B1").Value = "Payment Period"

            .Range("C1").Value = "Loan Starting Balance"

            .Range("D1").Value = "Yearly Payment"

            .Range("E1").Value = "Principal Payment"

            .Range("F1").Value = "Interest Payment"

            .Range("G1").Value = "Loan Remaining Balance"

        End With

    

  'V. Calculate yearl payment details for each year period in iteration loop

        RemainingBalance = LoanAmount

        YearlyInterest = 0

        

       'loop each loan term (yearly) to find yearly interest and principal.

        For i = 1 To LoanTerm

            

            StartingBalance = RemainingBalance 'at the beginning of each loop step to transfer remaining balance to starting balalnce

            

            'loop the 12 months cycle under a year and accumulate all 12 monthly interests for the year interest

                For month = 1 To 12

                    monthlyInterest = RemainingBalance * (InterestRate / 12)

                    YearlyInterest = YearlyInterest + monthlyInterest

                    monthlyPrincipal = MonthlyPayment - monthlyInterest

                    RemainingBalance = RemainingBalance - monthlyPrincipal

                Next month

        

        'update year period principal payment with the calculated yealy interest

            YearlyPrincipal = YearlyPayment - YearlyInterest

            

        ' Populate data in report sheet

            With wsLoanReport

                .Cells(i + 1, 1).Value = year

                .Cells(i + 1, 2).Value = i

                .Cells(i + 1, 3).Value = Format(StartingBalance, "0.00")

                .Cells(i + 1, 4).Value = Format(YearlyPayment, "0.00")

                .Cells(i + 1, 5).Value = Format(YearlyPrincipal, "0.00")

                .Cells(i + 1, 6).Value = Format(YearlyInterest, "0.00")

                .Cells(i + 1, 7).Value = Format(RemainingBalance, "0.00")

            End With

        

        'update year period interest and calendar year number

            YearlyInterest = 0

            year = year + 1

        Next i

    

    

    'VI-1  Format report sheet

        With wsLoanReport

            .Columns("A:G").AutoFit

            .Range("A1:G1").Font.Bold = True

        End With

    'VI-2 Display completion message

        MsgBox "Loan report generated successfully! ===>> Pls check the 'Loan Report' tab"


End Sub

 

Comments

Popular posts from this blog

Power BI DAX Formula for Dynamic Ranking Calculations Based on User Selections and Filters

Power BI DAX and Visualizations: Active employees across time (as x-axis) by Tenure group or age group

Excel VBA Tips: A Macro to Generate Line Chart Dynamically for Loan Schedule Trend Report