C
C.C.
I am trying to create a spread sheet to calculate variable rates. I am a
fairly new user, using Excel 2007. I have tried to read and figure this out,
but can’t quite get it.
Month Beginning Principle Interest Draw Remaining Principle
1
2
Invest. Amount Monthly Rate
$ 2,500.00 5%/3
$ 5,000.00 7%/3
$ 10,000.00 8%/3
$ 25,000.00 10%/3
$ 50,000.00 15%/3
$ 75,000.00 20%/3
$100,000.00 25%/3
$150,000.00 30%/3
$200,000.00 35%/3
The spreadsheet portion above is at A2:E4. The table below it is at H9:I18.
I want to calculate the amount of interest each month, and the interest rate
changes depending on the amount of principle. The thresholds are in the table
with the corresponding rate. Also, if the principle hits a new threshold in
the middle of the month, I want to calculate everything above that threshold
at the higher rate. I’m not sure if I can leave the rates entered like they
are or if I have to enter a decimal amount for the percentage. I have
received some helpful suggestions, but can’t get any to work right.
One suggestion was to use , =SUMPRODUCT(--(B3>$H$10:$H$18),
(B3*($I$10:$I$18))). Someone else suggested I use VLOOKUP, I’m not sure how
to do that. Any help you can offer me will be greatly appreciated. This is
driving me insane.
fairly new user, using Excel 2007. I have tried to read and figure this out,
but can’t quite get it.
Month Beginning Principle Interest Draw Remaining Principle
1
2
Invest. Amount Monthly Rate
$ 2,500.00 5%/3
$ 5,000.00 7%/3
$ 10,000.00 8%/3
$ 25,000.00 10%/3
$ 50,000.00 15%/3
$ 75,000.00 20%/3
$100,000.00 25%/3
$150,000.00 30%/3
$200,000.00 35%/3
The spreadsheet portion above is at A2:E4. The table below it is at H9:I18.
I want to calculate the amount of interest each month, and the interest rate
changes depending on the amount of principle. The thresholds are in the table
with the corresponding rate. Also, if the principle hits a new threshold in
the middle of the month, I want to calculate everything above that threshold
at the higher rate. I’m not sure if I can leave the rates entered like they
are or if I have to enter a decimal amount for the percentage. I have
received some helpful suggestions, but can’t get any to work right.
One suggestion was to use , =SUMPRODUCT(--(B3>$H$10:$H$18),
(B3*($I$10:$I$18))). Someone else suggested I use VLOOKUP, I’m not sure how
to do that. Any help you can offer me will be greatly appreciated. This is
driving me insane.