Sumproduct - Weighted average interest rate (Multiple coloumn)

[a_dani20]

Hii guys...


i have a file containing outstanding balance,interest rates and maturity for deposits received. Deposits are classified on the basis of there maturity period viz 0-30, 31-90, and 91-120 days. Further deposits are also categorized on the basis of there group viz retail and corporate. I need a formula to calculate weighted average interest rate for each combination. ex weighted average rate for retail deposits falling under 0-30 days.

Catch is i need a single formula which i can use for all other combinations also...(something similar to formula i have used to calculate outstanding balance for each combinations)

Sample File is attached at below link..


https://www.dropbox.com/s/a8o6mh6wrhfzay8/IncNew%20-%20Copy.xls

 

[Sajan]

What is the average "weighted" on?

 

[a_dani20]

Hii sajan...


Average is weighted on outstanding balance..

 

[Sajan]

Hi,

Could you try the following?


Sample data setup

[pre]

OS Bal	Int Rate	Maturity	Deposit Type
10000	10%	        30	        Retail
20000	20%	        45	        Corporate
50000	30%	        125	        Retail
60000	25%	        25	        Retail
45000	40%	        160	        Corporate
27000	50%	        37	        Retail
14000	60%	        49	        Corporate
13450	70%	        101	        Retail
For ease of reference, I have used the following Named ranges, to refer to the columns/data above:

OSBal

IntRate

Maturity

DepType


To calculate the weighted average interest rate (setup in the range K1:P3)

Type	        0	31	91	121	181
Retail	        22.86%	50.00%	70.00%	30.00%	0.00%
Corporate	0.00%	36.47%	0.00%	40.00%	0.00%

[/pre]
In the above results, 0 represents "0-30", 31 represents "31-90", etc.


The values are calculated using the following formula (shown for cell L2):

=IFERROR(SUMPRODUCT((DepType=$K2)*(LOOKUP(Maturity,$L$1:$P$1)=L$1)*OSBal*IntRate)/SUMPRODUCT(OSBal*(DepType=$K2)*(LOOKUP(Maturity,$L$1:$P$1)=L$1)), 0)


Copy to the other cells.


Cheers,

Sajan.