Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Effective Annual Interest Rate
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? |
#2
|
|||
|
|||
Answer: Effective Annual Interest Rate
__________________
I am not human. I am an Excel Wizard |
#3
|
|||
|
|||
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 "John" wrote in message ... 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? |
#4
|
|||
|
|||
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. -- Regards, Fred Please reply to newsgroup, not e-mail "John" wrote in message ... 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? |
#5
|
|||
|
|||
Effective Annual rate is the rate if compounded annually, will yield the
same amount of interest as if compounded monthly So using the function =Rate(8,0,-1100,1300) will give a monthly rate of 2.11% Hence the effective Annual rate = ((1+2.11%)^12 )-1 =28.47% Therefore if you financed 1100 today you have to pay 1300 in 8 months and 1.2847 * 1100 = 1413.17 in 12 months Using the effect function that fred suggested we are erroneously dividing the monthly rate by 12 and then compounding that If 25.5% is the correct annual rate then in 12 months 1100 would be = 1100* 1.255 = 1380.50 If in 8 months 1100 increases by 200 to 1300 then how come in next 4 months it only increases by 80.5 (from 1300 to 1380.5) whereas it should at least increase by 100 ignoring the effect of compounding "Fred Smith" wrote in message ... 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. -- Regards, Fred Please reply to newsgroup, not e-mail "John" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compound Annual Growth Rate | Excel Discussion (Misc queries) | |||
calculate interest on an increasing balance with variable rate | Excel Discussion (Misc queries) | |||
Annual Percentage Rate for Mortgage | Excel Discussion (Misc queries) | |||
Annual Percentage Rate | Excel Discussion (Misc queries) | |||
Calculating Interest where rate changes per quarter | Excel Discussion (Misc queries) |