How do I calculate the interest payment manually (without using IP
 

Hi there,

I used IPMT but I'm Canadian and it doesn't work for Canada so I basically
need to do an amortization schedule and I have no clue the formulas needed to
do this manually.

I fugure you need a principle column, interest column, monthly payment column.
I need to figure out the compounded interest.

Any guidance will be helpful

Thanks

Suzanne

How do I calculate the interest payment manually (without using IP
 

A couple of questions

Are you working from a Total Payable start point? Or a Brought Forward
Balance?

Are you trying to calculate the interest element of an installment which
includes both an element of principle repayment and interest?

Regards

Phil


"Grd" wrote:

Hi there,

I used IPMT but I'm Canadian and it doesn't work for Canada so I basically
need to do an amortization schedule and I have no clue the formulas needed to
do this manually.

I fugure you need a principle column, interest column, monthly payment column.
I need to figure out the compounded interest.

Any guidance will be helpful

Thanks

Suzanne

How do I calculate the interest payment manually (without using IP
 

Canadian mortgages are compounded semi-annually. As with all financial
functions, you need to calculate the periodic interest rate. Then all the
functions will work.

Let's suppose you make monthly payments, and the quoted interest rate is 5%. As
Canadian mortgages are compounded semi-annually, you know this is actually 2.5%
every six months. If you borrowed $100 with an interest rate of 2.5%, you would
owe $102.50 at the end of six months. But your payments are monthly. What you
need to know is: what monthly interest rate will turn $100 into $102.50 in six
months? Use the rate function for this, as in:

=rate(6,0,-100,102.50)

Or more generally:

=rate(6,0,-1,1+i/2)

Now use this rate in all your financial functions.

--
Regards,
Fred


"Grd" wrote in message
...
Hi there,

I used IPMT but I'm Canadian and it doesn't work for Canada so I basically
need to do an amortization schedule and I have no clue the formulas needed to
do this manually.

I fugure you need a principle column, interest column, monthly payment column.
I need to figure out the compounded interest.

Any guidance will be helpful

Thanks

Suzanne

How do I calculate the interest payment manually (without usin
 

Thanks Fred,

Now had a chance to try this. It works as you describe. Excellent

S

"Fred Smith" wrote:

Canadian mortgages are compounded semi-annually. As with all financial
functions, you need to calculate the periodic interest rate. Then all the
functions will work.

Let's suppose you make monthly payments, and the quoted interest rate is 5%. As
Canadian mortgages are compounded semi-annually, you know this is actually 2.5%
every six months. If you borrowed $100 with an interest rate of 2.5%, you would
owe $102.50 at the end of six months. But your payments are monthly. What you
need to know is: what monthly interest rate will turn $100 into $102.50 in six
months? Use the rate function for this, as in:

=rate(6,0,-100,102.50)

Or more generally:

=rate(6,0,-1,1+i/2)

Now use this rate in all your financial functions.

--
Regards,
Fred


"Grd" wrote in message
...
Hi there,

I used IPMT but I'm Canadian and it doesn't work for Canada so I basically
need to do an amortization schedule and I have no clue the formulas needed to
do this manually.

I fugure you need a principle column, interest column, monthly payment column.
I need to figure out the compounded interest.

Any guidance will be helpful

Thanks

Suzanne

How do I calculate the interest payment manually (without usin
 

Glad it helped. Thanks for the feedback.

--
Regards,
Fred


"Grd" wrote in message
...
Thanks Fred,

Now had a chance to try this. It works as you describe. Excellent

S

"Fred Smith" wrote:

Canadian mortgages are compounded semi-annually. As with all financial
functions, you need to calculate the periodic interest rate. Then all the
functions will work.

Let's suppose you make monthly payments, and the quoted interest rate is 5%.
As
Canadian mortgages are compounded semi-annually, you know this is actually
2.5%
every six months. If you borrowed $100 with an interest rate of 2.5%, you
would
owe $102.50 at the end of six months. But your payments are monthly. What you
need to know is: what monthly interest rate will turn $100 into $102.50 in
six
months? Use the rate function for this, as in:

=rate(6,0,-100,102.50)

Or more generally:

=rate(6,0,-1,1+i/2)

Now use this rate in all your financial functions.

--
Regards,
Fred


"Grd" wrote in message
...
Hi there,

I used IPMT but I'm Canadian and it doesn't work for Canada so I basically
need to do an amortization schedule and I have no clue the formulas needed
to
do this manually.

I fugure you need a principle column, interest column, monthly payment
column.
I need to figure out the compounded interest.

Any guidance will be helpful

Thanks

Suzanne