• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating payment using VBA

WMO102

New Member
Hello,

Calculating payment lines with different scenarios and periods

I am working on contracts data in order to calculate the total amount at a certain point of time for reporting. All contracts have different durations and there might be multiple payment lines for that same contract. Some contracts might have multiple occurences such as 0, 1, 2, 3 (ie. occurence 0 means the original contract, occurence 1 means the contract has been modified due to change in contract services, amount or term. There might be multiple payment lines under each occurance such as payment line # 1.0, 2.0 incrementally by 1. Howerver, the payment lines might be modified too due to changes as above. If the payment line 1.0 is modified, the modified line is 1.01. The fixed monthly amount is provided for all occurence, so I want to calculate the total amount of all the contract for the certain point of time, such as by Feb 29, 2012. For some contract the start date is not the first day of the month, so I need to consider calculating the partial amount as well.


I tried using IF(AND) function to take all these different scenario into account, but ends up with super long formula, which confused me at the end. So, I wonder if I can get help on the VBA code naming a variable that different formula can be applied to different scenario.


I uploaded the sample workbook below. Appreciate your input.

https://docs.google.com/spreadsheet/ccc?key=0ArVVgDn9sCp8dFdlNk85UzJIMkZkTVhrZGZlcFpoalE
 
Hi WMO102,


Welcome to the forum, will the newly modified line lets say 1.0.1 will replace the 1.0 or will added besides it (i mean underneath it)?? Can i have a sample file?


Regards,

Faseeh
 
Hi Faseeh,

Thanks. Can you see the sample files by clicking this link?

https://docs.google.com/spreadsheet/ccc?key=0ArVVgDn9sCp8dFdlNk85UzJIMkZkTVhrZGZlcFpoalE


To calculated the contract in full length, the modified line 1.01 needs to be added besides line 1.0 since line 1.0 has been end dated once that line is modified.
 
Hi ,


I downloaded your workbook , but am still wondering what is to be done.


Your file has the following contract IDs : 2240 , 2300 , 2643 , 2000472 and 2653. Can you take any one of these as an example , and explain what should be done ?


There are some dates in the first row of columns I through T ; do you want that values should appear in these columns , in each row ? If so , what should these values be i.e. how are these values to be calculated ?


Narayan
 
Hi,

You can just use the payment line dates do the calculation. The contract dates stay unchanged even though the contract has been modified with different occurrences. The occurrence dates are inline with the payment line dates that track the modification dates. I would like to calculate the value of the contract from Feb 28, 2011 to Feb 29, 2012 excluding the overlapping values in previous occurrences and payment lines.


Occurrence is copied down from the original contract as the original contract was end dated. There is also a case of voiding contract if the occurrence dates overlap with the previous occurrence. For example, 2240 occ#1 has the same start and end payment line date as 2240 occ #0, so the later occurrence can replace the previous occurrence. The payment lines overlap as well in later occurrences. 2240.0.1.0 and 2240.1.1.0 or 2240.0.2.0 and 2240.1.2.0 have exactly the same start and end payment line dates because the payment line 1.0 and 2.0 is the original payment line which is copied down from the original payment line. When the payment line 1.0 is modified, 1.01 shows the new start and end payment line date. 1.0 is from April 1, 2011 to April 30, 2011(which has been shortened), while 1.01 is from May 1, 2011 to May 31, 2012. Thus, I need the aggregate value of payment line 1.0 and 1.01.


For the calculation, I need the total value of the contract 2240 from Feb 28, 2011 to Feb 29, 2012 only. Thus, I need filter out the dates that is outside of the reporting period. Hopefully, it makes sense to you. I have updated the link below.

https://docs.google.com/spreadsheet/ccc?key=0ArVVgDn9sCp8dFdlNk85UzJIMkZkTVhrZGZlcFpoalE
 
Hi ,


Sorry , but it still is not clear to me.


If we take the example contract you have mentioned , 2240 , the dates against this contract are :


April 1 , 2011 and May 31 , 2012 for the Contract.


April 1 , 2011 and May 1 , 2011 , April 30 , 2011 and May 31 , 2012 for the Occurrence.


April 1 , 2011 and May 1 , 2011 , April 30 , 2011 and May 31 , 2012 for the Payment Line.


You also say that you want the total value of this contract from Feb. 28 , 2011 through Feb. 29 , 2012. Is this period fixed for all the contracts , or is this just an example date that you have used ?


If we assume that this is fixed for all the contracts , what is the significance of the dates in row 1 of columns I through T ? These do not have any relation with the dates within columns B through G , nor do they relate in any way with the Feb. dates you have mentioned.


Can you please upload your worksheet , after filling out the cells where you want formulae to appear , or the cells which you wish to be calculated using VBA , with values , at least for the example contract 2240 ?


Narayan
 
Columns I through T is for verfication/double checking purpose. Once the VBA codes is tested fine, I will get rid of the columns. If the range is calculated correctly, the fixed monthly amount will appear under that corresponding month. I have put notes and an sample result for the first contract in the highlighted columns for the first contract. The total value from Feb. 28 , 2011 through Feb. 29 , 2012 is fixed for all contract in the list.


My difficulty part is that I am not sure how to write the code to selected the payments lines dates for that certaion point of time feb-2011-feb, 2012, and delete the overlapping payment lines. Thanks a lot


I have updated the link, pls let me know if not working.
 
Hi Naryan,


Did the previous explanation make sense to you. Pls feel free to let me know. Thanks a lot!
 
Hi Narayan, Just one more thing to add. Some dates are not always the whole month, so I need to calculate the partial value too.


Fixed monthly amount * (days in the month)
 
Hi ,


Can you please download the following worksheet , and confirm whether the values entered in all the cells are correct ? I have manually entered them for the present ; once I get your confirmation , I can upload the workbook , either with formulae or with VBA code , to do what is to be done. Which would you prefer ?


https://docs.google.com/spreadsheet/ccc?key=0AkKMpuzr3MTVdFdaOXVFQTVhRHdxaE14WlpPZzB1UlE


Narayan
 
Hi Narayan,

I typed in the date on F2: R2, from Feb 1, 2011 to Feb 1, 2012 in my downloaded sheet.

Here are my feedbacks:

• I want to report the aggregate value from Feb 1, 2011 to Feb 29, 2012 only for all the records. Any value happened before Feb 1, 2011 or after Feb 29, 2012 should not be counted.

Ie: 2240.1.1.01 – the payment line date is from May 1, 2011 to May 31, 2012. A total of 10 months should be counted as there are the months that overlap with my reporting period (Feb 1, 2011-Feb 29, 2012)

• Can you also provide me the formula for calculating the corresponding monthly value under each month starting from column F.


If you already have both formula and VBA codes, can you send me both?


Thank you so much!!
 
Hi ,


Can you check the following workbook ?


https://docs.google.com/spreadsheet/ccc?key=0AkKMpuzr3MTVdHJyamYxX2tFVlAtejdoUWEwNmkyemc


Please note that all the values in all the cells ( including row 2 , starting from F2 ) are through formulae ; verify that these values are correct , and let me know if there are any mistakes.


Narayan
 
Hi Narayan,

The value and date in the excel sheet are "#NAME". Should I put date value starting from F2 . If I put the date in, I would override formulae.
 
Hi ,


I am not sure what the problem is , but I think your problem is similar to an earlier problem , for which Hui had given the solution. Can you check it out here ?


http://chandoo.org/forums/topic/rent-income-increment-one-year-after-commencement


I'll quote from Hui's post there :


The Eomonth function requires the Excel Analysis Toolkit addin

If your using Excel 97 - 03 you will need to load the Excel Analysis Toolkit addin which is not installed by default

Goto Tools, Addins and select Analysis Toolkit


Let me know if this solves your problem.


Narayan
 
Hi Narayan,

My version is 2007 and I suppose the toolkit is built in?

Maybe it's the prolbem of the shared file in Gmail. could you send me the excel file? wenna_mo@hotmail.com


Thank you@
 
Hi Narayan,

The values are correct under condition that the date range I asked is from Feb 2011 to Feb 2012. However, because the date range on the excel is starting from April 2011, there is only one criteria in the sumif formular such as <=Feb 2012. Will the formular be changed , if I want the reporting date to be from July 2011 to Mar 2012?

Thank you!
 
Hi ,


There are two points to be noted :


1. The values in column F through AI or even beyond , are put in those cells based on whether the payment dates in columns B and C are within the dates in each of the cells F2 , G2 , H2 ,... ; the date in F2 is the minimum date in columns B and C ; if the earliest date in these two columns is April 1 , 2011 , then there is no point in starting with an earlier date in F2 onwards. The last date in row 2 is one month after the maximum date in columns B and C.


2. The SUM in column E is based on all payments which have been made prior to the date in E2 ; if you change the date in E2 , the amounts in column E will change ; at present , the formula used in column E is a SUMIF , which allows only one condition ; if you want that two conditions should be used to total the payments viz. only those payments made after a certain date , and before a second date should be summed up , then we will have to use SUMIFS which allows multiple conditions.


Please let me know where this first date will be entered ; the second date can continue to be in E2.


Narayan
 
Hi Narayan,

1. I guess the sample excel data is too small. In my actual worksheet, some of the contracts' duration is more than 2 years. To report the contract value for a portion of the contract period, I will need to set conditions that can select between dates and the minimum date function can not work.


2. Can you kindly revise the sumif formula that can select between dates, such as the first date is July 1, 2011 and the last day stays Feb 29, 2012.


Thank you!
 
Back
Top