Payment function for canada

[csda1]

Re: Payment function for canada

Hi Jean-Pierre,

Has anyone created such a function for Canadian users, or is there a way to edit the existing function and adapt it to our needs

The Payment() function is buried in Alpha's Compiled "C" code, but here is an equivalent I wrote in XBasic that perhaps can be modified

FUNCTION PaymentX AS N (loan_amount as N,periodic_interest_rate as n,payment_periods as N)
PaymentX=loan_amount*(periodic_interest_rate*((1+periodic_interest_rate)**payment_periods))/(((1+periodic_interest_rate)**payment_periods)-1)
END FUNCTION

[DougMcLean]

Re: Payment function for canada

I am using Excel 2003.

1 DATA DESC
2 0.1 Interest
3 360 # of Monthly Payments
4 100000 Amount of Loan

($877.57) Monthly Payment at the end of Period
($870.32) Monthly Payment at the beginning of Period

=PMT(A2/12,A3,A4) (end of period)
=PMT(A2/12,A3,A4,0,1) (beginning of period)

Help notes.....

PMT

See Also

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

PMT(rate,nper,pv,fv,type)

For a more complete description of the arguments in PMT, see the PV function.

Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

Remarks
The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

[G Gabriel]

Re: Payment function for canada

Yes ..but that's American way, not the Canadian.

[DaveM]

Re: Payment function for canada

from Gabe

I suppose "compounding" is used here loosely to mean how often the interest is calculated. That's fine. The monthly payment on a mortgage (Canadian style) on a loan of $100,000 with semi-annual compounding rate of 10% annually amortized over 30 years with equal monthly payments would be: $862.67 . Calculated American style, would be: $877.57.

Actual is 877.571570088799 using the code i sent above.

'like this:::
 
mrate=10
mprinciple = 100000
years = 30
peryear = 12
t = years
n = peryear
N = n*t
r = mrate/100
R = r/n
P = mprinciple
dim a as n
dim b as n
R=(mrate/100)/peryear
a=P*R*((1+R)^N)
b=(1+R)^N-1
s = a/b
?s
= 877.571570088799

if the canadian way comes up to more/less, then there is a quirk in the method somewhere.

I come up with 877.28 canadian???

Hope it helps someone.

By the way??? How do you compute 45+- days to first?? Anybody know??

[G Gabriel]

Re: Payment function for canada

Dave:
Your calculation is correct..but it's American. I had the same result.

[DaveM]

Re: Payment function for canada

Yes you did Gabe, but what method did you use? You can use any payment calculator on the web to get that, but how is it really done??

I came up with a different canadian answer and not sure if it is right or not??

.

[BigBoss1]

Re: Payment function for canada

Hello,

I have noticed that the Payment and Payment2 functions built into alphafive are based on the American Calculation method (monthly compounding).

In canada the compounding is done a bit differently (semi-annually)
I have not found a way to apply that change in alphafive.

Has anyone created such a function for Canadian users, or is there a way to edit the existing function and adapt it to our needs ?

Thank you for all help

I have noticed this question and I think the answer has not been found.
The problem is very similar in Europe and you will find that in Canada the 'no of days' is different to the USA. One uses 360 days the other 365. Why I don't know but as GM Corporate Auditor travelling overseas auditing the European Plants I found this was why I could not reach figures I was shown. Alpha should let us know what the value 'no of days in the year' they use and the answer will be there. Remember the compound interest is using the 'no of days' to calculate the yearly interest.

[csda1]

Re: Payment function for canada

Hi David,

Alpha should let us know what the value 'no of days in the year' they use and the answer will be there. Remember the compound interest is using the 'no of days' to calculate the yearly interest.

The function I gave above returns the identical value that Alpha's function returns. They are not using any value for the number of days.

Use 360 days as the interest period, instead of weeks, months or years

[G Gabriel]

Re: Payment function for canada

Yes you did Gabe, but what method did you use? You can use any payment calculator on the web to get that, but how is it really done??

I haven't since I don't know what logic those Canadians use!

A professor of mathematics developed a formula that could be used for American, Canadian, French, German and other mortgages but I am not able to decipher some of the symbols. I am sure with some trial and error I might be able to figure it out but being short on time I took the easy way out and sent him an email. Hopefully I will get an answer.

The logic I would probably use would be something like this:
If the interest is only charged twice a year, and
If you make monthly payments, and
If you need these payments to be equal, then
The calculation you need to make is: What is the equivalent monthly interest to this semi-annual interest?

The American style function takes as an argument the interest charged. But as you know, to get the correct monthly note, you need to divide the annual interest by 12. It's the same in alpha and Excel. These functions do not provide for adjusting the rate when charged semi-annually. To change the term and interest (i.e. divide the rate by 2 instead of 12 and multiply the period by 2 instead of 12) then divide the output by 6 does not produce the correct answer which affirms my belief that the rate has to be adjusted to what it would have been if charged monthly.

The problem is very similar in Europe and you will find that in Canada the 'no of days' is different to the USA. One uses 360 days the other 365. Why I don't know but as GM Corporate Auditor travelling overseas auditing the European Plants I found this was why I could not reach figures I was shown.

In residential mortgages, typically it's 365. In commercial mortgages it's a bargaining point (supposedly) and most commercial notes are calculated based on 360 days. Don't know the exact reason(s) but perhaps because most those notes are tied to the 10 year Treasuries.

[DougMcLean]

Re: Payment function for canada

I found a site that offers a Canadian Mortgage caculator that can be used inr Excel. I've attached a copy of the file.



Inputs Balance at Term
Loan Amount $100,000 Date at Term 12/28/2036
Canadian Mortgage Rate 10.00% Interest Paid $210,557.11
Compound Period Semi-Annually Principal Paid $100,000.00
Amortization Period (in years) 30 Outstanding Balance ($0.00)
Term (in years) 30
First Payment Date 1/28/2007
Payment Frequency Monthly
.
Payment $862.67

[G Gabriel]

Re: Payment function for canada

OK.. I got it, found the logic and yes, it's the same as the one I described .. Now off to write a function.
Will get back with you soon.

[G Gabriel]

Re: Payment function for canada

And.. here is your function:

FUNCTION Pmt_Canada AS N (Loan_amt AS N,Periodic_interest_rate as N, Payment_periods AS N,Compound_Period AS N )
	mi=((1+Periodic_interest_rate/Compound_Period)^(Compound_Period/12))-1
	Pmt_Canada=payment(Loan_amt,mi,Payment_periods)
END FUNCTION

American:
?payment(100000,.1/12,360)
= 877.571570088799

Canadian:
?pmt_canada(100000,.1,360,2)
= 862.66798773271

[DougMcLean]

Re: Payment function for canada

What's the total amount paid over 360 payments?

Payment 862.66799 862.668 862.67
30yrs
310560.47558 310560.480 310561.20

excel 210557.11
100000.00
310557.11

[G Gabriel]

Re: Payment function for canada

Doug:
I am not following you!
What is with the Excel calculation?

[DougMcLean]

Re: Payment function for canada

My question is how much will be paid over the 30 years of the note? Using your formula and rounding off to two decimal points results in a different answer than if you round to three decimal points. Both of these solutions paid over 30 years seem to be different from the solution provided by the Canadian Excel worksheet. 862.67 * 360 = 310561.20 210557.11 + 100000 = 310557.11 What's the correct answer?

Inputs Balance at Term
Loan Amount $100,000 Date at Term 12/28/2036
Canadian Mortgage Rate 10.00% Interest Paid $210,557.11
Compound Period Semi-Annually Principal Paid $100,000.00
Amortization Period (in years) 30 Outstanding Balance ($0.00)
Term (in years) 30
First Payment Date 1/28/2007
Payment Frequency Monthly
.
Payment $862.67