Effective Annual Interest Rate

[John]

I'm trying to work out the effective annual interest rate for:

an item can be purchased for a payment of $100 today and a further $1,300 in
8 months time. The other option is to pay in full today for a cash price of
$1,200.

How can I calculate the effective annual interest rate (assuming monthly
compounding) being implicitly charged?

 

[macropod]

Hi John,

Technically, there isn't a compounding monthly interest rate for the
situation you have described, since there's only a single payment, after 8
months (rather than 8 monthly payments).

Also, you can only solve for the interest rate through iteration, there
being no formula for it (unless the periodic payment is zero). In this case,
the periodic payment (at the 8-month interval) is $200.

You can calculate an effective annual interest rate (through iteration),
using Excel's NPER formula and solving for NPER = 1.5, using the Goal Seek
function.

Cheers

 

[Fred Smith]

You can get your answer by rephrasing your question to: "If I invest $1100
today, what interest rate will I need to have $1300 in 8 months time?". Use
the Rate function to get your answer:

=Rate(8,0,-1100,1300)

This gives you the monthly rate. To get the effective annual rate, use (with
the Analysis ToolPak loaded):

=Effect(rate(8,0,-1100,1300),12)*12

I get 25.5% as the effective rate. Pay the whole $1200 today.