is there a Loan Amoritization Schedule for Canada available?

[Ghostrider]

I am looking for a Excel spreadsheet Loan amoritization for Canada, I can
only find the one for the USA on the MS Office website.

Thanks

 

[trip_to_tokyo]

What, exactly, are you trying to find out?

The PMT function will give you the monthly repayments ("loan amortization
schedule"?) if you known the Principal (the loan amount) the rate (of
interest) and the number of payments (in months).

Maybe this helps?

 

[James Silverton]

Ghostrider wrote on Sun, 20 Sep 2009 10:21:02 -0700:

I am looking for a Excel spreadsheet Loan amoritization for
Canada, I can only find the one for the USA on the MS Office
website.

Thanks

For my information, what is the difference?
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

 

[JoeU2004]

For my information, what is the difference?

Refer to http://support.microsoft.com/kb/294396/en-us .

Canadian loans specify the annual interest rate as a semi-annually
compounded rate; so the monthly rate, for example, is RATE(2,0,-1,1+r)/6,
where "r" is the annual rate. In contrast, US loans specify a nominal
annual interest rate; so the monthly rate is simply r/12.


----- original message -----

 

[JoeU2004]

Maybe this helps?

Probably not.

First, PMT for a Canadian loan must be calculated differently than for a US
loan. Refer to http://support.microsoft.com/kb/294396/en-us .

Canadian loans specify the annual interest rate as a semi-annually
compounded rate; so the monthly rate, for example, is RATE(2,0,-1,1+r)/6,
where "r" is the annual rate. In contrast, US loans specify a nominal
annual interest rate; so the monthly rate is simply r/12.

Second, a loan amortization schedule is much more than simply the monthly
payment amount. For a tutuorial, take a look at any of the loan
amortization templates available from Microsoft Office Online.


----- original message -----

 

[JoeU2004]

Errata....

Canadian loans specify the annual interest rate as a semi-annually
compounded rate; so the monthly rate, for example, is RATE(2,0,-1,1+r)/6,
where "r" is the annual rate.

Argh! I always get this wrong, in part because I do not agree with the
Canadian terminology.

According to http://support.microsoft.com/kb/294396/en-us , the monthly rate
is computed by RATE(6,0,-1,1+r/2).


----- original message -----

 

[JoeU2004]

Errata....

Canadian loans specify the annual interest rate as a semi-annually
compounded rate; so the monthly rate, for example, is RATE(2,0,-1,1+r)/6,
where "r" is the annual rate.

Argh! I always get this wrong, in part because I do not agree with the
Canadian terminology.

According to http://support.microsoft.com/kb/294396/en-us , the monthly rate
is computed by RATE(6,0,-1,1+r/2).


----- original message -----

 

[JoeU2004]

I am looking for a Excel spreadsheet Loan amoritization for Canada,
I can only find the one for the USA on the MS Office website.

I don't know if a Canadian template exists at MS Office Online. But you
might be able to modify a US template.

Since you don't say what US template(s) you looked at, and you don't say
what Excel version you have, it is difficult to give specifics.

But perhaps an example will suffice. (Note: I have not tested the
following myself.)

Look at the Excel 2003 template called "Mortgage Amortization Schedule".

The monthly payment is computed in J5. Change

PMT(E5/12,Q208,-E4)

to

PMT(RATE(6,0,-1,1+E5/2),Q208,-E4)

The monthly interest rate is computed in each entry in G12:G23. At a
minimum, change

D12*(E$5/12)


to

D12*RATE(6,0,-1,1+$E$5/2)

Refer to http://support.microsoft.com/kb/294396/en-us for insight into these
changes.

 

[JoeU2004]

PS....

The monthly interest rate is computed in each entry in G12:G23.
At a minimum, change [to]
D12*RATE(6,0,-1,1+$E$5/2)

I neglected to point to a similar change in I27, to be copied down. At a
minimum, change

PV(E$5/12,N205,-J$5)

to

PV(RATE(6,0,-1,1+$E$5/2),N205,-J$5)

"At a minimum" is a key phrase here. It would be better to compute
RATE(6,0,-1,1+$E$5/2) in one cell, and to refer to that cell in G12:G23 and
in I27 et al.


----- original message -----

 

[James Silverton]

JoeU2004 wrote on Sun, 20 Sep 2009 15:22:50 -0700:

Refer to http://support.microsoft.com/kb/294396/en-us .

Canadian loans specify the annual interest rate as a
semi-annually compounded rate; so the monthly rate, for
example, is RATE(2,0,-1,1+r)/6, where "r" is the annual rate. In
contrast, US loans specify a nominal annual interest rate; so the
monthly rate is simply r/12.

----- original message -----

Thanks for the information! It's an interesting difference and I wonder
how it arose but that doesn't really matter to me.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not