Find # of multiples of EDATE between two dates

[CDavid]

Hi,

In an assumption sheet I want the user to input a payment amount, starting date (first payment date), ending date, and frequency (Monthly, Quarterly, Semi Annual, or Annual). Based on the frequency I can use the EDATE formula to determine subsequent payment dates. For instance if payment is 100, first payment is 1/1/2011, and last payment is on 1/1/2012, and frequency is monthly then we know there are 12 different payment dates/period in that time frame. Using the EDATE formula we know the payment occurs on 1/1 of each month for 12 months.

On another sheet (proforma cash flow) the end date of a period is show in column B (say 1/1/2011) and in column C the end date of the second period is shown (say 1/1/2012). This format continues depending on how many periods are in the proforma and so forth. What I'm trying to determine is how many of the multiples of the EDATE in the first paragraph are between two dates on the proforma sheet. In this example there should be 12 of the payments between 1/1/2011 and 1/1/2012. If these dates were changed to 1/1/2011 and 4/1/2011 then 3 payments would show up within those dates.


The EDATE formula will return the future dates, however, I would manually have to enter EDATE(1/1/2011,1) = 2/1/2011, EDATE (1/1/2011, 2) = 3/1/2011 EDATE, and so forth all the way to EDATE (1/1/2011,12) = 1/1/2012 to get the prospective future dates. Then I could use those dates to determine if they are within two periods on the cash flow sheet. Is there a function or formula to see if the multiples of EDATE up to X period exist between two dates?

[DonkeyOte]

Perhaps worth investigating the DATEDIF function: http://www.cpearson.com/excel/datedif.htm

If needed post back with a sample file (dummy values obviously) but outline expected results for sample data etc...

[CDavid]

Attached is an example with outlined expected results in row 7. Assumptions are in row 17. My goal would be to create it in a way that if the dates in row 5 are abnormal (say a couple days in between dates or a couple months) and don't follow the same pattern as the assumptions in row 17 that it would still know what column to place the payments in based on the dates.

[DonkeyOte]

Apologies for belated response - slipped under the radar...

I'm still not entirely sure I follow - ie why Dec 2010 is 2k given bi-annual and first payment is Dec 2009

Ignoring that issue for the time being the below might help to some extent:

Please Login or Register  to view this content.

it's also not clear how you intend to handle daily - it would be a good idea to post a sample with a row for each payment frequency (using same payment amount)

[CDavid]

Thanks for the help. The formula you mentioned is a step in the right direction however it doesn't account for if multiple payments of 1,000 would have occurred in a period. Assume the following payment schedule:

EDATE(2/15/2010,12) - so each month on the 15th a payment of say 1,000 is due. Now on the cash flow page lets assume that each period is 6 months: 1/1/2010, 6/1/2010, 1/1/2011 and 6/1/2011. There should be 4 payments of 1,000 that show up in the column period ending in 6/1/2010. Is there a formula/function that tells how many multiples of a date (EDATE of 2/15/2010 with a multiple/months of 1 or 2 or 3...up to 12) exist between two dates (1/1/2010 and 6/1/2010).

I wrote a macro that creates another sheet with a payment schedule using a loop but this is very tedious. Any help would be appreciated. Thanks

[DonkeyOte]

I'd like to help but first I'd suggest either

a) sticking to the original sample and answering questions raised, ie:

I'm still not entirely sure I follow - ie why [in sample] Dec 2010 is 2k given bi-annual and first payment is Dec 2009

or

b) if you do opt to change the scenario for whatever reason post a further sample that reflects those conditions


Bear in mind people here will perhaps look at whatever you post up for perhaps 5 mins at a time and on that basis what may seem obvious to you (based on familiarity of model) may not be to those trying to assist.

[CDavid]

My apologies. To answer the first question, the December 2010 column is one year after the December 2009 column. If the payments are on a semi-annual basis then 2 payments should occur in the December 2010 column (the 6/1 and 12/1 payments).

You also asked about daily. I'm not sure how I would handle this. For the time being I'd like to stick with figuring out just monthly, quarterly, semi-annually, and yearly.

The end goal is to know how many times a multiple of a certain date occurs between two dates up to the end of the payment schedule.

Attached is a new sample file with the formula I'm currently using to get the correct answer. However, I would think a more efficient formula exists to accomplish this. My formula also has to be modified for anything aside from a multiple of a month. Here it is (note this is an array formula):

=IF(TEXT(start,"dd")=TEXT(E3,"dd"),IF(AND(E3>=start,E3<=end),(SUM(IF(TEXT(start,"dd")=LEFT(TEXT(ROW(INDIRECT(D3&":"&E3)),"dd/mm/yyyy"),2),1,0))-1)*amount,IF(AND(end>D3,end<E3),(SUM(IF(TEXT(start,"dd")=LEFT(TEXT(ROW(INDIRECT(D3&":"&end)),"dd/mm/yyyy"),2),1,0))-1)*amount,0)),IF(AND(E3>=start,E3<=end),SUM(IF(TEXT(start,"dd")=LEFT(TEXT(ROW(INDIRECT(D3&":"&E3)),"dd/mm/yyyy"),2),1,0))*amount,IF(AND(end>D3,end<E3),SUM(IF(TEXT(start,"dd")=LEFT(TEXT(ROW(INDIRECT(D3&":"&end)),"dd/mm/yyyy"),2),1,0))*amount,0)))

Taking things a step further I would like to grow the payments by a growth rate (monthly in the new sample file) and sum the total. So for example, in cell E5 it indicates that 29 payments would have occurred in that period. If each payment is 1 dollar then 29 dollars are due. However, if the payment compounds by 1% each month the summation of the payments in that month should total 33.78 (note this would represent the 2nd to 30th period of compounding as the first payment was made in the prior month).

Thanks for the help.

[DonkeyOte]

Still not 100% sure I follow given latest sample shows payment in first period whereas last example implied otherwise...

Using sample file / expected results etc

I named C14 "growth"

I named C15 "freq"
I entered 1 into C15 to denote monthly frequency

Please Login or Register  to view this content.

[CDavid]

Your formula did the trick Thanks so much for your help and time. It's greatly appreciated.

Regarding your comment on where the first payment occurred in my examples, I was mainly just trying to figure out the sum of payments due between two dates. Whether the first payment was in the first column/period or any subsequent one wasn't the key concern.

I modified the second formula slightly to reflect the actual payment amount in each period (added "amount" instead of the number 1 after SUMPRODUCT).

=IF(ISERR(C$1),"",SUM(amount,IF(C$1>1,SUMPRODUCT(amount*((1+growth)^ROW($A$1:INDEX($A:$A,C$1-1))))))-SUM($B6:B6))

Thanks again.