recurrence

[roter789]

Here is my problem in Excel: This is a recurrence problem. I was doin
the Capital Growth which calculates the amount of the investment as i
is compounded by the addition of interest for each period.

here is the formula:
=IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_Period))),"")

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 correspondin
to it. i dont know what is wrong with my formula becuase it is sayin
FALSE..

Thanks for the help in advance

 

[Bernard Liengme]

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

 

[JoeU2004]

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_Period))),"")

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]

It is really too difficult to explain all of your problems, in par
because
you neglect to explain the exact layout of things with cell names. S
let
me suggest an approach by starting from scratch. I hope you can adap
this
to your layout.

-

Assume the titles are in A1:A7, and the values are in B1:B7. I
appears
that you might have named some or all of the value cells. That's
good
thing. I will assume names similar to the titles. But keep in min
that
any explicit cell references to these parameters should be "absolute"

namely $B$1, $B$2, etc.

-

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 row
(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 wil
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 -----

i will explain more in details with regards to the purpose of what i a
doing... in the parameter worksheet, i enter every values for th
Initial Capital, Investment Date, Maturity Date, Interest Rate
Interest Period, Term and Number of Periods.. and from that values, th
period and capital will appear on a different worksheet. the period wil
display on the amount i plug in in the parameter worksheet along wit
the capital growth as it increments.

so lets say i plug in 20 periods, it will display on the othe
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_Period))),"")

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.

 

[JoeU2004]

and for the capital growth...
=IF(ISNUMBER(Period(C3*(1+Interest_Rate*Interest_Period))),"")

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 -----

 

[JoeU2004]

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 -----

 

[Dana DeLouis]

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

 

[JoeU2004]

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 -----