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,[1:D1],B1,C1))}, where if the number 5 is entered in
D1, the "[1:D1]" in the array represents years 1, 2, 3, 4, and 5?.

Please advise.

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" wrote in message
...
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,[1:D1],B1,C1))}, where if the number 5 is entered in
D1, the "[1:D1]" in the array represents years 1, 2, 3, 4, and 5?.

Please advise.

On Sep 25, 9:40*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...



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,[1:D1],B1,C1))}, where if the number 5 is entered in
D1, the "[1:D1]" 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?

- 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