Log In
Register
Home
Active Member
April 24, 2020
Offline
I don't know where to place this or where to look for answers. Don't know if it can be done easily or if it will require some sophisticated work. Examples of the scenarios I would like to have the spreadsheet calculate are as follows:
- HOA dues received after Feb. 28th but before April 15th = no lien placed on property and no late fee. So, homeowner will owe $300.
- HOA dues received after Feb. 28th but before May 15th = a lien will be placed on property, $14 will be added to cover the cost of the lien, additionally there will be a late fee of $50 for April. So, homeowner will need to pay $364 (300+14+50).
- HOA dues received after Feb. 28th but before Sept. 15th = a lien will be placed on property, $14 will be added to cover the cost of the lien, additionally there will be a late fee of $50 for April, May, June, July, and Aug. So, the homeowner will need to pay $564 (300+14+250).
- HOA dues recived after Feb. 28th but after Sept. 15th = a lien will be placed on property, $14 will be added to cover the cost of the lien, additionally there will be a late fee of $50 for April, May, June, July, Aug, and Sept. (300+14+300).
I've attached a spreadsheet of how I was thinking of setting up the spreadsheet. I've manually placed the late fee in column H but I would like to have column H automatically calculated. The late fee may have to be calculated over years.
Trusted Members
December 20, 2019
Offline
Is the attached what you are after? My columns are in a kind of orange colour
Column F works out the lien date to 2 months from the due date rounded down to the 15th, column G is just an if Lien date vs calc date then column H works out the number of months failed and multiplies by 50.
Probably different ways but seems to do work so far (i really hope it is okay as its the first time i have been able to use DATEDIF - https://www.myonlinetraininghu.....on-datedif
Purfleet
Active Member
April 24, 2020
Offline
Thank you for the information. However, DATEIF doesn't appear to work for what I am trying to calculate because it appears to calculate based on the standard month where I'm trying to make calculations based on the middle of the month. I also noticed I didn't word the examples in the text portion correctly in the 4/24/20 post so I've update the text and hopefully explained things better. I've also updated the attached spreadsheet. In the attached spreadsheet, I would like to have column H2 - H6 automatically calculate the months. In the attached spreadsheet, the cells currently have the manually entered numbers I want the formula to automatically calculate.
This is my attempt to explain the scenario. Fees have a due date of 02/28/20 (column D) and there will be a 15 day grace period before late fees are calculated. The number of months to be used to calculate the late fee will be the difference between the "Report Date" (column A) and "Due Date" (column D). So, payments received from 03/01/20 to 3/15/20 = 0 months for late fees, payments received 03/16/20 to 04/15/20 = 1 month for late fees, payments received 04/16/20 - 05/15/20 = 2 months for late fees, payments received 05/16/20 - 06/15/20 = 3 months for late fees, etc.). A more detailed description, which includes all the moneys involved and the total amount due, is included below (hope I got the detail description correct this time).
HOA dues received after Feb. 28th (29th leap year) but before March 15th = no lien placed on property and no late fee. So, homeowner will only owe $300.
HOA dues received after March 15th but before April 16th (payment received April 15th) = a lien will be placed on the property, $14 will be added to cover the cost for filling and removing the lien, additionally there will be a $50 late fee for March. So, the homeowner will have to pay $364 to have the lien removed ($300+14+50).
HOA dues received after March 15th and after April 15th (payment received April 16th) = a lien will be placed on the property, $14 will be added to cover the cost for filling and removing the lien, additionally there will be a $100 late fee for March and April. So, the homeowner will have to pay $414 to have the lien removed ($300+14+100).
HOA dues received after March 15th but before Sept. 16th (payment received Sept. 15th) = a lien will be placed on the property, $14 will be added to cover the cost for filling and removing the lien, additionally there will be a $50 late fee for March, April, May, June, July, and Aug. So, the homeowner will have to pay $614 to have the lien removed ($300+14+300).
HOA dues received after March 15th but after Sept. 15th (payment received Sept. 16th) = a lien will be placed on the property, $14 will be added to cover the cost for filling and removing the lien, additionally there will be a $50 late fee for March, April, May, June, July, Aug, and Sept. So, the homeowner will have to pay $664 to have the lien removed ($300+14+350).
I've also updated the spreadsheet to match the above description as I made errors in the spreadsheet attached to the 04/24/20 posting as well. The closest way I've come up with automatically calculating the months was with an IF statement but it has problems.
The numbers I manually added in cells H2 - H6 are what I want to end up with and hope there is a way of having Excel manually calculate. The spreadsheet in rows 2 - 6 is what I want to end up with. The speadsheet in rows 23 - 27 is where I'm attempting to figure out how to get to the spreadsheet in rows 2 - 6.
The IF statement I created to calculate the months and the results can be found in cells H23 - H27 and then I use the TRUNC function in cells I23 - I27 to eliminate decimals so a whole number would be used in the calculation to calculate the "Late Fee Amount" and "Total Amount Due" columns. However, the results in H23 - H27 (truncated) do not always result in the same numbers in cells H2 - H6 which are the ones I want.
At this point, I'm thinking this either can't be done or will require Excel knowledge beyond my knowledge and ability. Any assistance is appreciated, even if it is to let me know this can't be done.
As a side note, I don't know if just replying will allow others to see my updated post and be able to provide additional thoughts on resolving this issue or if it would be better for me to start a new post. Also, I posted this in the General forum and if it needs to be posted in another forum for more specific advice please let me know. Any feedback on how to handle this post will be appreciated.
Thanks
Trusted Members
December 20, 2019
Offline
i thought the 15th issue had been covered by the calc i added in column H but you are right the 15th/16th issue was a problem, just no examples in the test data.
As it turns out that the standard for mid month accounting is (regardless of month length) that the 15th is the current month and the 16th is the next month - if you adhere to that standard we can go with =IF(DAY(A37)<=15,DATEDIF(D37,A37,"M"),IF(DAY(A37)>=16,DATEDIF(D37,A37,"M")+1))
1 Guest(s)
