Dynamic Sum Array Formula Input Help

[excelCPA]

I am trying to create a dynamic formula to calculate the total
interest paid on a $100,000 loan over a period of K years. As I have
the formula written now, it calculates this over a period of 5 years
(A2:A6).

={SUM(ISPMT(rate,per,nper,pv))}


The sheet looks like this:

A B C
1 5% 30 100,000
2 1
3 2
4 3
5 4
6 5

={SUM(ISPMT(A1,A2:A6,B1,C1))}

The result correctly = -$22,500


As it stands now the 'per' variable in the array summation is 1, 2, 3,
4, and 5 as represented in A2:A6. Is there a way to make this formula
completely dynamic in the sense that "A2:A6" can be replaced with
something like "1:5" so that the formula would look something like
this:

={SUM(ISPMT(A1,[1:5],B1,C1))} where [1:5] will represent years 1, 2,
3, 4, and 5 in the array?

Or in cell D1 can I dynamically enter the number of years I want to
calculate so that the formula like:
={SUM(ISPMT(A1,[11],B1,C1))}, where if the number 5 is entered in
D1, the "[11]" in the array represents years 1, 2, 3, 4, and 5?.

Please advise.

 

[Bernie Deitrick]

No need to re-invent the wheel....

=CUMIPMT(5%/12,360,100000,1,K*12,0)

Change K to either a number or a cell reference....


HTH,
Bernie
MS Excel MVP

 

[excelCPA]

No need to re-invent the wheel....

=CUMIPMT(5%/12,360,100000,1,K*12,0)

Change K to either a number or a cell reference....

HTH,
Bernie
MS Excel MVP

I am trying to create a dynamic formula to calculate the total
interest paid on a $100,000 loan over a period of K years.  As I have
the formula written now, it calculates this over a period of 5 years
(A2:A6).

The sheet looks like this:

    A    B        C
1  5%  30  100,000
2   1
3   2
4   3
5   4
6   5

The result correctly = -$22,500

As it stands now the 'per' variable in the array summation is 1, 2, 3,
4, and 5 as represented in A2:A6.  Is there a way to make this formula
completely dynamic in the sense that "A2:A6" can be replaced with
something like "1:5" so that the formula would look something like
this:

={SUM(ISPMT(A1,[1:5],B1,C1))} where [1:5] will represent years 1, 2,
3, 4, and 5 in the array?

Or in cell D1 can I dynamically enter the number of years I want to
calculate so that the formula  like:
={SUM(ISPMT(A1,[11],B1,C1))}, where if the number 5 is entered in
D1, the "[11]" in the array represents  years 1, 2, 3, 4, and 5?.

Please advise.- Hide quoted text -

- Show quoted text -

Thanks a lot. I didn't know this formula existed.

For future reference, does anyone know how to enter dynamic ranges in
sum array formulas as in the earlier example?

 

[Bernie Deitrick]

- Show quoted text -

Thanks a lot. I didn't know this formula existed.

For future reference, does anyone know how to enter dynamic ranges in
sum array formulas as in the earlier example?



Generally, a variable array can be introduced into the formula by using a construct like

ROW(INDIRECT("A1:A" & CellWithNumber))

HTH,
Bernie
MS Excel MVP