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

[Guest]

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

 

[Guest]

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

 

[Fred Smith]

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.

 

[Guest]

Thanks Fred,

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

S

 

[Fred Smith]

Glad it helped. Thanks for the feedback.