Creating a List of Loan Periods Dynamically

[Guest]

I am creating a Loan Amortization schedule and would like to configure it to
autmatically populate the list of periods based on the number of periods in
the loan. Is this possible, and if so, how would one do it. Thanks.

 

[Bernie Deitrick]

David,

You can use an event. For example, the code below will copy row 5 down for enough rows to match the
number entered in cell C2. This assumes that Row 5 has the formulas needed to created the
amortization table.

Copy the code, right-click the sheet tab, and select "View Code", then paste the code in the window
that appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$2" Then Exit Sub
Application.EnableEvents = False
Range("6:65536").Clear
Range("5:5").Copy Range("5:" & Target.Value + 4)
Application.EnableEvents = True
End Sub

 

[Guest]

I am creating a Loan Amortization schedule and would like to configure it to
autmatically populate the list of periods based on the number of periods in
the loan. Is this possible, and if so, how would one do it. Thanks.

Assume cell B4 contains the Loan Start Date.
Assume cell A7 is the first period
Assume cell A8 is the second period
Assume each period is one month

1) make sure the 'Analysis ToolPak' addin is active
2) format cells A7 and A8 as DATE (mmm-yyyy)
3) formula in A7...
=EOMONTH(B4,0)+1
4) formula in A8...
=EOMONTH(A7,1)
5) copy the formula in A8 down the appropriate # of rows for the
amortization table

HTH,