Due Amount

[Abdul]

Hello,

I have the following:

A1 : Amount B1: Paid Upto C1: Payment Term D1: Due Amount

By using a formula i want to get the due amount in D1 Due Amount

eg:

If A1= 50,000(for one year) B1= 01/01/2006 C1=6 Months (normally this
is either Yearly or in Every Six months) then in D1 i want to get
75000 i.e two (25000+25000) payment fo 2006 and one payment for 2007
(25000) and if todays date is 1/6/07 then the amount should be 100,000
i.e two payment for 2006 and two payment for 2007 (payment is always
advance for the coming six months if it is in every six months or for
one year if the term (C1) is Yearly. No need to show fraction amount

how can I achieve this using a formula?

thanks

Abdul

 

[Joel]

You need to have in c1 a number for the month.

to convert months to days
=C1/12 * 365

Number of days from pay up to
=now() - B1

Now you simply need the integer portion of the division to get the number of
payments
=int(now() - B1)/(C1/12 * 365))

The dollar amount of the paymentts is (12 is 12 month in a yearr)
=A1/(12/C1)

Now put thhe whole thing together

=A1/(12/C1) * (int(now() - B1)/(C1/12 * 365)))