Dynamic Sum Array Formula Input Help

E

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,[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.
 
B

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
 
E

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,[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?
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top