How do you calculate effective interest

C

Connie Walsh

Hi:

Here in Canada we calculate interest on mortgages semi-annually. How can
I do that in an excel workbook?

Connie
 
K

Kevin H. Stecyk

Hi Connie,

I am guessing you want the monthly interest rate so that you can create a
payment schedule?

A1=5% annual interest

B1 monthly interest
B1=(1+A1/2)^(1/6)-1
B1=0.4124 %

So if you have a 10 years, you now have 120 months. You can use your
financial formulas with your monthly rate.

Does that answer your question? If not, please provide more detail.

Regards,
Kevin
 
A

alainB

Hi,

I think it should be:

A1=5% annual interest ( 0,05 )

B1 monthly interest
B1=(1+A1/2)^(1/6)-1
B1=0,0041239154......

Alai
 
K

Kevin H. Stecyk

alainB ...
Hi,

I think it should be:

A1=5% annual interest ( 0,05 )

B1 monthly interest
B1=(1+A1/2)^(1/6)-1
B1=0,0041239154......

Alain
Click to expand...

Hi Alain,

It was correct in the original version. If you enter 5% (with the percent
sign), Excel correctly infers 0.05. Or A1 might be formatted as a
percentage. You could, of course, use the more direct method of simply 0.05
(or 0,05).

In my case, B1 was formatted to %. So the answer of 0.4124% is okay also.
Rather than showing a bunch of decimals, I truncated it to four decimals. I
like to be able to look at the answer and get a "gut" check that the answer
is about right. 5% annually ought to be near 0.4% per month. And indeed
it is.

Regards,
Kevin
 
A

alainB

Hi Keven,

I am very surprise that you had time to read my post.

A few minutes after posting, I realised that there was nothing wrong i
your post as you explain it in your last one. So I deleted min
immediately.

Sorry!;)

Alai
 
K

Kevin H. Stecyk

alainB wrote...
Hi Keven,

I am very surprise that you had time to read my post.

A few minutes after posting, I realised that there was nothing wrong in
your post as you explain it in your last one. So I deleted mine
immediately.

Sorry!;)
Click to expand...

AlainB,

No harm, no foul. Others might have learned something from it, so no big
deal.

Incidentally, once you make a post, you can never fully retrieve it. It
goes out to various news servers around the globe. Some servers might honor
your request to delete a message, others will ignore it. For example, I
still see all your posts related to this thread.

Again, no worries.

Regards,
Kevin
 
Top