Print Generated Array

S

Stephen Lynch

All:

I found this code that sends an amortization schedule to an array.

Can someone help me print the array (final amortization schedule) to a table
or a text file.

Thanks - Steve

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
 
A

Alex Dybenko

Hi,
something like this:

for i = 1 to ubound(Schedule2())
debug.print Schedule2(i, 1), Schedule2(i, 2) ,Schedule2(i, 3)
,Schedule2(i, 4) ,Schedule2(i, 5)
next i

But if you need to have it is a table - better put values into table
directly during calculations, no need to create array first

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top