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