How to set payment dates and frequency?
 

I'm working on a payment amortization, but because it's 'specially situated'
for collections proceeding none of the templates 'quite fit' so I'm trying to
do one 'from scratch'. I've pretty much got it done except for a couple
things.

Here's a basic outline of what is needed to complete the SS:
A10 = Payment Number
B10 = Payment Date
C10 = Payment Amount

Right now I'm basically 'copying rows' down to the point that the balance is
paid off, but this requires someone with excel experience and I need to make
this 'novice friendly'.

So... can anyone offer suggestions on how I can:
1. Set up a 'first payment date' and 'payment frequency' field so that it
automatically adjusts A10 based on what's entered? Say 'first payment' is
01/01/06, and frequency is 'weekly'....then the SS would automatically fill
in payment numbers and dates based on those criteria? The start date and
frequency (monthly, weekly, etc) would change so I need the payment dates to
change based on what is entered.

2. How can i set it up so that the SS stops computing when the estimated
balance is equal or less than the payment amount? Right now I 'copy rows'
down to the 'payoff' but this required me to add or delete rows based on the
payment schedule and amounts,

thanks,

How to set payment dates and frequency?
 

Dim bExit as Boolean, i as Long
Dim StartDate as Date
Dim interval as String
Dim rng as Range
Dim PaybackAmount as Double
' set startdate and interval
' and PaybackAmount

bExit = False
i = 1
do
Cells(i,1) = i
cells(i,2) = DateSerial(year(StartDate), _
Month(StartDate) + iif(interval="Monthly",1,0)*(i-1), _
Day(StartDate)+iif(interval="Weekly",1,0)*(i-1))
Cells(i,3) = PaymentAmount
set rng = Range("C1",Cells(i,3))
if PayBackAmount - Application.Sum(rng) <= 0 then
cells(i,3) = cells(i,3) - (paybackAmount = Applicaiton.Sum(rng))
bExit = true
end if
i = i + 1
loop until bExit


--
Regards,
Tom Ogilvy


"phooey" wrote in message
...
I'm working on a payment amortization, but because it's 'specially
situated'
for collections proceeding none of the templates 'quite fit' so I'm trying
to
do one 'from scratch'. I've pretty much got it done except for a couple
things.

Here's a basic outline of what is needed to complete the SS:
A10 = Payment Number
B10 = Payment Date
C10 = Payment Amount

Right now I'm basically 'copying rows' down to the point that the balance
is
paid off, but this requires someone with excel experience and I need to
make
this 'novice friendly'.

So... can anyone offer suggestions on how I can:
1. Set up a 'first payment date' and 'payment frequency' field so that it
automatically adjusts A10 based on what's entered? Say 'first payment' is
01/01/06, and frequency is 'weekly'....then the SS would automatically
fill
in payment numbers and dates based on those criteria? The start date and
frequency (monthly, weekly, etc) would change so I need the payment dates
to
change based on what is entered.

2. How can i set it up so that the SS stops computing when the estimated
balance is equal or less than the payment amount? Right now I 'copy rows'
down to the 'payoff' but this required me to add or delete rows based on
the
payment schedule and amounts,

thanks,