Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for Payment Schedule
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula for Payment Schedule
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. "Abdul" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loan Schedule with Balloon Payment | Excel Worksheet Functions | |||
Student loan payment schedule? | Excel Discussion (Misc queries) | |||
need a loan payment schedule | New Users to Excel | |||
what should a future payment schedule have on it | Excel Discussion (Misc queries) | |||
I need an amortization schedule with the first 6mo no payment? | Excel Worksheet Functions |