Formula for Payment Schedule

[Abdul]

Hello !,

I have the following in sheet1

A1 = amount
B1 = Paid Up to
C1 = Payment Term which is "Yearly" or "6 Months"
D1= Days per year Whic is either 365 or 355 (I have to use to types of
calander)

E1, F1, G1 ..... Jan 07, Feb 07, Mar 07, .... Month for 3 years

Based on the amount, Paid upto and Payment term i want to know in
which month next payments are due and the amount.

for eg.
if A1=50000 and B1=1/1/2007 and C1="6 Months" and D1=355 then I want
to get under the month which comes after 177.5 days (Jun 07) 25000,
and next 177.5 days (Dec 07) 25000, etc....
if A1=50000 and B1=1/1/2007 and C1="yearly" and D1= 355 then I want to
get under B1+355 days (Dec 07) 50000, and from that date 355 Days (Dec
08) 50000 etc...

same for the 365 day Canader

How I can have this using a formula?

Thanks

Abdul

 

[Joel]

I put in C2 the following formula for the number of days of the term

=IF(C1="Yearly",D1,D1/2)

then I used this formula (note I had the Add-in analysis tool pack for
eomonth)
in E2

=IF(MOD(E1-$B1,$C2)<=(EOMONTH(E1,0)-E1),$A1,"")

I copied E2 to all the cells in row 2 under the date. the formula shows a
payment for Jan 1 2007, so you may need to modify to eliminate this payment.