Calculate SIMPLE interest??

[Guest]

Hi
All the functions i seem to find (ISPMT,IPMT,etc) are calculating compound interest by default and there doesn't seem to be a way to modify that. Is there a function that will calc simple interest? $24,700 @ 2.9%apr for 60 months making monthly payments

Thanks muc

Rick

 

[Fred Smith]

As soon as you say apr, you're using compound interest, not simple interest.
Aren't you just asking for the payments on a $24700 loan for 60 months at
2.9%? If so, PMT will give your answer:

=PMT(2.9%,60,24700)
=-873.45 (the negative signifies money's coming out of your pocket)

If you *really* want simple interest, it's just the loan amount times the
rate times the period. Eg,

=24700*2.9%*60/12

However, the problem with this is that you are being charged interest on the
full amount for the full period. You're not getting any credit for the
payments you've made. The PMT function takes all this into account. It
calculates one month's interest on $24700. Then you make a payment of
$873.45, so it calculates the next month's interest on the outstanding
balance of $23,826.55. And so on, for the 60 months.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

Hi,
All the functions i seem to find (ISPMT,IPMT,etc) are calculating compound

interest by default and there doesn't seem to be a way to modify that. Is
there a function that will calc simple interest? $24,700 @ 2.9%apr for 60
months making monthly payments.

 

[Norman Harker]

Hi!

If it really is simple interest on each of the payments then you need:

=(60/2)*(24700*2.9%/12*60+24700+24700*2.9%/12*60+24700+59*-((24700*2.9
%/12*60+24700)-(24700*2.9%/12*59+24700)))
Returns: 1591235.75

Almost certainly capable of simplification but it's all based upon the
sum of an arithmetic progression. I haven't simplified because I'm not
sure that you really want simple interest and that is fairly rare
these days.

The first deposit of 24700 accumulates for 60 months at 2.9%/12 simple
interest.
This gives the first term of:
=24700*2.9%/12*60+24700
returns: 28281.5

The second deposit of 24700 accumulates for 59 months at 2.9%/12
simple interest
This gives the second term as:
=(24700*2.9%/12*59+24700)
returns: 28221.80833
The difference is -59.6916666666657

You could continue but you'll note a constant difference of -59.61

So we can sum the different amounts using:
Sn = n/2(t1+tn)

tn (the last term of the progression) is found using:
tn = t1+(n-1)d
=28281.5+(59*-59.69166667)
Returns: 24759.69

Putting that in the sum of arithmetic progression formula:
=60/2*(28281.5+24759.69)
Returns: 1591235.7

All the long formula above does is the string this into a single
formula.

With:
G1
24700
G2
2.9%
G3
60

Formula becomes:
=(G3/2)*(G1*G2/12*G3+G1+G1*G2/12*G3+G1+(G3-1)*-((G1*G2/12*G3+G1)-(G1*G
2/12*(G3-1)+G1)))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.