Write a VBA program that will query the user for a loan amount (dollars) and a l

Question

Write a VBA program that will query the user for a loan amount (dollars) and a loan period (years), and will then create a 2-d array containing an amoritization table (monthly), with the following columns: Payment number, Monthly Payment, Amount for Interest, Amount for Principle, Current Balance. Output the array to a txt file. **Format text in a readable way!

***Run the program with a loan amount in the range of $75,000 to $325,000, and an interest rate in the range of 2.75 to 6.9% annually.

***Give full solution to the problem for five star rating!!!!!!

Explanation / Answer

Option Explicit ' Loan amortization schedule functions Function AmortSchedTraditional(BeginPrincipal As Double, PeriodRate As Double, Periods As Long, _ Optional ExtraPrin As Double = 0) ' This function returns an array with the amortization schedule for a loan with known ' beginning principal, period rate, number of periods, and optional fixed extra principal ' with each payment. All arguments should be positive ' The function returns an array of length (1 To N, 1 To 5), where N is the number of payments ' ultimately needed to retire the loan (may be different from initial Periods argument if extra ' principal payments are made) ' In the second dimension, the values are: ' 1: Balance before payment X ' 2: Total amount of payment X ' 3: Principal amount of payment X ' 4: Interest amount of payment X ' 5: Balance after payment X ' To use this function in the Excel UI, use an array formula. If the array area has more rows than the ' function returns, the 'extra' rows will show a #N/A error. You can use Conditional Formatting ' to dynamically hide such results. ' This function will work in other VBA and VB projects. Dim Schedule() As Double Dim Schedule2() As Double Dim BeginBal As Double Dim Counter As Long Dim Counter2 As Long Dim LevelPay As Double ' Dimension array with 'first pass' amortization schedule ReDim Schedule(1 To Periods, 1 To 5) As Double ' Negative extra payments are suppressed If ExtraPrin < 0 Then ExtraPrin = 0 BeginBal = BeginPrincipal LevelPay = -Pmt(PeriodRate, Periods, BeginPrincipal) + ExtraPrin For Counter = 1 To Periods Schedule(Counter, 1) = BeginBal ' Interest is easy to determine; do it first Schedule(Counter, 4) = BeginBal * PeriodRate ' Amount of principal portion depends in part on the remaining balance before the payment ' is applied; the principal amount can never be larger than this Schedule(Counter, 3) = IIf((LevelPay - Schedule(Counter, 4)) < BeginBal, _ LevelPay - Schedule(Counter, 4), BeginBal) ' Total payment = principal portion + interest portion Schedule(Counter, 2) = Schedule(Counter, 3) + Schedule(Counter, 4) ' Determine balance after payment is applied. Check for a balance approaching zero Schedule(Counter, 5) = BeginBal - Schedule(Counter, 3) If Schedule(Counter, 5) < 0.01 Then Schedule(Counter, 5) = 0 Exit For End If BeginBal = Schedule(Counter, 5) Next ' Create a new array that has only as many 'rows' as there are payments to be made ReDim Schedule2(1 To Counter, 1 To 5) As Double For Counter2 = 1 To Counter Schedule2(Counter2, 1) = Schedule(Counter2, 1) Schedule2(Counter2, 2) = Schedule(Counter2, 2) Schedule2(Counter2, 3) = Schedule(Counter2, 3) Schedule2(Counter2, 4) = Schedule(Counter2, 4) Schedule2(Counter2, 5) = Schedule(Counter2, 5) Next ' Assign the function return value AmortSchedTraditional = Schedule2 End Function Function AmortSchedSpecialPmt(BeginPrincipal As Double, PeriodRate As Double, DesiredPay As Double) ' This function returns an array with the amortization schedule for a loan with known ' beginning principal, period rate, and desired amount of each payment. All arguments ' should be positive ' If the desired payment is not larger than the first period's interest due, the function will ' return an error (because the loan cannot be retired). ' The function returns an array of length (1 To N, 1 To 5), where N is the number of payments ' ultimately needed to retire the loan ' In the second dimension, the values are: ' 1: Balance before payment X ' 2: Total amount of payment X ' 3: Principal amount of payment X ' 4: Interest amount of payment X ' 5: Balance after payment X ' To use this function in the Excel UI, use an array formula. If the array area has more rows than the ' function returns, the 'extra' rows will show a #N/A error. You can use Conditional Formatting ' to dynamically hide such results. ' This function will work in other VBA and VB projects. Dim Schedule() As Double Dim BeginBal As Double Dim Counter As Long Dim NperResult As Double ' Determine the number of payments needed. Round all decimals up to the next integer NperResult = NPer(PeriodRate, DesiredPay, -BeginPrincipal) ReDim Schedule(1 To IIf((NperResult - Int(NperResult)) > 0.000001, Int(NperResult) + 1, _ Int(NperResult)), 1 To 5) As Double BeginBal = BeginPrincipal For Counter = 1 To UBound(Schedule, 1) Schedule(Counter, 1) = BeginBal ' Interest is easy to determine; do it first Schedule(Counter, 4) = BeginBal * PeriodRate ' Amount of principal portion depends in part on the remaining balance before the payment ' is applied; the principal amount can never be larger than this Schedule(Counter, 3) = IIf((DesiredPay - Schedule(Counter, 4)) < BeginBal, _ DesiredPay - Schedule(Counter, 4), BeginBal) ' Total payment = principal portion + interest portion Schedule(Counter, 2) = Schedule(Counter, 3) + Schedule(Counter, 4) ' Determine balance after payment is applied. Check for a balance approaching zero Schedule(Counter, 5) = BeginBal - Schedule(Counter, 3) If Schedule(Counter, 5) < 0.01 Then Schedule(Counter, 5) = 0 Exit For End If BeginBal = Schedule(Counter, 5) Next ' Assign the function return value AmortSchedSpecialPmt = Schedule End Function

this will work for you

please rate 5 star for help and explaination

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.