sumproduct while number of added fields is changing?
 

I need to calculate how big amount of interest income is amortizing. I have
sales of the each period, amort rate (how much amortizes in 1-st ,2-nd and so
on period after sales, and the Yield applied to each period sales.
The problem is, that I could not figure out, how to build the sumproduct
function so, that on each next period it would take in account, that there is
new volume that started to amortize and the others have one more amortization
rate to be taken in account.

B C D E F
3 New Sales 195 192 146 394 97 596 1 076 343
4 New Sales Yield 7,25% 7,22% 7,19% 7,26%
5 Amortization 1,12% 1,13% 1,13% 1,14%
6 Amortized interest Income 159 437 795 2 031

In simple formulas:
C6=$B3*$B4*SUM($B5:B5)
D6=$B3*$B4*SUM($B5:C5)+$C3*$C4*SUM($B5:B5)
E6=$B3*$B4*SUM($B5:D5)+$C3*$C4*SUM($B5:C5)+$D3*$D4 *SUM($B5:B5)F6=$B3*$B4*SUM($B5:E5)+$C3*$C4*SUM($B5 :D5)+$D3*$D4*SUM($B5:C5)+$E3*$E4*SUM($B5:B5)
and so on

Can somebody help please?

sumproduct while number of added fields is changing?
 

I tried to avoid using auxiliary cells but either it is impossible or
I am missing something. The aux values you need are the numbers 1, 2,
3 growing in parallel to B3, C3, D3, .... I assume you have them in
row 2, starting from B2.
You can enter the following formula in C6 and copy to the right:

=SUMPRODUCT($B3:B3*$B4:B4*(SUBTOTAL(9,OFFSET($B5,0 ,0,1,C2-$B$2:B2))))

Maybe one of the posters will jump in and suggest a solution using
COLUMN or ROW to generate the needed arrays w/o using the aux cells.

HTH
Kostis Vezerides

On Nov 26, 5:33*pm, Massimus
wrote:
I need to calculate how big amount of interest income is amortizing. I have
sales of the each period, amort rate (how much amortizes in 1-st ,2-nd and so
on period after sales, and the Yield applied to each period sales.
The problem is, that I could not figure out, how to build the sumproduct
function so, that on each next period it would take in account, that there is
new volume that started to amortize and the others have one more amortization
rate to be taken in account.

* * * * B * * * C * * * D * * * E * * * F
3 * * * New Sales * * * 195 192 146 394 97 596 *1 076 343
4 * * * New Sales Yield 7,25% * 7,22% * 7,19% * 7,26%
5 * * * Amortization * *1,12% * 1,13% * 1,13% * 1,14%
6 * * * Amortized interest Income * * * 159 * * 437 * * 795 * * 2 031

In simple formulas:
C6=$B3*$B4*SUM($B5:B5) *
D6=$B3*$B4*SUM($B5:C5)+$C3*$C4*SUM($B5:B5) * * *
E6=$B3*$B4*SUM($B5:D5)+$C3*$C4*SUM($B5:C5)+$D3*$D4 *SUM($B5:B5)F6=$B3*$B4*SUM($B5:E5)+$C3*$C4*SUM($B5 :D5)+$D3*$D4*SUM($B5:C5)+$E3*$E4*SUM($B5:B5)
and so on

Can somebody help please?

sumproduct while number of added fields is changing?
 

Huge thanks!

It works perfectly, I hope I can replace the auxiliary stuff with column
function combination, and even if I cannot, it is still great.

"vezerid" wrote:

I tried to avoid using auxiliary cells but either it is impossible or
I am missing something. The aux values you need are the numbers 1, 2,
3 growing in parallel to B3, C3, D3, .... I assume you have them in
row 2, starting from B2.
You can enter the following formula in C6 and copy to the right:

=SUMPRODUCT($B3:B3*$B4:B4*(SUBTOTAL(9,OFFSET($B5,0 ,0,1,C2-$B$2:B2))))

Maybe one of the posters will jump in and suggest a solution using
COLUMN or ROW to generate the needed arrays w/o using the aux cells.

HTH
Kostis Vezerides

On Nov 26, 5:33 pm, Massimus
wrote:
I need to calculate how big amount of interest income is amortizing. I have
sales of the each period, amort rate (how much amortizes in 1-st ,2-nd and so
on period after sales, and the Yield applied to each period sales.
The problem is, that I could not figure out, how to build the sumproduct
function so, that on each next period it would take in account, that there is
new volume that started to amortize and the others have one more amortization
rate to be taken in account.

B C D E F
3 New Sales 195 192 146 394 97 596 1 076 343
4 New Sales Yield 7,25% 7,22% 7,19% 7,26%
5 Amortization 1,12% 1,13% 1,13% 1,14%
6 Amortized interest Income 159 437 795 2 031

In simple formulas:
C6=$B3*$B4*SUM($B5:B5)
D6=$B3*$B4*SUM($B5:C5)+$C3*$C4*SUM($B5:B5)
E6=$B3*$B4*SUM($B5:D5)+$C3*$C4*SUM($B5:C5)+$D3*$D4 *SUM($B5:B5)F6=$B3*$B4*SUM($B5:E5)+$C3*$C4*SUM($B5 :D5)+$D3*$D4*SUM($B5:C5)+$E3*$E4*SUM($B5:B5)
and so on

Can somebody help please?