Repeating A cell a predefined amount of times and allowing formulas to compensate

Hi all,

I would really appreciate some help and i couldnt find any posts that might help me. I thank anyone for their help in advance.

Here is my issue

I must create a form that calculates monthly payments and displays the term (month 1, 2, 3, 4, 5... etc)in column a, the debt for that month in column b, the payment for that month in column c, the percentage of the payment that is contributed towards the debt in d, the interest in that payment in e, and remaining balance on debt in f

eg
A______B______C___________D_______________________E________F
Month__Debt____Payment___Percentage towards Debt__Interest___Balance
1______1000____237.40_____177.40_________________60.00______822.60
2______822.60___95.28______145.93_________________49.36______676.68
etc

Formulas are as follows
B2: =I3
C2: =(INDIRECT("RC[-1]",0))*I1*(1+I1)^I2/((1+I1)^I2-1)
D2: =(INDIRECT("RC[-1]",0))-(INDIRECT("RC[1]",0))
E2: =(INDIRECT("RC[-3]",0))*I1
F2: =(INDIRECT("RC[-4]",0))-(INDIRECT("RC[-2]",0))

B3: =OFFSET(B3,-1,4)
C3,D3,E3,F3 are the exact same formulas as in row 2

i have the formulas down pat for the table itself, it references the column it needs. initial Debt (B1) is taken from another cell (I3) interest is taken from ( I1) and term (how many payments) is taken from (I2). Im worried about the formula in B3 because it may not work for what i need.

My question is about the term (the amount of payments). If i input x into cell i2, i would like the table to continue x amount of times and do the calculations sequentially as above. so say its a 5 term (5 month payment plan) i need the table to display the sequential calculations until column F on the fifth month is zero. i Just dont know how to make it repeat itself x amount of times.

i can upload/email the .xls doc if needed and am happy to explain or give the formulas i use.

Sorry about the long post. Thank you for your help in advance.

Thanks..Bob

An uploaded sample worksheet is always a good idea.

I'm sure others will have cleaner suggesstions, but this should work..

A2 =IF((ROW()-1)<=I$2,ROW()-1,"")

Copy that down as far as you need to go.

Then all other formulas can be tied to Column A with

=IF(Ax="","",INSERT Your FORMULA)

For instance, B3 would look like this.

=IF(A3="","",OFFSET(B3,-1,4))

Please upload it here within the forum..


Use the paperclip icon in the reply box to browse and upload the file.

As per Papaexcel's suggestion:

In A2: =IF(ROW()-1>$I$2,"",ROW()-1)

copied down as far as you want.. this will leave blanks after the count exceeds your input number of periods...