Weighted average to calculate monthly payments

[Brusky]

Hi, I am pricing 2 extended warranty agreements. The warranties have a upfront price but the customer wants to pay for each warranty as monthly payment over 3 years/36 months.

Extended Warranty A; qty 700; $2,000.00
Extended Warranty B; qty 30; $2,100.00

Because the warranties have different quantities I believe i should use a weighed average to calculate the monthly payments.

Can someone suggest excel formula to assign a weight for each warranty, and then use a weighted average to calculate the monthly payment amounts for Warranty A and Warranty B?

 

[joeu2004]

Well, the weighted average cost is 2000*700/730 + 2100*30/730. I'm not sure how that is related to your requirements.

In general, the weighted average of values v1, v2,..., vN with corresponding units of w1, w2,..., wN is SUMPRODUCT(v1:vN, w1:wN) / SUM(w1:wN).

 

[Brusky]

thank you. I guess my question is if the monthly payments are:
qty 700: $2000/36 = $55.55 per month
qty 30: $2100/36 = $58.33 per month

Does it make sense to assign a higher weight to the item that is $55.55 per month?

And the correct way to calculate this is to use the SUMPRODUCT function? If yes what should be the proper weights to assign to each item?

 

[joeu2004]

No. Even with the cost/month for each warranty, the total cost/month is simply the sum.

The monthly payment is simply $2000/36 + $2100/36 = (2000+2100)/36 = $113.89/month.

You would use the weighted average if you have the cost per unit for each warranty.

For example (columns A:C):

   A           B           C
1  Warranty A: 700 units @ $2000/700 per unit (to avoid rounding error, which would complicate things)
2  Warranty B:  30 units @ $2100/30  per unit

The weighted average cost per unit is =SUMPRODUCT(B1:B2,C1:C2)/SUM(C1:C2), which is $5.61917808219178, calculated in D1.

So the total cost for the 730 units is =D1*SUM(C1:C2), which is $4100, calculated in E1. And the monthly cost is =E1/36, which $113.89.

PS.... Of course, for this example, it is more straight-forward to calculate the total cost with =SUMPRODUCT(B1:B2,C1:C2) in D1, and the cost/month with =D1/36.

The weighted average might be useful for determining the cost of a warranty for 500 units, for example. OTOH, obviously the warranty is based on a sliding scale relative to the number of units. So even then, the weighted average might not be useful.

 

[Brusky]

ah, my apologies, i should have stated more clearly. The cost shown below are the unit costs $2000 per unit, and $2100 per unit respectively.

qty 700: unit cost of $2000/36 = $55.55 per month per unit * 700 units
qty 30: unit cost of $2100/36 = $58.33 per month per unit * 30 units

 

[joeu2004]

If 700 and 30 are in B1:B2, and $2000 and $2100 are in C1:C2, the total monthly cost is =SUMPRODUCT(B1:B2,C1:C2)/36, which is $40,638.89.

Mathematically, that is the same as $55.56*700 + $58.33*30. (Some difference might arise due to rounding.)

That is not a weighted "average". That is the total cost/month. And each product (55.56*700 and 58.33*30) is the total cost/month for the corresponding warranty.

-----

The weighted average cost/month per unit is =SUMPRODUCT(B1:B2,C1:C2) / SUM(B1:B2) / 36, which is $55.67.

(Note the correction to my previous posting, where is miswrote SUM(C1:C2).)

That is in contrast to the simple average per unit: = AVERAGE(C1/36, C2/36), which is $56.94.

But again, I do not see the relevance to your problem.

IMHO, you do not want the customer to pay 55.67*700 for warranty A and 55.67*30 for warranty B. The total is the same. So what is the point of misrepresenting the cost per warranty?

Unless you are trying present the two warranties as one for 730 units, with a (weighted) average cost/unit of =SUMPRODUCT(B1:B2,C1:C2) / SUM(B1:B2), which is $2004.11.

 

[Brusky]

this makes sense, thanks!

 

[Brusky]

thank you, yes i would like to present one monthly cost/month for both units.

 

[joeu2004]

I hope I didn't cause some confusion with my last comment. Let me clarify....

If the customer is aware that he is paying for warranty A for 700 units at $2000 each and for warranty B for 30 units at $2100, then the total monthly cost for both warranties is simpy ($2000*700 + $2100*30)/36, which is $40,638.89.

It is not an average, much less a weighted average.

On the other hand, if you choose to present a single warranty for 730 units, then the unit cost is the weighted average ($2000*700 + $2100*30)/730, which is $2004.11. The total monthly cost is still $40,638.89; or you might choose to say $2004.11*730/36, which is $40,638.90 due to rounding.

I provided example formulas for all of those calculations in my previous postings, based on my worksheet layout.

 

[Brusky]

yes i like the idea to present this as a single warranty and a single monthly cost. So then the unit monthly cost will be around 55.67 per warranty for the combined 730. This will be simpler to show to the client than if we were to quote two separate monthly prices for $55 and $58.

thank you so much for your advise about the formulas.