Tennisguuy
Well-known Member
- Joined
- Oct 17, 2007
- Messages
- 564
- Office Version
- 2016
- Platform
- Windows
I'm trying to create a payment schedule in excel and I got it to work when they payment schedule is based on a percentage down and a number of fixed installments. Below is currently how I have it set up.
Column B Column C
Property Premium 100,000 (C7)
Brokers Commission 10%
Down Payment % 25% (C8)
# of Equal Installments 5
Policy Term (1 or 2 YR)
Effective Date 1/1/2014 (C12)
Expiration Date 1/1/2015 (C13)
A B C D
Due Date Payment Amt Commission Net Payment
1/1/2014 10,000
2/1/2014 18,000
3/1/2014 18,000
4/1/2014 18,000
5/1/2014 18,000
6/1/2014 18,000
In cell C13 I have the following formula IF(C11=1,DATE((YEAR(C12)+1),(MONTH(C12)),(DAY(C12)))). Once the user enters in the effective date in cell C11 the formula will automatically enter in the expiration date for one year later
In cell A16 I have the following formula if(C12,"","",C12), the first due date will always be the effective date. In the above example the premium is 100,000 and the down payment is 10%, the first payment will always be the percent down. The remaining payments will be the number of equal payments divided by the remaining balance. In this case 90,000 is the remaining balance and the number of payments is 5 so you get 18,000.
I have the due dates adjust to the remaining number of installments. In the above example I have this formula in cell A17 IF(C7="","",IF(C10>=1,DATE(YEAR($C$12),(MONTH($C$12)+1),DAY($C$12)),""))
In cell A18 IF(C7="","",IF(C10>=2,DATE(YEAR($C$12),(MONTH($C$12)+2),DAY($C$12)),"")) this formula repeats down to cell A28.
In cell B16 I have the following formula if(C7="","",V9) Is cell V8 I have a formula this formula C7*C8 to calculate an estimated down payment. In the example above it would take 1000,00 *.10 to to get 10,000. However in this example everything rounds perfectly. In most cases it will not which is why I calculate a estimated down payment. I have a formula in cell V10 that takes the remaining balance and divides it by the number of installments in this case is 90,000/5=18,000. Then in cell V9 I take the sum of these monthly payments and minus the premium in cell C7 and I make this my actual down payment because the total of the down payments plus the total number of installments has to equal the premium in C7. In this example everything works out great but in real life cases the number of installs left once you sum them up and add them to the down payment will not equal the premium in C7. This is why I adjust the down payment after calculating the installments. It's only usually a buck or two off but it has to equal the premium in cell C7 so I adjust the down payment.
As I mentioned above this setup is working. However where I am struggling and not sure if this can be done but I thought I would ask. We have other payment options that are not a percentage down and a number of fixed installments. We have options of 1/3 payments which is just the premium in cell C7 divided by 3 for 1/3. We also have quarterly installments which is just the premium in cell C7 divided by 4. However the problem I'm having is the on the 1/3 payment option and let's use the 100,000 in premium in the above example in cell C7 is the the first one third would be due on the effective date of 1/1/2014. However the next payment would not be due until 5/1/2014 and the last payment would not be due until 9/1/2014. In the quarterly case the first payment would be due 1/1/2014, then 4/1/2014, 7/1/2014 and then 10/1/2014. In the above 1/3 month case the payment due would be 33,333,33 for 1/1/2014 and 5/1/2014 but on 9/1/2014 33,333.34 would be due because it has to equal exactly which is why I stated above I would adjust the down payment to make for the difference.
I'm not sure how to adjust my formulas I have in my due date column to adjust for the dates and the payments when the monthly due are not in succession.
My last problem is that we also have a payment option for a two year policy term this is a why I have in cell C11 a 1 or 2 for 1 year or two year. If 2 was enter in cell I tried adjusting my formula I showed above to this. IF(C11=1,DATE((YEAR(C12)+1),(MONTH(C12)),(DAY(C12)))),IF(C11=2,DATE((YEAR(C12)+2),(MONTH(C12)),(DAY(C12)))) but I got an error. I'm also having a problem similar to the above with the due dates and payment calculation
In the above example lets say the 100,000 premium in cell C7 is a two year premium. We would take 10% of the down and the number of installments would be 20 ten for the first year and 10 for the second year. Therefore 10,000 would be due on 1/1/2014 and then on 2/1/2014 through 11/1/2014 $4,500 would be due and this on 1/1/2015 the other ten installments would be due starting a 1/1/2014 to 10/1/2014. The example again as far as number installments works out great when just taking the percentage down and the number installments but in almost every case either down payment or one of the installments would have to adjust so that the all the payments when added together equals exactly the premium.
My last problem is again with the two year option but with the quarterly installments. In this case in the example I gave above for the one year quarterly the same would follow for the two year quarterly example half of the premium would need to following the first year schedule and the other half would follow the same schedule except just a year later on the dates.
I'm not sure if all of these options can be done in excel with the set up above. If they can be done but the setup needs to change I can change the setup.
Column B Column C
Property Premium 100,000 (C7)
Brokers Commission 10%
Down Payment % 25% (C8)
# of Equal Installments 5
Policy Term (1 or 2 YR)
Effective Date 1/1/2014 (C12)
Expiration Date 1/1/2015 (C13)
A B C D
Due Date Payment Amt Commission Net Payment
1/1/2014 10,000
2/1/2014 18,000
3/1/2014 18,000
4/1/2014 18,000
5/1/2014 18,000
6/1/2014 18,000
In cell C13 I have the following formula IF(C11=1,DATE((YEAR(C12)+1),(MONTH(C12)),(DAY(C12)))). Once the user enters in the effective date in cell C11 the formula will automatically enter in the expiration date for one year later
In cell A16 I have the following formula if(C12,"","",C12), the first due date will always be the effective date. In the above example the premium is 100,000 and the down payment is 10%, the first payment will always be the percent down. The remaining payments will be the number of equal payments divided by the remaining balance. In this case 90,000 is the remaining balance and the number of payments is 5 so you get 18,000.
I have the due dates adjust to the remaining number of installments. In the above example I have this formula in cell A17 IF(C7="","",IF(C10>=1,DATE(YEAR($C$12),(MONTH($C$12)+1),DAY($C$12)),""))
In cell A18 IF(C7="","",IF(C10>=2,DATE(YEAR($C$12),(MONTH($C$12)+2),DAY($C$12)),"")) this formula repeats down to cell A28.
In cell B16 I have the following formula if(C7="","",V9) Is cell V8 I have a formula this formula C7*C8 to calculate an estimated down payment. In the example above it would take 1000,00 *.10 to to get 10,000. However in this example everything rounds perfectly. In most cases it will not which is why I calculate a estimated down payment. I have a formula in cell V10 that takes the remaining balance and divides it by the number of installments in this case is 90,000/5=18,000. Then in cell V9 I take the sum of these monthly payments and minus the premium in cell C7 and I make this my actual down payment because the total of the down payments plus the total number of installments has to equal the premium in C7. In this example everything works out great but in real life cases the number of installs left once you sum them up and add them to the down payment will not equal the premium in C7. This is why I adjust the down payment after calculating the installments. It's only usually a buck or two off but it has to equal the premium in cell C7 so I adjust the down payment.
As I mentioned above this setup is working. However where I am struggling and not sure if this can be done but I thought I would ask. We have other payment options that are not a percentage down and a number of fixed installments. We have options of 1/3 payments which is just the premium in cell C7 divided by 3 for 1/3. We also have quarterly installments which is just the premium in cell C7 divided by 4. However the problem I'm having is the on the 1/3 payment option and let's use the 100,000 in premium in the above example in cell C7 is the the first one third would be due on the effective date of 1/1/2014. However the next payment would not be due until 5/1/2014 and the last payment would not be due until 9/1/2014. In the quarterly case the first payment would be due 1/1/2014, then 4/1/2014, 7/1/2014 and then 10/1/2014. In the above 1/3 month case the payment due would be 33,333,33 for 1/1/2014 and 5/1/2014 but on 9/1/2014 33,333.34 would be due because it has to equal exactly which is why I stated above I would adjust the down payment to make for the difference.
I'm not sure how to adjust my formulas I have in my due date column to adjust for the dates and the payments when the monthly due are not in succession.
My last problem is that we also have a payment option for a two year policy term this is a why I have in cell C11 a 1 or 2 for 1 year or two year. If 2 was enter in cell I tried adjusting my formula I showed above to this. IF(C11=1,DATE((YEAR(C12)+1),(MONTH(C12)),(DAY(C12)))),IF(C11=2,DATE((YEAR(C12)+2),(MONTH(C12)),(DAY(C12)))) but I got an error. I'm also having a problem similar to the above with the due dates and payment calculation
In the above example lets say the 100,000 premium in cell C7 is a two year premium. We would take 10% of the down and the number of installments would be 20 ten for the first year and 10 for the second year. Therefore 10,000 would be due on 1/1/2014 and then on 2/1/2014 through 11/1/2014 $4,500 would be due and this on 1/1/2015 the other ten installments would be due starting a 1/1/2014 to 10/1/2014. The example again as far as number installments works out great when just taking the percentage down and the number installments but in almost every case either down payment or one of the installments would have to adjust so that the all the payments when added together equals exactly the premium.
My last problem is again with the two year option but with the quarterly installments. In this case in the example I gave above for the one year quarterly the same would follow for the two year quarterly example half of the premium would need to following the first year schedule and the other half would follow the same schedule except just a year later on the dates.
I'm not sure if all of these options can be done in excel with the set up above. If they can be done but the setup needs to change I can change the setup.
