Prorated Payment Help
I have a situation where my client has asked me to create for them a monthly payment walk based on a total contract value, payments are made on the first day of the month. My issue is that my start and stop dates can occur in the middle of the month. I would really like to identify a formula that can "automagically" make the walk work based on the input of:
1. Monthly Amount
2. Total Contract Value
3. First Day of Charge (FDOC)
4. Last Day of Charge (LDOC)
5. Calculated total number of Months.
I have included a sample of data that shows the different scenarios I am trying to cover with manually calculated expected outcomes.
Scenario 1 - FDOC and LDOC are both on the First and Last Day of the month, making the payments for the full month equal to the number of months in the agreement.
Scenario 2 - FDOC is in the middle of the month and LDOC is the last day of the month.
Scenario 3 - FDOC is on the first of the month and LDOC is in the middle of the month.
Scenario 4 - Both FDOC and LDOC are in the middle of a month.
My spreadsheet is almost 1000 line items and it would really be terrible if i have to create a calculation manually by line item.
Any help that can be provided would be greatly appreciated.
Last edited by eflair; 08-24-2011 at 09:22 PM. Reason: Adjusted Post Title
This formula in G3 filled down to G12 then across to AO12 generates results that correspond to your calculations...
Beau Nydal=IF(MIN(EOMONTH(G$2,0),$E3)-MAX(G$2,$D3)+1<=0,"NA",(MIN(EOMONTH(G$2,0),$E3)-MAX(G$2,$D3)+1)/DAY(EOMONTH(G$2,0))*$B3)
I can't thank you enough! It worked beautifally!!
There's a workbook with examples of several different ways to do this at http://www.box.net/shared/7xm2kt408n
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks