Weighted Average

[cjpascoe1]

I need to find the weighted average loan amount of all loans between and including interest rates 4.75 and 5.25. I also include the volume percentage per note rate.

InterestRate	LoanAmount
4.875	150500
4.875	2540575
5	187500
5.375	15200
4.875	14848784
4.375	282292
5.125	4095500
4.50	25000045
4.50	2836007
4.875	373500
5.625	1163035
4.75	13945900
5.625	18915072

 

[CA_Punit]

I was confused as what weight needs to be multiplied with Loan Amount

I have calculated the Loan Amount falling under the criteria and divided it with no of intererst rates falling under the criteria

Book1
	O	P	Q	R	S
1	InterestRate	LoanAmount
2	4.875	150500			2780168
3	4.875	2540575
4	5	187500
5	5.375	15200
6	4.875	14848784
7	4.375	282292
8	5.125	4095500
9	4.5	25000045
10	4.5	2836007
11	4.875	373500
12	5.625	1163035
13	4.75	13945900
14	5.625	18915072
Sheet5

Cell Formulas
Range	Formula
S2	S2	=SUM(IF((O2:O14>=4.75)*(O2:O14<=5.25),P2:P14))/COUNT(O2:O14)-SUM(--((IF((O2:O14>=4.75)*(O2:O14<=5.25),P2:P14))=FALSE))

 

[cjpascoe1]

How do I get the volume percentage per note rate ?

 

[CA_Punit]

can you explain with an example manually. I am not able to get you

 

[AhoyNC]

Is this what you want?
Note: the formula in D20 is done without the helper columns D&E.

Book1
	A	B	C	D	E
1	InterestRate	LoanAmount		Loan Wt. Factor	Loan
2	4.875	150500		7336.875	150500
3	4.875	2540575		123853.0313	2540575
4	5	187500		9375	187500
5	5.375	15200		0	0
6	4.875	14848784		723878.22	14848784
7	4.375	282292		0	0
8	5.125	4095500		209894.375	4095500
9	4.5	25000045		0	0
10	4.5	2836007		0	0
11	4.875	373500		18208.125	373500
12	5.625	1163035		0	0
13	4.75	13945900		662430.25	13945900
14	5.625	18915072		0	0
15			Total	1754975.876	36142259
16
17				Wt. Average Interest
18			Wt. Average	4.86%
19
20				4.86%	All in one formula
Sheet2

Cell Formulas
Range	Formula
D2:D14	D2	=IF(AND(A2>=4.75,A2<=5.25),(A2/100)*B2,0)
E2:E14	E2	=IF(D2=0,0,B2)
D15:E15	D15	=SUM(D2:D14)
D18	D18	=ROUND(D15/E15,4)
D20	D20	=ROUND(SUMPRODUCT(($A$2:$A$14>=4.75)*($A$2:$A$14<=5.25)*(($A$2:$A$14/100)*($B$2:$B$14)))/SUMIFS(B2:B14,A2:A14,">="&4.75,$A$2:$A$14,"<="&5.25),4)