How to Populate Excel using Bing Chat GPT-4

Channel:

Matrix Inception

Subscribers:

839

Published on March 25, 2023 10:31:27 PM ● Video Link: https://www.youtube.com/watch?v=JC37u9-dUAI

Category:

Guide

Duration: 1:59

4,183 views

13

How to populate Excel file with a monthly mortgage amortization schedule using Bing Chat GPT-4.

Prompt to Bing Chat (Precise): please write VBA code to populate excel file with a mortgage monthly amortization schedule. please start with a summary of parameters: starting principal $500,000, annual interest rate 5%, term 25 years before starting the header on row 7

Working VBA code generated by Bing Chat:

Sub AmortizationSchedule()
' Declare variables
Dim StartingPrincipal As Double
Dim AnnualInterestRate As Double
Dim TermInYears As Integer
Dim MonthlyPayment As Double
Dim MonthlyInterestRate As Double
Dim NumberOfPayments As Integer
Dim PaymentNumber As Integer
Dim InterestPaid As Double
Dim PrincipalPaid As Double
Dim EndingBalance As Double

' Set values for variables
StartingPrincipal = 500000
AnnualInterestRate = 0.05
TermInYears = 25

' Calculate monthly interest rate and number of payments
MonthlyInterestRate = AnnualInterestRate / 12
NumberOfPayments = TermInYears * 12

' Calculate monthly payment using PMT function
MonthlyPayment = Application.WorksheetFunction.Pmt(MonthlyInterestRate, NumberOfPayments, -StartingPrincipal)

' Set initial value for ending balance
EndingBalance = StartingPrincipal

' Print summary of parameters
Range("A1").Value = "Starting Principal"
Range("B1").Value = StartingPrincipal
Range("A2").Value = "Annual Interest Rate"
Range("B2").Value = AnnualInterestRate
Range("A3").Value = "Term (Years)"
Range("B3").Value = TermInYears
Range("A4").Value = "Monthly Payment"
Range("B4").Value = MonthlyPayment

' Print headers for amortization schedule table
Range("A7").Value = "Payment Number"
Range("B7").Value = "Beginning Balance"
Range("C7").Value = "Payment"
Range("D7").Value = "Interest Paid"
Range("E7").Value = "Principal Paid"
Range("F7").Value = "Ending Balance"

' Loop through each payment and calculate values for table
For PaymentNumber = 1 To NumberOfPayments
InterestPaid = EndingBalance * MonthlyInterestRate
PrincipalPaid = MonthlyPayment - InterestPaid
EndingBalance = EndingBalance - PrincipalPaid

' Print values for current payment in table
Cells(PaymentNumber + 7, 1).Value = PaymentNumber
Cells(PaymentNumber + 7, 2).Value = EndingBalance + PrincipalPaid
Cells(PaymentNumber + 7, 3).Value = MonthlyPayment
Cells(PaymentNumber + 7, 4).Value = InterestPaid
Cells(PaymentNumber + 7, 5).Value = PrincipalPaid
Cells(PaymentNumber + 7, 6).Value = EndingBalance

Next PaymentNumber

End Sub