|
recurrence
Here is my problem in Excel: This is a recurrence problem. I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period.
here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") here is the outcome: Period Capital 0 $30,000.00 1 FALSE 2 3 FALSE 4 FALSE 5 FALSE 6 FALSE 7 FALSE 8 FALSE 9 FALSE 10 FALSE 11 FALSE 12 FALSE 13 FALSE 14 FALSE 15 FALSE 16 FALSE 17 FALSE 18 FALSE 19 FALSE 20 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE This is the parameter: Initial Capital:$30,000 Investment Date: 1/31/1993 Maturity Date:1/31/2003 Interest Rate:8.00% Interest Period:0.5 Term:10.00 Number of Periods:20 I want the capital growth be visible ONLY to the period corresponding to it. i dont know what is wrong with my formula becuase it is saying FALSE.. Thanks for the help in advance. |
recurrence
Suppose your have this starting in A1
Period Capital 0 $30,000.00 1 <formula 2 <formula 3 <formula So the value 1 is in A2 and the first formula is in B2 The formula should read =IF(ISNUMBER(A2), Period(C3*(1+Interest_Rate*Interest_Period))),"") or =IF( A2 0, Period(C3*(1+Interest_Rate*Interest_Period))),"") But I have no idea what Period(C3*(1+Interest_Rate*Interest_Period))) is all about You should look at the FV function, I think Or return here with a more detailed explanations best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "roter789" wrote in message ... Here is my problem in Excel: This is a recurrence problem. I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") here is the outcome: Period Capital 0 $30,000.00 1 FALSE 2 3 FALSE 4 FALSE 5 FALSE 6 FALSE 7 FALSE 8 FALSE 9 FALSE 10 FALSE 11 FALSE 12 FALSE 13 FALSE 14 FALSE 15 FALSE 16 FALSE 17 FALSE 18 FALSE 19 FALSE 20 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE This is the parameter: Initial Capital:$30,000 Investment Date: 1/31/1993 Maturity Date:1/31/2003 Interest Rate:8.00% Interest Period:0.5 Term:10.00 Number of Periods:20 I want the capital growth be visible ONLY to the period corresponding to it. i dont know what is wrong with my formula becuase it is saying FALSE.. Thanks for the help in advance. -- roter789 |
recurrence
"roter789" wrote:
I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") It is really too difficult to explain all of your problems, in part because you neglect to explain the exact layout of things with cell names. So let me suggest an approach by starting from scratch. I hope you can adapt this to your layout. This is the parameter: Initial Capital: $30,000 Investment Date: 1/31/1993 Maturity Date: 1/31/2003 Interest Rate: 8.00% Interest Period: 0.5 Term: 10.00 Number of Periods: 20 Assume the titles are in A1:A7, and the values are in B1:B7. It appears that you might have named some or all of the value cells. That's a good thing. I will assume names similar to the titles. But keep in mind that any explicit cell references to these parameters should be "absolute", namely $B$1, $B$2, etc. here is the outcome: Period Capital 0 $30,000.00 1 FALSE Assume that 0 is in A10, and B10 is $30,000; that is: B10: =Initial_Capital Then put the following into A11:B11 and copy down for at least 20 rows (many more, if you wish): A11: =if(A10 < Number_of_Periods, A10+1, "") B11: =if(A11 = "", "", B10 * (1 + Interest_Rate * Interest_Period)) As you copy down, the relative cell references A10, A11 and B10 will change correctly automagically. Programming note: Instead of repeatedly computing the "constant" Interest_Rate*Interest_Period, I would put the following into C4 and reference $C$4 (perhaps by an assigned name): =Interest_Rate * Interest_Period Does that address your problem? Or did I miss the boat altogether? ----- original message ----- "roter789" wrote in message ... Here is my problem in Excel: This is a recurrence problem. I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") here is the outcome: Period Capital 0 $30,000.00 1 FALSE 2 3 FALSE 4 FALSE 5 FALSE 6 FALSE 7 FALSE 8 FALSE 9 FALSE 10 FALSE 11 FALSE 12 FALSE 13 FALSE 14 FALSE 15 FALSE 16 FALSE 17 FALSE 18 FALSE 19 FALSE 20 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE This is the parameter: Initial Capital:$30,000 Investment Date: 1/31/1993 Maturity Date:1/31/2003 Interest Rate:8.00% Interest Period:0.5 Term:10.00 Number of Periods:20 I want the capital growth be visible ONLY to the period corresponding to it. i dont know what is wrong with my formula becuase it is saying FALSE.. Thanks for the help in advance. -- roter789 |
Quote:
so lets say i plug in 20 periods, it will display on the other worksheet just only 20 periods. and here is the formula for that... =IF(B3<Number_of_Periods,B3+1,"") and for the capital growth... =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") i think there is a few mistakes in the parenthesis and i cannot figure out which one is incorrect. this formula: C3*(1+Interest_Rate*Interest_Period)) will display the corresponding amount for that particular period. Here is the problem: i want the amount displayed ONLY up to the period it corresponds... if there is no period displayed, it should not display anything on the capital growth column. |
Quote:
Thanks to both of you guys... i truly appreciate the help |
recurrence
"roter789" wrote:
and for the capital growth... =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") i think there is a few mistakes in the parenthesis and i cannot figure out which one is incorrect. And from my perspective, it is too far off the mark to explain. But for the record, what version of Excel are you talking about? Excel 2007 does have some new syntax that I am not familiar with. Here is the problem: i want the amount displayed ONLY up to the period it corresponds... if there is no period displayed, it should not display anything on the capital growth column. What is wrong with the solution that I provided? I think it does exactly what you ask for -- except that I might have chosen different cells. Perhaps B3 has 0, C3 has $30,000, and: B4: =if(B3 < Number_of_Periods, B3+1, "") C4: =if(B4 = "", "", C3 * (1 + Interest_Rate * Interest_Period)) ----- original message ----- "roter789" wrote in message ... JoeU2004;864097 Wrote: "roter789" wrote:- I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"")- It is really too difficult to explain all of your problems, in part because you neglect to explain the exact layout of things with cell names. So let me suggest an approach by starting from scratch. I hope you can adapt this to your layout. - This is the parameter: Initial Capital: $30,000 Investment Date: 1/31/1993 Maturity Date: 1/31/2003 Interest Rate: 8.00% Interest Period: 0.5 Term: 10.00 Number of Periods: 20- Assume the titles are in A1:A7, and the values are in B1:B7. It appears that you might have named some or all of the value cells. That's a good thing. I will assume names similar to the titles. But keep in mind that any explicit cell references to these parameters should be "absolute", namely $B$1, $B$2, etc. - here is the outcome: Period Capital 0 $30,000.00 1 FALSE- Assume that 0 is in A10, and B10 is $30,000; that is: B10: =Initial_Capital Then put the following into A11:B11 and copy down for at least 20 rows (many more, if you wish): A11: =if(A10 < Number_of_Periods, A10+1, "") B11: =if(A11 = "", "", B10 * (1 + Interest_Rate * Interest_Period)) As you copy down, the relative cell references A10, A11 and B10 will change correctly automagically. Programming note: Instead of repeatedly computing the "constant" Interest_Rate*Interest_Period, I would put the following into C4 and reference $C$4 (perhaps by an assigned name): =Interest_Rate * Interest_Period Does that address your problem? Or did I miss the boat altogether? ----- original message ----- "roter789" wrote in message ...- Here is my problem in Excel: This is a recurrence problem. I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") here is the outcome: Period Capital 0 $30,000.00 1 FALSE 2 3 FALSE 4 FALSE 5 FALSE 6 FALSE 7 FALSE 8 FALSE 9 FALSE 10 FALSE 11 FALSE 12 FALSE 13 FALSE 14 FALSE 15 FALSE 16 FALSE 17 FALSE 18 FALSE 19 FALSE 20 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE This is the parameter: Initial Capital:$30,000 Investment Date: 1/31/1993 Maturity Date:1/31/2003 Interest Rate:8.00% Interest Period:0.5 Term:10.00 Number of Periods:20 I want the capital growth be visible ONLY to the period corresponding to it. i dont know what is wrong with my formula becuase it is saying FALSE.. Thanks for the help in advance. -- roter789 - i will explain more in details with regards to the purpose of what i am doing... in the parameter worksheet, i enter every values for the Initial Capital, Investment Date, Maturity Date, Interest Rate, Interest Period, Term and Number of Periods.. and from that values, the period and capital will appear on a different worksheet. the period will display on the amount i plug in in the parameter worksheet along with the capital growth as it increments. so lets say i plug in 20 periods, it will display on the other worksheet just only 20 periods. and here is the formula for that... =IF(B3<Number_of_Periods,B3+1,"") and for the capital growth... =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") i think there is a few mistakes in the parenthesis and i cannot figure out which one is incorrect. this formula: C3*(1+Interest_Rate*Interest_Period)) will display the corresponding amount for that particular period. Here is the problem: i want the amount displayed ONLY up to the period it corresponds... if there is no period displayed, it should not display anything on the capital growth column. -- roter789 |
recurrence
"JoeU2004" wrote:
What is wrong with the solution that I provided? Not trying to be provocative. Just struggling to understand if and how I am not addressing your needs. ----- original message ----- "JoeU2004" wrote in message ... "roter789" wrote: and for the capital growth... =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") i think there is a few mistakes in the parenthesis and i cannot figure out which one is incorrect. And from my perspective, it is too far off the mark to explain. But for the record, what version of Excel are you talking about? Excel 2007 does have some new syntax that I am not familiar with. Here is the problem: i want the amount displayed ONLY up to the period it corresponds... if there is no period displayed, it should not display anything on the capital growth column. What is wrong with the solution that I provided? I think it does exactly what you ask for -- except that I might have chosen different cells. Perhaps B3 has 0, C3 has $30,000, and: B4: =if(B3 < Number_of_Periods, B3+1, "") C4: =if(B4 = "", "", C3 * (1 + Interest_Rate * Interest_Period)) ----- original message ----- "roter789" wrote in message ... JoeU2004;864097 Wrote: "roter789" wrote:- I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"")- It is really too difficult to explain all of your problems, in part because you neglect to explain the exact layout of things with cell names. So let me suggest an approach by starting from scratch. I hope you can adapt this to your layout. - This is the parameter: Initial Capital: $30,000 Investment Date: 1/31/1993 Maturity Date: 1/31/2003 Interest Rate: 8.00% Interest Period: 0.5 Term: 10.00 Number of Periods: 20- Assume the titles are in A1:A7, and the values are in B1:B7. It appears that you might have named some or all of the value cells. That's a good thing. I will assume names similar to the titles. But keep in mind that any explicit cell references to these parameters should be "absolute", namely $B$1, $B$2, etc. - here is the outcome: Period Capital 0 $30,000.00 1 FALSE- Assume that 0 is in A10, and B10 is $30,000; that is: B10: =Initial_Capital Then put the following into A11:B11 and copy down for at least 20 rows (many more, if you wish): A11: =if(A10 < Number_of_Periods, A10+1, "") B11: =if(A11 = "", "", B10 * (1 + Interest_Rate * Interest_Period)) As you copy down, the relative cell references A10, A11 and B10 will change correctly automagically. Programming note: Instead of repeatedly computing the "constant" Interest_Rate*Interest_Period, I would put the following into C4 and reference $C$4 (perhaps by an assigned name): =Interest_Rate * Interest_Period Does that address your problem? Or did I miss the boat altogether? ----- original message ----- "roter789" wrote in message ...- Here is my problem in Excel: This is a recurrence problem. I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") here is the outcome: Period Capital 0 $30,000.00 1 FALSE 2 3 FALSE 4 FALSE 5 FALSE 6 FALSE 7 FALSE 8 FALSE 9 FALSE 10 FALSE 11 FALSE 12 FALSE 13 FALSE 14 FALSE 15 FALSE 16 FALSE 17 FALSE 18 FALSE 19 FALSE 20 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE This is the parameter: Initial Capital:$30,000 Investment Date: 1/31/1993 Maturity Date:1/31/2003 Interest Rate:8.00% Interest Period:0.5 Term:10.00 Number of Periods:20 I want the capital growth be visible ONLY to the period corresponding to it. i dont know what is wrong with my formula becuase it is saying FALSE.. Thanks for the help in advance. -- roter789 - i will explain more in details with regards to the purpose of what i am doing... in the parameter worksheet, i enter every values for the Initial Capital, Investment Date, Maturity Date, Interest Rate, Interest Period, Term and Number of Periods.. and from that values, the period and capital will appear on a different worksheet. the period will display on the amount i plug in in the parameter worksheet along with the capital growth as it increments. so lets say i plug in 20 periods, it will display on the other worksheet just only 20 periods. and here is the formula for that... =IF(B3<Number_of_Periods,B3+1,"") and for the capital growth... =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") i think there is a few mistakes in the parenthesis and i cannot figure out which one is incorrect. this formula: C3*(1+Interest_Rate*Interest_Period)) will display the corresponding amount for that particular period. Here is the problem: i want the amount displayed ONLY up to the period it corresponds... if there is no period displayed, it should not display anything on the capital growth column. -- roter789 |
recurrence
i dont know what is wrong with my formula becuase it is saying
FALSE.. I don't understand the question, but in your formula, you have basically: =IF(ISNUMBER(..),"") If it's a number, do nothing. The "False" part is because you left out the 3rd argument of the IF function. =IF(ISNUMBER(..),"","False Part Here") Also... Period(C3*(1+Interest....) Did you leave out the multiplication symbol? Period * (C3*(1+Interest....)) = = = = = = = = = HTH Dana DeLouis roter789 wrote: Here is my problem in Excel: This is a recurrence problem. I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") here is the outcome: Period Capital 0 $30,000.00 1 FALSE 2 3 FALSE 4 FALSE 5 FALSE 6 FALSE 7 FALSE 8 FALSE 9 FALSE 10 FALSE 11 FALSE 12 FALSE 13 FALSE 14 FALSE 15 FALSE 16 FALSE 17 FALSE 18 FALSE 19 FALSE 20 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE This is the parameter: Initial Capital:$30,000 Investment Date: 1/31/1993 Maturity Date:1/31/2003 Interest Rate:8.00% Interest Period:0.5 Term:10.00 Number of Periods:20 I want the capital growth be visible ONLY to the period corresponding to it. i dont know what is wrong with my formula becuase it is saying FALSE.. Thanks for the help in advance. |
recurrence
"Dana DeLouis" wrote:
If it's a number, do nothing. The "False" part is because you left out the 3rd argument of the IF function. =IF(ISNUMBER(..),"","False Part Here") Shouldn't that be just the opposite, namely: =IF(ISNUMBER(formula), formula, "") Did you leave out the multiplication symbol? Period * (C3*(1+Interest....)) But that would not achieve the OP's goal as I interpret it, to wit: "__compounded__ by the addition of interest for each period". Perhaps it should be: $C$3 * (1 + Interest_Rate*Interest_Period)^Period ass-u-me-ing that C3 is the Initial Capital ($30,000). Note that relative C3 is changed to absolute $C$3 so that the formula can be copied down. That also assumes that Period is a named range. But I don't think C3 is the Initial Capital. In a later posting, the OP wrote that the following computes the period number (presumably in B4): =IF(B3<Number_of_Periods,B3+1,"") If B3 is the period number, C3 is probably the per-period balance in the table that the OP presents with FALSE values in column C (presumably). In that case, "^Period" (or "Period*") is not needed (and it would be wrong), and relative C3 was right the first time (assuming the ISNUMBER expression is in C4). ----- original message ----- "Dana DeLouis" wrote in message ... i dont know what is wrong with my formula becuase it is saying FALSE.. I don't understand the question, but in your formula, you have basically: =IF(ISNUMBER(..),"") If it's a number, do nothing. The "False" part is because you left out the 3rd argument of the IF function. =IF(ISNUMBER(..),"","False Part Here") Also... Period(C3*(1+Interest....) Did you leave out the multiplication symbol? Period * (C3*(1+Interest....)) = = = = = = = = = HTH Dana DeLouis roter789 wrote: Here is my problem in Excel: This is a recurrence problem. I was doing the Capital Growth which calculates the amount of the investment as it is compounded by the addition of interest for each period. here is the formula: =IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_P eriod))),"") here is the outcome: Period Capital 0 $30,000.00 1 FALSE 2 3 FALSE 4 FALSE 5 FALSE 6 FALSE 7 FALSE 8 FALSE 9 FALSE 10 FALSE 11 FALSE 12 FALSE 13 FALSE 14 FALSE 15 FALSE 16 FALSE 17 FALSE 18 FALSE 19 FALSE 20 FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE This is the parameter: Initial Capital:$30,000 Investment Date: 1/31/1993 Maturity Date:1/31/2003 Interest Rate:8.00% Interest Period:0.5 Term:10.00 Number of Periods:20 I want the capital growth be visible ONLY to the period corresponding to it. i dont know what is wrong with my formula becuase it is saying FALSE.. Thanks for the help in advance. |
| All times are GMT +1. The time now is 04:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com