Extend Rows and related formula downwards using functions not macros
 

I have a simple worksheet to amortise a loan. I would like to use a
formula to extend the rows downwards to the number of payments (Number
of years x Number of payments per year) using a formula.

For example, if there were 26 payments per year over 1 year, there
should be 26 rows. Now if I change the number of years to 2 years, two
things should happen:

1. The number of rows should expand to 52 from 26
2. The sum of the interest paid should include these extra rows.i.e
=Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to
rownumber 26).

Currently I am using a simple formula along the lines of
=IF(A47=Term,"",A47+1)

This just provides the numer of payments and then I copy the formulas
down.

Thanks in advance, NR