Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Due Amount
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Due Amount
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))) "Abdul" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the amount of time over a permitted amount (12 hours) | Excel Discussion (Misc queries) | |||
Formula for amount owing subtract amount paid | Excel Worksheet Functions | |||
How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
Converting amount in figures to amount in words | Excel Discussion (Misc queries) | |||
How do I calculate Amount of Sales Tax from Total Amount? | Excel Worksheet Functions |