I've searched the other postings but cannot find an answer. This is probably a simple answer for you, but I'm stumped.
Need a formula for Final payment date based on given Start date and number of total scheduled payments (Term) on a Semi-Monthly basis (Days are static).
Example:
Start Date A1=8/9/2015
Term A2=5
Final Payment date A3=10/9/2015 (Formula)
FYI: Here is the payment schedule
8/9/2015
8/24/2015
9/9/2015
9/24/2015
10/9/2015
Thank you in advance
Stephen
Try in A3:
=EDATE(A1,INT((A2-1)/2))+MOD(A2-1,2)*15
Quang PT
Comment withdrawn - I was wrong...
Last edited by Glenn Kennedy; 11-22-2015 at 12:28 PM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
OK, BeBo02199 Genius, but I ran into one snag testing another date. It worked on the date start date 8/9/2015 on several other examples, but not on these....Mmmm Any thoughts?
Wrong example data (Removed examples)
Last edited by stephenvward; 11-22-2015 at 01:57 PM. Reason: Wrong example data (Removed examples)
Surely you said that the payment dates were STATIC - on the 9th and 24th of each month. Now it seems that they are NOT static, but can be on pretty much ANY date. What rules goven the dates of the bimonthly payments.
I tried A1=08/09/2015, A2=96, then A3=07/24/2019 is far away from 2/10/2019 (your first sample)
Could you re-post sample again with new example?
I guess it might comes from leap year.
Sorry about that, I thought I posted but didn't see it. Strange. Thanks for your help
Example1
Start Date=10/16/2013
Term=63
Final Date=5/16/2016
Example2
Start Date=9/17/2014
Term=72
Final Date=9/2/2017
Example3
Start Date=2/9/2014
Term=78
Final Date=4/25/2017
You may be on to something....
Let go through your logic
Example2
Start Date=9/17/2014
Term=72
Final Date=9/2/2017
Term=72 => number of moths = (72-1)/2 = 35.5 = 35 months and 15 days
9/17/2014 + 35 months = 08/17/2017
+15 days = 09/01/2017
Is is base on true days of month (31 days of August)
But you expect it to be 09/02/2017 (30 days of August). Is it base on month with 30 days?
I think it would be 360 days. The payment should fall on the same day for each month. So if 1st payment was 9/17/2014, 2nd payment would be 10/2/2014, third would be 10/17/2014, 4th would be 11/2/2014
I noticed something earlier that may have confused me further. In example # 1, the months of February, April, June and November, this date needs to be adjusted since the second payment date fell on the 31st. So if the start date or 2nd payment date fall on the 31st in April, June, or Nov the date needs to adjust to the 30th. If in Feb the date adjust to the end of the month.
Sorry, this just got more confusing.
I have downloaded a spreadsheet. Maybe this helps.
I afraid not to follow you. I apply my original formula in column E sheet 'example 1'. Check it and add your expected results in column F.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks