Canadian Car Loan Amortization Macro

Is there a car loan amortization macro/file that I could download?  I 
have a built-in one with my Excel but it doesn't yield the same results 
as my financial institution. The interest and principal are off.

Thanx 

Methos:
> Is there a car loan amortization macro/file that I could download?  I 
> have a built-in one with my Excel but it doesn't yield the same results 
> as my financial institution. The interest and principal are off.
>
It is built in.
Try Tools|Numeric Tools|Analysis Tools...

or various formulae, notably those starting @AMT...

It is unlikely, but not impossible, that QP will agree with the output of 
your financial institution. There are various methods and conventions in 
these calculations. Institutions tend to choose those beneficial to 
themselves.

In the UK, the govt. has from time to time come up with measures designed 
to make comparison simpler: APR, AER, and so on. A little time with a 
spreadsheet showed that matters were still not entirely straightforward.
-- 
Good wishes!
Roy Lewis
C_Tech volunteer
(UK)

Methos wrote:
> Is there a car loan amortization macro/file that I could download?  I 
> have a built-in one with my Excel but it doesn't yield the same results 
> as my financial institution. The interest and principal are off.

Mortgages in Canada are calculated differently than in the US; since your car 
loan amortization schedule is different from what it should be, it may well be 
that your car loan schedule is calculated like a home loan.

@PMTC gives you the monthly loan payment for a Canadian mortgage.  Although the

function works correctly, QP's help for this function ironically cites the 
formula for a US mortgage payment (P*((R/12)/(1-(1+(R/12))^-(N*12)))).

The difference between the two methods boils down to how exactly the interest is

applied in the calculation.  The US method is fairly straightforward in that it

applies the annual rate as an equivalent periodic rate that corresponds to the 
frequency of the payment schedule (e.g., R/12 for a monthly payment schedule). 
The Canadian method is more complex in that it compounds interest semiannually 
while applying it to a monthly schedule.  This easily leads to computational 
dissonance, unless you first make a convoluted adjustment to the interest rate 
BEFORE applying it to the payment schedule calculation -- IMO an example of 
needless obfuscation by a professional elite.  It's akin to forcing you to run 
around the block a couple of times just to find your way to the next-door 
neighbor's house.  In the end, it amounts to a storm in a teacup: the Canadian 
method works out to a slightly lower effective interest rate for the borrower --

somewhat like the "real feel" temperature vs. the nominal thermometer
reading.

For example, a 5-year loan for $10,000 at 7% would cost about $28 less interest

in Canada than in the US.  Another way of looking at it is that a Canadian 
borrower would get the same outcome with a 7% loan as a US borrower would get 
with a 6.9% loan.  More importantly, the lender on either side of the border 
will quote whatever nominal rate is compatible with the prevailing market, so 
the exact nominals are nothing more than advertising -- what counts is the 
EFFECTIVE rate WITHIN THE CONTEXT of the applicable market environment.

Going back to the issue of the APPLICABLE interest rate for a loan payment 
calculation, in the case of a 7% loan, the US method would apply a monthly 
interest rate of 0.583333% (.07/12), whereas the Canadian method would apply a 
monthly interest rate of 0.575004% (((1+(.07/2))^2)^(1/12)-1).  It's this 
difference in APPLYING the STATED interest rate (which in both cases was 7%) 
that is causing all the difference in the outcome.

This then also provides the clue on how to use QP's loan functions for a 
Canadian loan:  wherever the loan function calls for the interest rate, instead

of entering R/12, enter ((1+(R/2))^2)^(1/12)-1.  A simple test will show that

   @PMT(10000,((1+(0.07/2))^2)^(1/12)-1,5*12)

   and

   @PMTC(10000,0.07,5*12)

yield identical results.

The above principle can also be applied to @IPAYMT and @PPAYMT, for which QP has

no canned Canadian-method version.

Cheers,
Uli

March 3, 2008

Bravo for a clear presentation of a difficult topic, Uli. 

I could have saved myself a lot of trouble painstaking crafting a 
spreadsheet to track my mortgage with the Cdn semi-annual calculation of 
interest, if I had known about @PMTC.

Don Codling
WP 12.0.0.602
DP 2.6x
Windows XP home, SP2
512 MBytes RAM

Uli wrote:
>
> Methos wrote:
>> Is there a car loan amortization macro/file that I could download?  I 
>> have a built-in one with my Excel but it doesn't yield the same 
>> results as my financial institution. The interest and principal are
off.
>
> Mortgages in Canada are calculated differently than in the US; since 
> your car loan amortization schedule is different from what it should 
> be, it may well be that your car loan schedule is calculated like a 
> home loan.
>
> @PMTC gives you the monthly loan payment for a Canadian mortgage.  
> Although the function works correctly, QP's help for this function 
> ironically cites the formula for a US mortgage payment 
> (P*((R/12)/(1-(1+(R/12))^-(N*12)))).
>
> The difference between the two methods boils down to how exactly the 
> interest is applied in the calculation.  The US method is fairly 
> straightforward in that it applies the annual rate as an equivalent 
> periodic rate that corresponds to the frequency of the payment 
> schedule (e.g., R/12 for a monthly payment schedule). The Canadian 
> method is more complex in that it compounds interest semiannually 
> while applying it to a monthly schedule.  This easily leads to 
> computational dissonance, unless you first make a convoluted 
> adjustment to the interest rate BEFORE applying it to the payment 
> schedule calculation -- IMO an example of needless obfuscation by a 
> professional elite.  It's akin to forcing you to run around the block 
> a couple of times just to find your way to the next-door neighbor's 
> house.  In the end, it amounts to a storm in a teacup: the Canadian 
> method works out to a slightly lower effective interest rate for the 
> borrower -- somewhat like the "real feel" temperature vs. the
nominal 
> thermometer reading.
>
> For example, a 5-year loan for $10,000 at 7% would cost about $28 less 
> interest in Canada than in the US.  Another way of looking at it is 
> that a Canadian borrower would get the same outcome with a 7% loan as 
> a US borrower would get with a 6.9% loan.  More importantly, the 
> lender on either side of the border will quote whatever nominal rate 
> is compatible with the prevailing market, so the exact nominals are 
> nothing more than advertising -- what counts is the EFFECTIVE rate 
> WITHIN THE CONTEXT of the applicable market environment.
>
> Going back to the issue of the APPLICABLE interest rate for a loan 
> payment calculation, in the case of a 7% loan, the US method would 
> apply a monthly interest rate of 0.583333% (.07/12), whereas the 
> Canadian method would apply a monthly interest rate of 0.575004% 
> (((1+(.07/2))^2)^(1/12)-1).  It's this difference in APPLYING the 
> STATED interest rate (which in both cases was 7%) that is causing all 
> the difference in the outcome.
>
> This then also provides the clue on how to use QP's loan functions for 
> a Canadian loan:  wherever the loan function calls for the interest 
> rate, instead of entering R/12, enter ((1+(R/2))^2)^(1/12)-1.  A 
> simple test will show that
>
>   @PMT(10000,((1+(0.07/2))^2)^(1/12)-1,5*12)
>
>   and
>
>   @PMTC(10000,0.07,5*12)
>
> yield identical results.
>
> The above principle can also be applied to @IPAYMT and @PPAYMT, for 
> which QP has no canned Canadian-method version.
>
> Cheers,
> Uli
>

Don Codling wrote:
> I could have saved myself a lot of trouble painstaking crafting a 
> spreadsheet to track my mortgage with the Cdn semi-annual calculation of 
> interest, if I had known about @PMTC.

FWIW, a spreadsheet calculation of an amortization schedule will almost always 
vary slightly from the lender's official schedule due to rounding errors.  In 
order to get it 100% accurate to the penny, you would have to know the lender's

exact method of rounding.  I suspect (but I don't know) that the main source of

potential deviation stems from the precision level of the interest rate which 
the lender applies.  Because you don't know if the lender goes to 7, 8, 9 or 
more decimals when applying the periodic rate, you won't be able to reproduce 
the lender's amortization schedule exactly, but with a spreadsheet you can come

very close if you are careful in how you apply the available tools.  I have 
found that I can substantially reduce the amount of deviation by following a few

simple guidelines, which should help mitigate the accumulation of penny errors:

0.  Do NOT round the periodic interest rate.
1.  Calculate the periodic payment and round to 2 decimals.
2.  Calculate the periodic interest and round to 2 decimals.
3.  Calculate the principal paid for each payment by subtracting the periodic 
interest from the periodic payment.

Ironically, you will find that by using rounded amounts you introduce a 
cumulative "error", but this apparent error corresponds more closely
to what 
your lender actually applies.  This seeming oddity arises from the fact that 
(without explicit instructions to employ rounding) the spreadsheet is dealing 
with fractional pennies in its calculation.  In the real world, however, you 
don't pay in fractional pennies, and neither your interest nor your principal 
paid are credited in fractional pennies.  Thus, the near-perfect sum (normally 
to within 1 billionth of a penny!) achieved by the algorithm of the 
spreadsheet's loan functions cannot be duplicated in the real world -- in this 
case, the spreadsheet is TOO ACCURATE!!  Stated differently, the spreadsheet 
divides a whole into portions (payments) that have boundaries which don't exist

in the real world.  In reality then, the penny-boundary-induced error is allowed

to accumulate for the duration of the loan until BEFORE the final payment. 
Then, the final payment will be adjusted by whatever amount is necessary for 
your total principal paid to equal the original loan amount.  (Caveat emptor: 
I'm not a finance expert.  This is just what I've picked up "watching the
world 
go by."  My impression is that financial professionals tend to be fairly 
tight-lipped about the cogs and wheels of their trade, probably because 
ignorance generates business.  I'd be interested in any corrections/explanations

from someone in-the-know.)

Cheers,
Uli