| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
pls help again!
Thanks for the help Roger. It worked......also, how can i use this function
if there are more than 7 installments to be paid? Shirley Hi Shirley Assuming you enter proper Excel Dates in D1 onward, e.g. 01 Feb 2006 formatted to just show Month and year with FormatCellsNumberCustommmmyy Also, assuming you use a proper Excel date in column B, then enter the following formula into cell D2 and copy across as far as required. Copy the whole row down as far as required. =IF($A3="","",IF(D$1<$B3,"", IF(DATEDIF($B3,D$1,"m")=0,$C3*50%, IF(DATEDIF($B3,D$1,"m")=5,$C3*25%, IF(DATEDIF($B3,D$1,"m")=10,$C3*25%,""))))) Until you enter a value greater than 0 in your flag field, the values will remain as 0 If you need more help on the undocumented Datedif function, take a look at Chip Pearson's site http://www.cpearson.com/excel/datedif.htm -- Regards Roger Govier "shirley" wrote in message ... hi, i have a small problem....here's what it looks like: A(flag) B(date of sale) C(sale amt) D(Feb06) E(Mar06) F(Apr06)..... 1 0 25000 ok heres where the problem is.....when there is a sale, the flag in col A changes from 0 to 1. then the month of sale is entered in col B..... now the first installment of the payment has to start in the month of the sale....for eg. if the first sale is for 25000$ in the month of Mar06, then 50% of $25000 shud appear in Mar06 column and 25% of $25000 in the col 5 months from Mar06(i.e. in Aug06) and the last 25% in the col 10 months from Mar06. kindly help me if my problem is clear! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pls help again!
As you have set it up there are only 3 instalments as you have choosen 50% 25% and 25% of the total as the repayments. When do you want the seven repayments to be made and what percentage of the total are they? Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=517204 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pls help again!
Dav,
assume that the 2nd payment is 75% instead of the 25% that i gave in the example and that this 75% has to be paid in 8 equal quarterly installments. I hope its clear... Thanks "Dav" wrote: As you have set it up there are only 3 instalments as you have choosen 50% 25% and 25% of the total as the repayments. When do you want the seven repayments to be made and what percentage of the total are they? Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=517204 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pls help again!
Hi Shirley
Just a small point, it would have been better to stay in the same thread, rather than post a new thread. Luckily, I spotted this request also. As Dav has said, we need more information about when these instalments will take place, and the amounts per instalment. What is going to be the "trigger" that determines one is 3 instalments, another is 4 or 5 or 7? Will it be the flag? -- Regards Roger Govier "shirley" wrote in message ... Thanks for the help Roger. It worked......also, how can i use this function if there are more than 7 installments to be paid? Shirley Hi Shirley Assuming you enter proper Excel Dates in D1 onward, e.g. 01 Feb 2006 formatted to just show Month and year with FormatCellsNumberCustommmmyy Also, assuming you use a proper Excel date in column B, then enter the following formula into cell D2 and copy across as far as required. Copy the whole row down as far as required. =IF($A3="","",IF(D$1<$B3,"", IF(DATEDIF($B3,D$1,"m")=0,$C3*50%, IF(DATEDIF($B3,D$1,"m")=5,$C3*25%, IF(DATEDIF($B3,D$1,"m")=10,$C3*25%,""))))) Until you enter a value greater than 0 in your flag field, the values will remain as 0 If you need more help on the undocumented Datedif function, take a look at Chip Pearson's site http://www.cpearson.com/excel/datedif.htm -- Regards Roger Govier "shirley" wrote in message ... hi, i have a small problem....here's what it looks like: A(flag) B(date of sale) C(sale amt) D(Feb06) E(Mar06) F(Apr06)..... 1 0 25000 ok heres where the problem is.....when there is a sale, the flag in col A changes from 0 to 1. then the month of sale is entered in col B..... now the first installment of the payment has to start in the month of the sale....for eg. if the first sale is for 25000$ in the month of Mar06, then 50% of $25000 shud appear in Mar06 column and 25% of $25000 in the col 5 months from Mar06(i.e. in Aug06) and the last 25% in the col 10 months from Mar06. kindly help me if my problem is clear! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pls help again!
Hi Roger
ok. there are three overall installments to be made, 20%, 75% and 5%. Now according to the formula, if the sale takes place in Mar-06, the first installment of 20% of sale will show up under column Mar-06. The 75% has to be split into 8 part installments payable every three months after Mar-06. The final 5% will be paid 3 months after the last of the 8 part-installments is completed. hope this makes things a little clearer Shirley "Roger Govier" wrote: Hi Shirley Just a small point, it would have been better to stay in the same thread, rather than post a new thread. Luckily, I spotted this request also. As Dav has said, we need more information about when these instalments will take place, and the amounts per instalment. What is going to be the "trigger" that determines one is 3 instalments, another is 4 or 5 or 7? Will it be the flag? -- Regards Roger Govier "shirley" wrote in message ... Thanks for the help Roger. It worked......also, how can i use this function if there are more than 7 installments to be paid? Shirley Hi Shirley Assuming you enter proper Excel Dates in D1 onward, e.g. 01 Feb 2006 formatted to just show Month and year with FormatCellsNumberCustommmmyy Also, assuming you use a proper Excel date in column B, then enter the following formula into cell D2 and copy across as far as required. Copy the whole row down as far as required. =IF($A3="","",IF(D$1<$B3,"", IF(DATEDIF($B3,D$1,"m")=0,$C3*50%, IF(DATEDIF($B3,D$1,"m")=5,$C3*25%, IF(DATEDIF($B3,D$1,"m")=10,$C3*25%,""))))) Until you enter a value greater than 0 in your flag field, the values will remain as 0 If you need more help on the undocumented Datedif function, take a look at Chip Pearson's site http://www.cpearson.com/excel/datedif.htm -- Regards Roger Govier "shirley" wrote in message ... hi, i have a small problem....here's what it looks like: A(flag) B(date of sale) C(sale amt) D(Feb06) E(Mar06) F(Apr06)..... 1 0 25000 ok heres where the problem is.....when there is a sale, the flag in col A changes from 0 to 1. then the month of sale is entered in col B..... now the first installment of the payment has to start in the month of the sale....for eg. if the first sale is for 25000$ in the month of Mar06, then 50% of $25000 shud appear in Mar06 column and 25% of $25000 in the col 5 months from Mar06(i.e. in Aug06) and the last 25% in the col 10 months from Mar06. kindly help me if my problem is clear! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pls help again!
Hi Shirley
OK, so lets see if we can come up with a generic solution. My assumptions. 1. All agreements have a deposit paid (of varying percentages) at the same date as in column B. 2. All agreements have a number of repayments at varying frequency of varying percentages. 3. All agreements have a terminal payment, made at the same frequency after the last regular payment as above, but this terminal payment can be set to 0%. With the above, I set up a table which I named as Rates holding the following data ( I am showing the headings going down the page to avoid email wrap, but they were set up going across the page) A: Total No. Payments B: Deposit (%) C: Frequency (months) D: Instalment (%) E: Terminal (%) So this latest request would have values of 8, 20%, 3, 12.5%, 5% Your original request would have values of 4, 50%, 5, 25.0%, 0% With the above table set up (add as many more types as you wish, as long as they conform to the above) then the formula becomes a fairly horrendous =IF(ISERROR(VLOOKUP($A2,Rates,1,0)),"", IF(D$1<$B2,"", IF(DATEDIF($B2,D$1,"m")($A2-1)*VLOOKUP($A2,Rates,3,0),"", IF(DATEDIF($B2,D$1,"m")=0,$C2*VLOOKUP($A2,Rates,2, 0), IF(DATEDIF($B2,D$1,"m")=($A2-1)*VLOOKUP($A2,Rates,3,0),$C2*VLOOKUP($A2,Rates,5, 0), IF(MOD(DATEDIF($B2,D$1,"m"),VLOOKUP($A2,Rates,3,0) )=0,$C2*VLOOKUP($A2,Rates,4,0), "")))))) I have assumed that you will enter the Total No. of Payments in the Flag column (A) of your results table. Basically the formula says If no values in table for that No. of Months does not exist, then nothing If Month in row 1 is before start date then nothing If number of months is greater than the total duration of contract, then nothing If month equals starting month, then loan * deposit percentage If number of months equals last month of contact, then loan * terminal percentage If month is a multiple of frequency then loan * instalment percentage Otherwise nothing. Note that you have to take 1 away from the total number of payments, as the initial deposit uses 1 of the counts. Hopefully from the above you will be able to deal with any type of contact that you have. -- Regards Roger Govier "shirley" wrote in message ... Hi Roger ok. there are three overall installments to be made, 20%, 75% and 5%. Now according to the formula, if the sale takes place in Mar-06, the first installment of 20% of sale will show up under column Mar-06. The 75% has to be split into 8 part installments payable every three months after Mar-06. The final 5% will be paid 3 months after the last of the 8 part-installments is completed. hope this makes things a little clearer Shirley "Roger Govier" wrote: Hi Shirley Just a small point, it would have been better to stay in the same thread, rather than post a new thread. Luckily, I spotted this request also. As Dav has said, we need more information about when these instalments will take place, and the amounts per instalment. What is going to be the "trigger" that determines one is 3 instalments, another is 4 or 5 or 7? Will it be the flag? -- Regards Roger Govier "shirley" wrote in message ... Thanks for the help Roger. It worked......also, how can i use this function if there are more than 7 installments to be paid? Shirley Hi Shirley Assuming you enter proper Excel Dates in D1 onward, e.g. 01 Feb 2006 formatted to just show Month and year with FormatCellsNumberCustommmmyy Also, assuming you use a proper Excel date in column B, then enter the following formula into cell D2 and copy across as far as required. Copy the whole row down as far as required. =IF($A3="","",IF(D$1<$B3,"", IF(DATEDIF($B3,D$1,"m")=0,$C3*50%, IF(DATEDIF($B3,D$1,"m")=5,$C3*25%, IF(DATEDIF($B3,D$1,"m")=10,$C3*25%,""))))) Until you enter a value greater than 0 in your flag field, the values will remain as 0 If you need more help on the undocumented Datedif function, take a look at Chip Pearson's site http://www.cpearson.com/excel/datedif.htm -- Regards Roger Govier "shirley" wrote in message ... hi, i have a small problem....here's what it looks like: A(flag) B(date of sale) C(sale amt) D(Feb06) E(Mar06) F(Apr06)..... 1 0 25000 ok heres where the problem is.....when there is a sale, the flag in col A changes from 0 to 1. then the month of sale is entered in col B..... now the first installment of the payment has to start in the month of the sale....for eg. if the first sale is for 25000$ in the month of Mar06, then 50% of $25000 shud appear in Mar06 column and 25% of $25000 in the col 5 months from Mar06(i.e. in Aug06) and the last 25% in the col 10 months from Mar06. kindly help me if my problem is clear! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
pls help again!
Hi Roger
Thanks for all the effort and help. I appreciate it. I will try this out and see if it works. Regards Shirley "Roger Govier" wrote: Hi Shirley OK, so lets see if we can come up with a generic solution. My assumptions. 1. All agreements have a deposit paid (of varying percentages) at the same date as in column B. 2. All agreements have a number of repayments at varying frequency of varying percentages. 3. All agreements have a terminal payment, made at the same frequency after the last regular payment as above, but this terminal payment can be set to 0%. With the above, I set up a table which I named as Rates holding the following data ( I am showing the headings going down the page to avoid email wrap, but they were set up going across the page) A: Total No. Payments B: Deposit (%) C: Frequency (months) D: Instalment (%) E: Terminal (%) So this latest request would have values of 8, 20%, 3, 12.5%, 5% Your original request would have values of 4, 50%, 5, 25.0%, 0% With the above table set up (add as many more types as you wish, as long as they conform to the above) then the formula becomes a fairly horrendous =IF(ISERROR(VLOOKUP($A2,Rates,1,0)),"", IF(D$1<$B2,"", IF(DATEDIF($B2,D$1,"m")($A2-1)*VLOOKUP($A2,Rates,3,0),"", IF(DATEDIF($B2,D$1,"m")=0,$C2*VLOOKUP($A2,Rates,2, 0), IF(DATEDIF($B2,D$1,"m")=($A2-1)*VLOOKUP($A2,Rates,3,0),$C2*VLOOKUP($A2,Rates,5, 0), IF(MOD(DATEDIF($B2,D$1,"m"),VLOOKUP($A2,Rates,3,0) )=0,$C2*VLOOKUP($A2,Rates,4,0), "")))))) I have assumed that you will enter the Total No. of Payments in the Flag column (A) of your results table. Basically the formula says If no values in table for that No. of Months does not exist, then nothing If Month in row 1 is before start date then nothing If number of months is greater than the total duration of contract, then nothing If month equals starting month, then loan * deposit percentage If number of months equals last month of contact, then loan * terminal percentage If month is a multiple of frequency then loan * instalment percentage Otherwise nothing. Note that you have to take 1 away from the total number of payments, as the initial deposit uses 1 of the counts. Hopefully from the above you will be able to deal with any type of contact that you have. -- Regards Roger Govier "shirley" wrote in message ... Hi Roger ok. there are three overall installments to be made, 20%, 75% and 5%. Now according to the formula, if the sale takes place in Mar-06, the first installment of 20% of sale will show up under column Mar-06. The 75% has to be split into 8 part installments payable every three months after Mar-06. The final 5% will be paid 3 months after the last of the 8 part-installments is completed. hope this makes things a little clearer Shirley "Roger Govier" wrote: Hi Shirley Just a small point, it would have been better to stay in the same thread, rather than post a new thread. Luckily, I spotted this request also. As Dav has said, we need more information about when these instalments will take place, and the amounts per instalment. What is going to be the "trigger" that determines one is 3 instalments, another is 4 or 5 or 7? Will it be the flag? -- Regards Roger Govier "shirley" wrote in message ... Thanks for the help Roger. It worked......also, how can i use this function if there are more than 7 installments to be paid? Shirley Hi Shirley Assuming you enter proper Excel Dates in D1 onward, e.g. 01 Feb 2006 formatted to just show Month and year with FormatCellsNumberCustommmmyy Also, assuming you use a proper Excel date in column B, then enter the following formula into cell D2 and copy across as far as required. Copy the whole row down as far as required. =IF($A3="","",IF(D$1<$B3,"", IF(DATEDIF($B3,D$1,"m")=0,$C3*50%, IF(DATEDIF($B3,D$1,"m")=5,$C3*25%, IF(DATEDIF($B3,D$1,"m")=10,$C3*25%,""))))) Until you enter a value greater than 0 in your flag field, the values will remain as 0 If you need more help on the undocumented Datedif function, take a look at Chip Pearson's site http://www.cpearson.com/excel/datedif.htm -- Regards Roger Govier "shirley" wrote in message ... hi, i have a small problem....here's what it looks like: A(flag) B(date of sale) C(sale amt) D(Feb06) E(Mar06) F(Apr06)..... 1 0 25000 ok heres where the problem is.....when there is a sale, the flag in col A changes from 0 to 1. then the month of sale is entered in col B..... now the first installment of the payment has to start in the month of the sale....for eg. if the first sale is for 25000$ in the month of Mar06, then 50% of $25000 shud appear in Mar06 column and 25% of $25000 in the col 5 months from Mar06(i.e. in Aug06) and the last 25% in the col 10 months from Mar06. kindly help me if my problem is clear! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
