Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
Hi,
I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X
Hi. You didn't quite define your F( ) function, but if I understand it correctly... = (x*((y + 1)^(-A + B + 1) - y - 1))/y -- HTH :) Dana DeLouis "To understand recursion, one must first understand recursion." "axr0284" wrote in message ... Hi, I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
Oh wait. When you say for n=1 to (b-a), the first term is actually
f(n-1) - f(0). See if this is better. If z is the number of terms (ie b-a) then perhaps: (x*((y + 1)^z - 1))/y -- HTH :) Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X Hi. You didn't quite define your F( ) function, but if I understand it correctly... = (x*((y + 1)^(-A + B + 1) - y - 1))/y -- HTH :) Dana DeLouis "To understand recursion, one must first understand recursion." "axr0284" wrote in message ... Hi, I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
No,
this does not compute what I need. "Dana DeLouis" wrote: Oh wait. When you say for n=1 to (b-a), the first term is actually f(n-1) - f(0). See if this is better. If z is the number of terms (ie b-a) then perhaps: (x*((y + 1)^z - 1))/y -- HTH :) Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X Hi. You didn't quite define your F( ) function, but if I understand it correctly... = (x*((y + 1)^(-A + B + 1) - y - 1))/y -- HTH :) Dana DeLouis "To understand recursion, one must first understand recursion." "axr0284" wrote in message ... Hi, I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
No,
this does not compute what I need. Can you give a simple example, and the first few terms of the sequence. -- Dana DeLouis "axr0284" wrote in message ... No, this does not compute what I need. "Dana DeLouis" wrote: Oh wait. When you say for n=1 to (b-a), the first term is actually f(n-1) - f(0). See if this is better. If z is the number of terms (ie b-a) then perhaps: (x*((y + 1)^z - 1))/y -- HTH :) Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X Hi. You didn't quite define your F( ) function, but if I understand it correctly... = (x*((y + 1)^(-A + B + 1) - y - 1))/y -- HTH :) Dana DeLouis "To understand recursion, one must first understand recursion." "axr0284" wrote in message ... Hi, I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
Here you go. I would use it to calculate my loan interest every day.
Daily interest percentage: 0.02 (Y) Starting loan amount 7695 (X) Starting day 01/11/08 (A) ending date 01/16/08 (B) In excel, if I do compute it manually it comes out like this Amount Date 7695 01/11/08 7696.598684 01/12/08 7698.197701 01/13/08 7699.797049 01/14/08 7701.39673 01/15/08 7702.996743 01/16/08 I would like to automate this process so that i don't need to calculate each date's amount to obtain the next one. I would put in the start and end data and excel would calculate what I owe on the end date. I hope it's clearer now. "Dana DeLouis" wrote: No, this does not compute what I need. Can you give a simple example, and the first few terms of the sequence. -- Dana DeLouis "axr0284" wrote in message ... No, this does not compute what I need. "Dana DeLouis" wrote: Oh wait. When you say for n=1 to (b-a), the first term is actually f(n-1) - f(0). See if this is better. If z is the number of terms (ie b-a) then perhaps: (x*((y + 1)^z - 1))/y -- HTH :) Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X Hi. You didn't quite define your F( ) function, but if I understand it correctly... = (x*((y + 1)^(-A + B + 1) - y - 1))/y -- HTH :) Dana DeLouis "To understand recursion, one must first understand recursion." "axr0284" wrote in message ... Hi, I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X
this does not compute what I need. Hi. The sum of the first 6 terms is: =SUM(A2:A7) - 46,193.98691 When I enter the equation: Sub Demo() Dim x, y, z x = 7695 y = 0.000207756229861 z = 6 ' (ie b-a) Debug.Print (x * ((y + 1) ^ z - 1)) / y End Sub It returns the same amount. 46193.98691 The value of y is probably not exact. =7695*(1+0.02%) does not exactly match your next value. In your table, if the next term was actually = 7695 * (1 + .02%) = 7696.5390 and copied down, then using y=.02/100 would give the same amount. 46,193.09116 -- HTH Dana DeLouis "axr0284" wrote in message ... Here you go. I would use it to calculate my loan interest every day. Daily interest percentage: 0.02 (Y) Starting loan amount 7695 (X) Starting day 01/11/08 (A) ending date 01/16/08 (B) In excel, if I do compute it manually it comes out like this Amount Date 7695 01/11/08 7696.598684 01/12/08 7698.197701 01/13/08 7699.797049 01/14/08 7701.39673 01/15/08 7702.996743 01/16/08 I would like to automate this process so that i don't need to calculate each date's amount to obtain the next one. I would put in the start and end data and excel would calculate what I owe on the end date. I hope it's clearer now. "Dana DeLouis" wrote: No, this does not compute what I need. Can you give a simple example, and the first few terms of the sequence. -- Dana DeLouis "axr0284" wrote in message ... No, this does not compute what I need. "Dana DeLouis" wrote: Oh wait. When you say for n=1 to (b-a), the first term is actually f(n-1) - f(0). See if this is better. If z is the number of terms (ie b-a) then perhaps: (x*((y + 1)^z - 1))/y -- HTH :) Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X Hi. You didn't quite define your F( ) function, but if I understand it correctly... = (x*((y + 1)^(-A + B + 1) - y - 1))/y -- HTH :) Dana DeLouis "To understand recursion, one must first understand recursion." "axr0284" wrote in message ... Hi, I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
and excel would calculate what I owe on the end date.
7702.996743 01/16/08 Sounds like you no longer want SUM The loan at time 0 is 7695. Is this what you are looking for? Your 5th payment is: =FV(0.02%,5,0,-7695) $7,702.70 Maybe?? -- HTH Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X this does not compute what I need. Hi. The sum of the first 6 terms is: =SUM(A2:A7) - 46,193.98691 When I enter the equation: Sub Demo() Dim x, y, z x = 7695 y = 0.000207756229861 z = 6 ' (ie b-a) Debug.Print (x * ((y + 1) ^ z - 1)) / y End Sub It returns the same amount. 46193.98691 The value of y is probably not exact. =7695*(1+0.02%) does not exactly match your next value. In your table, if the next term was actually = 7695 * (1 + .02%) = 7696.5390 and copied down, then using y=.02/100 would give the same amount. 46,193.09116 -- HTH Dana DeLouis "axr0284" wrote in message ... Here you go. I would use it to calculate my loan interest every day. Daily interest percentage: 0.02 (Y) Starting loan amount 7695 (X) Starting day 01/11/08 (A) ending date 01/16/08 (B) In excel, if I do compute it manually it comes out like this Amount Date 7695 01/11/08 7696.598684 01/12/08 7698.197701 01/13/08 7699.797049 01/14/08 7701.39673 01/15/08 7702.996743 01/16/08 I would like to automate this process so that i don't need to calculate each date's amount to obtain the next one. I would put in the start and end data and excel would calculate what I owe on the end date. I hope it's clearer now. "Dana DeLouis" wrote: No, this does not compute what I need. Can you give a simple example, and the first few terms of the sequence. -- Dana DeLouis "axr0284" wrote in message ... No, this does not compute what I need. "Dana DeLouis" wrote: Oh wait. When you say for n=1 to (b-a), the first term is actually f(n-1) - f(0). See if this is better. If z is the number of terms (ie b-a) then perhaps: (x*((y + 1)^z - 1))/y -- HTH :) Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X Hi. You didn't quite define your F( ) function, but if I understand it correctly... = (x*((y + 1)^(-A + B + 1) - y - 1))/y -- HTH :) Dana DeLouis "To understand recursion, one must first understand recursion." "axr0284" wrote in message ... Hi, I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a custom formula
Sorry,
for some reason, when I ran your formula, the numbers did not come out right but now i realize i made the mistake. Yeah eventually I would like to calculate the accrued interest on the loan daily as well as the loan amount and a bunch of different stuff. I'll check out the FV function also. Did not know it existed. Thanks for all the help. I really appreciate it. Amish "Dana DeLouis" wrote: and excel would calculate what I owe on the end date. 7702.996743 01/16/08 Sounds like you no longer want SUM The loan at time 0 is 7695. Is this what you are looking for? Your 5th payment is: =FV(0.02%,5,0,-7695) $7,702.70 Maybe?? -- HTH Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X this does not compute what I need. Hi. The sum of the first 6 terms is: =SUM(A2:A7) - 46,193.98691 When I enter the equation: Sub Demo() Dim x, y, z x = 7695 y = 0.000207756229861 z = 6 ' (ie b-a) Debug.Print (x * ((y + 1) ^ z - 1)) / y End Sub It returns the same amount. 46193.98691 The value of y is probably not exact. =7695*(1+0.02%) does not exactly match your next value. In your table, if the next term was actually = 7695 * (1 + .02%) = 7696.5390 and copied down, then using y=.02/100 would give the same amount. 46,193.09116 -- HTH Dana DeLouis "axr0284" wrote in message ... Here you go. I would use it to calculate my loan interest every day. Daily interest percentage: 0.02 (Y) Starting loan amount 7695 (X) Starting day 01/11/08 (A) ending date 01/16/08 (B) In excel, if I do compute it manually it comes out like this Amount Date 7695 01/11/08 7696.598684 01/12/08 7698.197701 01/13/08 7699.797049 01/14/08 7701.39673 01/15/08 7702.996743 01/16/08 I would like to automate this process so that i don't need to calculate each date's amount to obtain the next one. I would put in the start and end data and excel would calculate what I owe on the end date. I hope it's clearer now. "Dana DeLouis" wrote: No, this does not compute what I need. Can you give a simple example, and the first few terms of the sequence. -- Dana DeLouis "axr0284" wrote in message ... No, this does not compute what I need. "Dana DeLouis" wrote: Oh wait. When you say for n=1 to (b-a), the first term is actually f(n-1) - f(0). See if this is better. If z is the number of terms (ie b-a) then perhaps: (x*((y + 1)^z - 1))/y -- HTH :) Dana DeLouis "Dana DeLouis" wrote in message ... Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X Hi. You didn't quite define your F( ) function, but if I understand it correctly... = (x*((y + 1)^(-A + B + 1) - y - 1))/y -- HTH :) Dana DeLouis "To understand recursion, one must first understand recursion." "axr0284" wrote in message ... Hi, I am a newbie at excel so I would like to know How to do the following. Cell A1 would contain 1 date A Cell A2 would contain a second date B Cell A3 would contain a number X Cell A4 would contain a number Y I would like cell A5 to contain a formula that will perform the following recursively: Sum[F(n-1)+{F(n-1)*Y}] for n = 1 to (B - A) and F(0) = X so if n = 1 to 20, it would iterate 20 times first using X and then the that result in the next iteration and so on until n=28. Then it would display the final result in cell A5. I have not been able to figure out how to do this. Thanks for any help, Amish |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating and adding custom formulas | Excel Worksheet Functions | |||
Creating a Custom Form | Excel Discussion (Misc queries) | |||
Creating custom charts | Charts and Charting in Excel | |||
Creating custom list with a comma in it | Excel Discussion (Misc queries) | |||
Creating an custom input box | Excel Worksheet Functions |