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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
Back
Top