Expert: Scott A Wilson - 12/8/2008

Question
QUESTION: steve gave me a formula for the following

starting balance
$1000.00
and is compounded at the rate of 10% every 30 days for
15 thirty day periods

his formula was

B=1000(1 + (1.20/12))^15

=1000*(1 + (1.2/12))^15  ( I used this in excel )

Result was $4,177.25

when I use this formula I am close but not exact to
me starting with 10% of 1000 or $100.00 and multiply times 1.1 fifteen times and then adding the results of each 30 period

$100.00   
$110.00   
$121.00   
$133.10   
$146.41   
$161.05   
$177.16   
$194.87   
$214.36   
$235.79   
$259.37   
$285.31   
$313.84   
$345.23   
$379.75   
$3,177.25   TOTAL

which is correct and what formula would let me plug in the balance and number of 30 day periods and come up with the correct snswer

ANSWER: Here is what I've been told:
The original amount was $1,000.00.
The interest rate is 10%.
There are 15 30 day periods involved.

I will assume the interest rate is per annum,
which means 0.833...%/ month (a little less than 1% each month).

His formula was incorrect.  I don't know where he came up with 1.2/12 as the interest rate.  The interest rate, as was said, is 0.10/12 .

In your method, the starting amount should be $1,000 (not $100).  The interest rate in this calculation is %10 per 30 days.  I have never seen anyone charage that kind of interest on the open market.  It should be 10%/12 per month.

The correct answer is $1,000(1 + 0.10/12)^15 (his way) or $1,000 multiplied by (1 + 0.10/12) 15 times (your way).

The correct answer is $1,132.56 .



---------- FOLLOW-UP ----------

QUESTION: the rate is 10% per 30 day period

(this is a quirky penalty rule on some insurance claims i am working) but it really is 10% per 30 days on an balance due. Balance due for this example is $1000.00.

The correct "long" calculation and answer is as follows:

$100.00 (1st thirty day period is 10% of balance)
$110.00 (2nd thirty day period is 1st (100.00) times 1.1)
$121.00 (3rd thirty day period is 2nd (110.00) times 1.1)
$133.10 and so on
$146.41
$161.05
$177.16
$194.87
$214.36
$235.79
$259.37
$285.31
$313.84
$345.23
$379.75
$3,177.25 TOTAL


I'm looking for a shortcut I can use in Excel to arrive at $3,177.25 by I guess plugging in the original balance due and the number of 30 day periods

I have 55 claims to work and complete by Wednesday and some have over fifty 30 day periods...

Thanks, Rodney






ANSWER: Multiply by 1.1^15.  I'm sorry, I only computed 14 by hand since I was think that the $100 was the first one.  The last one should really be 4,177.25.

1.1 to the 15th is 4.177248169, so multiply each of them by that number.


---------- FOLLOW-UP ----------

QUESTION: For this example we are using the months as 30 day periods and the formula doesn't make sense - in this example each of the 15 (30 day period) is multiplied x 1.1

August 1 Balance or (principal) of $1,000.00
______________________________

Sept 1 Interst $100.00   
Oct 1 Interest $110.00
Nov 1 Intrest $121.00
Dec 1 Interest $133.10   
Jan 1 Interest $146.41
Feb 1 Interest $161.05   
March 1 Interest $177.16   
APril 1 Interest $194.87   
May Interest $214.36   
June Interest $235.79   
July Interest $259.37
August 1 Interst $285.31   
Sept Interest $313.84   
October Interest $345.23   
Nov Interest $379.75   

Total Interest  $3,177.25

Answer
I read the problem as 10% every 30 days.  It may be meant that it is really 10% per anum and interest is given every 30 days, so that the interest rate per period is 0.10/12 = 0.083333...

In this case, the total interest after 15 months would be given by
(1.083333...)^15.

Remember that the 1.083333... is really 1 + .1/12 = 1 + 1/120.

Note that this is true, since over 12 months the basic interest would be 12/120 = 1/10.
Compounded, however, the interest per year is (1+1/120)^12.