Excel Loan Calculator with functions PMT, IPMT, PPMT and it’s generated Report via VBA Macro .

 


Loan calculations involve using mathematical formulas to determine important loan aspects such as monthly payments, total interest, and overall cost. They are essential for financial planning and widely used by individuals, businesses, and financial institutions. Excel provides powerful loan calculation functions like PMT, PPMT, and IPMT, enabling users to analyze loans effectively. In my Excel example, these functions are utilized to create a basic Loan Calculator, where users input loan details and obtain calculated results for monthly and final repayments. Additionally, Excel's VBA codes can automate the generation of long-term loan reports, providing comprehensive information on each payment period. Enabling the Developer tab and accessing VBA procedures allows users to streamline and expedite loan analysis.


My Excel VBA codes can automate the generation of long-term loan reports:


Sub GenerateLoanReport_monthlyPeriodReport()

    

    ' 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 monthly repayment period

    ' Version 1.0

    

    ' I.  define the required variables

    Dim wsLoan, wsLoanReport As Worksheet

    Dim LoanAmount As Double

    Dim InterestRate As Double

    Dim LoanTerm As Integer

    Dim MonthlyPayment As Double

    Dim PeriodInterest As Double

    Dim PrincipalPayment As Double

    Dim StartingBalance, RemainingBalance As Double

    Dim year, month As Integer

    Dim MName As String

    Dim i, CurrentRow As Integer

    

    

    

    ' II. load loan details from loan input sheet

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

        CurrentRow = 1

    ' II. Read loan details from the loan input sheet

        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 monthly payment

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

    

    

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

        Set wsLoanReport = ThisWorkbook.Sheets("Loan Report") ' set and select report sheet report to the sheet variable

        wsLoanReport.UsedRange.Clear

    ' IV. Set headers in report sheet

        With wsLoanReport

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

            .Range("B1").Value = "Month"

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

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

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

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

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

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

        End With

    

    

    ' V. Calculate monthly payment details for each period in iteration steps

        

        RemainingBalance = LoanAmount 'initalize loan amount to balance vaiable

        month = 0                      'initalize month number

        LoanTerm = LoanTerm * 12       'convert loan term from year number level to the month number level

        

        For i = 1 To LoanTerm           'loop loan term at loan monthly period level

            

            StartingBalance = RemainingBalance 'set and update the starting loan balance

            month = month + 1                   'update month number with loop

            

            'check and update month number in between 1 to 12 clcyle under a year

                If month > 12 Then

                    month = 1

                    year = year + 1

                End If

                MName = monthName(month, ture) ' update month name according to updated month number

            

            ' calcualte period payment and uodate balance

                PeriodInterest = RemainingBalance * (InterestRate / 12)

                PrincipalPayment = MonthlyPayment - PeriodInterest

                RemainingBalance = RemainingBalance - PrincipalPayment

        

    ' V. populate all calculated results into report sheet with desired decimal format

                With wsLoanReport

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

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

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

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

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

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

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

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

                End With

        Next i 'loop next i until to reach the last loan term

    

    

    ' VI. Format report sheet

        With wsLoanReport

            .Columns("A:H").AutoFit

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

        End With

    ' VI. 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