Financial Spreadsheet Help

[Guest]

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.

 

[Guest]

Hi,

I am having trouble trying to comprehend what you are trying to achieve so I
need a few answers.

What does the /3 associated with the interest represent? Is it meant to be
divided by 3?

You say "if the principle hits a new threshold in the middle of the month",
then calculate everything above that threshold at the higher rate. Do I
assume that the interest is an annual interest rate applied on a daily
principle?
(Example: 5% divided by 365).

The interest rates have to be basically numeric. The way they appear in your
table with the /3 they are text characters which would need to be converted
to numeric to use them. So in answer to your query if you format cells to
percentage you can enter 5% and they will display as such but the actual
numeric value stored by Excel is 0.05. (You can establish this by
reformatting back to numeric with 2 decimal places)

Regards,

OssieMac

 

[Guest]

OssieMac,

I sure appreciate you trying to help me out. The /3 means to divide it by 3.
For example, the 5%/3 means 5% divided by three. The reason I did that is
because I have the quarterly rate (like 5%) and I want to divide it by 3 to
get the monthly rate. I can change that easy enough, but if I did it like
5%/3, I thought it would carry it out to the most decimal places and be more
accurate.

"if the principle hits a new threshold in the middle of the month" This
bascially means that I want to pro-rate the monthly interest earned. Let me
give you an example: If I have a principle of $140,000 on Jan. 1, it will be
earning a 8.33% (25%/3) rate of return. If I multiply $140,000 by 8.33%, it
gives me $11,662 in interest earned. Adding $140,000 and $11,662 is $151,662.
Well, at $150,000, the rate earned automatically goes up to 10% (30%/3). So
the $1,662 (the amount over $150,000) actually earned an extra 1.66% (the
difference between 10% and 8.33%) over the money below $150,000. So the
principle on Feb. 1 would not be $151,662. It would actually be
$151,662+($1,662*1.66%)=$151,689.

I figured out a formula to calculate the additional interest earned during
the month if the principle hit a new threshold and automatically started
earning at a higher rate. I did this for the increases of 1.66% (5% a
quarter). Then I just made different sub-groups for each interest rate, and
would apply this formula where I saw the principle reach one of those
thresholds in order to calculate the additional interest. This is the formula
I was using:
=IF(SUM(B13:C13)>150000,(SUM(B13:C13)+(SUM(B13:C13)-150000)*1.66667%))

In that spreasheet I had: Month in A, Monthly beginning priciple in B,
Interest earned in C, and Monthly ending principle in D.

I looked like this for the $150,000 threshold mark:
Feb-08 $142,708.16 $11,887.59 $154,672.35

I would apply the above formula to the $154,672.35 cell.
I want to creat a spreadsheet that will automatically do this for all the
cells in one column automatically and would do it for all the different
interest rates. I hope I explained this okay, I wish I could just call you
and talk to you about it. Another thing is, in the above formula I have
">150000". So, I think it will actually calculate everything from 15001 at
the higher rate. I want it to start at 150000, so should probably try to
change my current formulas to calculate the rate I want "<150000".

I know this is long, thanks again for you help. I'm studing hard learn more
about how to do this.