weighted average interest rate

[hegemony]

I thought it would be fun to determine the weighted average interest rate of my debt as a way to see the overall cost of my indebtiness. Weigthing APR by the percentage of total debt.

 

If you want to see yours here is how:

 

five columns in excel

first row titles of columns: name of debt, amount of debt, interest rate, percentage of debt, weighted average

 

in column B total the debt. Column E is the individual debt divided by the total debt to give you the percentage that debt is of all your debt. So in my example cell D2 is B2/B7. Then multiple D2 by the rate (cell E2 is C2*D2).

 

Sum the values in column D. In my example cell C7 is "=SUM(E2:E4)"

 

So my effective rate of my debt is 5.106517269%

 

 

What this allows me to do is to see the impact on my effective interest rate if I, for example, move 20K of our mortgage to a zero percent credit card (and of course pay that card off before the 0% expires).

 

Moving 20K to 0 percent reduces the effective interest rate to 4.72.

 

Edited December 21, 2004 by hegemony

[MilesFred]

I love to see people who are as obsessed with Excel as I am. I use it for everything.

 

I'll have to try this out when I get home and have access to all my records.

[hegemony]

bump for the evening crew...

[Kona]

Great idea. I use a similar method myself. I like to play "what if..." before I actually do it.

[hegemony]

bump for those bored obsessive-compulsive types

[tootie357]

i'm borderline obsessive-compulsive but that's just too much math for me.

[Kona]

bump for those bored obsessive-compulsive types

<{POST_SNAPBACK}>

 

Ideal for those who have no plans for New Year's Eve. A glass of wine or two or more makes designing the Exel program endurable. This is especially appropriate as you make your New Year's Resolutions...in a clear mind or not so clear mind depending upon number of glasses of wine or substitute.

[hegemony]

Ideal for those who have no plans for New Year's Eve. A glass of wine or two or more makes designing the Exel program endurable.  This is especially appropriate as you make your New Year's Resolutions...in a clear mind or not so clear mind depending upon number of glasses of wine or substitute.

<{POST_SNAPBACK}>

 

 

Kona...it is only 3:21 pm here silly...too early to start libations...but I'll take a glass of madeira if you insist

[Kona]

Ideal for those who have no plans for New Year's Eve. A glass of wine or two or more makes designing the Exel program endurable.  This is especially appropriate as you make your New Year's Resolutions...in a clear mind or not so clear mind depending upon number of glasses of wine or substitute.

<{POST_SNAPBACK}>

 

 

Kona...it is only 3:21 pm here silly...too early to start libations...but I'll take a glass of madeira if you insist

<{POST_SNAPBACK}>

 

It's never too early, but it is currently 6:27 pm here. Will Chardonnay suffice? I've already had one glass with dinner.

[hegemony]

t's never too early, but it is currently 6:27 pm here. Will Chardonnay suffice? I've already had one glass with dinner.

<{POST_SNAPBACK}>

I've already moved onto the single malt scotch...30 year old...

[ingenue]

You can eliminate some intermediate steps in your table by using the =SUMPRODUCT function.

 

If you have the following table,

 

_|_A______|_B_____

1| Balance | Rate

2| 5000.00 | 0.0899

3| 6000.00 | 0.1499

4| 3000.00 | 0.0549

5| 2000.00 | 0.1299

 

then the single equation

 

=SUMPRODUCT(A2:A5,B2:B5)/SUM(A2:A5)

 

will yield the average interest rate weighted by balance (11.08% for the sample table above).

 

-ingenue

[hegemony]

ingenue, I like having the intermediate steps.

Edited January 31, 2005 by hegemony

[Eyecannon]

Thanks, my effective rate is 7.4%

[hegemony]

mine is now 5.16444%

[hegemony]

my weighted average interest rate in up to 5.303%

[EastRidge]

For me, 22.27% ... thanks, Chase and Sears! (30 and 29)

 

I got a card at 7.99% fixed, but they won't approve my

balance transfers. Meanwhile, the floating rates keep

rising and I keep sinking.

 

ER

[hegemony]

5.28% for the new year.

[hegemony]

5.28% for the new year.

 

5.27%