Payment function for canada

[gauvain65]

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

[DaveM]

Re: Payment function for canada

I have also noticed that the function does not accept 45 days to first payment with any way to get a correct figure.

i had to write my own funtion for payments. It was not easy and would not work for semianual(I think).


.

[gauvain65]

Re: Payment function for canada

Thank you for the quick reply Dave,

Is there a way to view the code of the function built into Alphafive, so that we have a starting point for writing our own function ?

Or maybe could we see the code of your function to help us out ?

Thanks again, any further advice would be appreciated

[Steve Andrews]

Re: Payment function for canada

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

Jean-Pierre,

It doesn't seem to me that Alpha 5 is necessarily based on monthly compounding. Suppose that you have a loan of $100,000 compounded semi-annually for 30 years at a rate of 1.5% every 6-month period (3% per year). Then we have:

?payment(100000,.015,60)
= 2539.342743

So the semi-annual payment of $2,539.34 would be needed. If you want monthly payments (but still compounded semi-annually), simply divide this by six for six equal installments of $423.22.

My 2 cents.

Steve

[DaveM]

Re: Payment function for canada

I don't know what their code is, but here is what I have that will give a payment based on 30 to first that conforms to regZ.

 
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
pmtonapr = a/b

[DougMcLean]

Re: Payment function for canada

It been some time since I've attended an accounting course but my old HP 12 c gives a semiannual amount of $250.20. Total over 60 paymentrs $15,011.81.
In Alpha

?payment2(10000,.015,60)
= 253.934274271092

The interest rate varies for the number of payments per year. If the interst is 3% per year the semiannual compounted interest rate is 1.5%. If payment is monthly the interst rate is 0.25%. At least that's how I recall it works.

[G Gabriel]

Re: Payment function for canada

Jean Pierre:
I think you are confusing payment() with FV().

In payment() or any amortization function there is no compounding.
What exactly are you trying to do?

[gauvain65]

Re: Payment function for canada

Thank you for all replies,

Sorry if I am not very clear...(not an accountant :))

what we try to do is create amortization tables for mortgages, some of them being US, and some being Canadian. Since the calculation method is slightly different between the two countries we need to have functions for each scenario.

The aplhafive built in PAYMENT function works for US based mortgages, but not for Canadian based.

So we now try to figure out how to build that specific function.

The problems are:

1) calculation of monthly payment is different
2) calculation of interest is different
we need to reslove both of them

Hopes this helps clarify things a bit

[Steve Andrews]

Re: Payment function for canada

Doug,

The difference between what Alpha provides and what you saw in your accounting class is the difference between an "annuity due" and an "annuity immediate". The $250.20 figure from your class assumes that the payment is made at the beginning of each month (immediate).

On the other hand, the Alpha functions assume that you pay the payment at the end of each month (due). I think there are ways to use the Alpha functions for an immediate annuity with some adjustments (e.g. add the would be interest to the initial balance and add one to the number of payments). But I haven't yet determined what that would be.

Regardless, Alpha should be more descriptive in their help file on the assumptions. Likewise, it would be helpful if they had more parameters to allow for the variations in assumptions (e.g. immediate vs. due; APR rate vs. effective interest rate; etc.).

Gabe:

In payment() or any amortization function there is no compounding.

There IS compounding within the payment() function. It assumes that interest is applied every "period". That means compounding every period. Hence, in the example I posted in my previous post, by using 60 periods (2 periods every year), I got the results for compounding semi-annually.

Steve

[Steve Andrews]

Re: Payment function for canada

The aplhafive built in PAYMENT function works for US based mortgages, but not for Canadian based.

As I've mentioned above, the Alpha functions have nothing to do with a US standard. They are simply compund interest functions which only are bound to laws of mathematics.

If you want semi-annual compounding, make the number of periods twice the number of years. Also, make the interest rate to be whatever rate applies every six months. Then you get from the payment() function what the semi-annual payment would be. If you are paying monthly, divide that SA payment by six (no compounding in the intervening months).

Steve

[DougMcLean]

Re: Payment function for canada

You need to be very careful here. If you are going to be determining mortgage payments for a bank or lending institution you need to work with their accountants and use their formulas and service fees. Watch out for rounding errors. Several years ago I had a mortgage with a bank. The bank was accused of overcharging interest. At the end of the year the bank rounded accounts to the nearest penny. Over several years this error compounted. The bank ended up in a class action suit. A law firm walked away with hundreds of thousands and I and other injured parties received nothing, not even the penny or two they may had overcharged us.

[DougMcLean]

Re: Payment function for canada

Steve,
You said it better than I. My orginal formula was for a loan of $10,000 you used $100,00. What I find interesting is Alpha gives us an answer of :

?payment(100000,.015,60)
= 2539.34274271092

HP 12 C gives an answer of:
2501.83079

Is either of these solutions correct?

[G Gabriel]

Re: Payment function for canada

Steve:
Alpha's built in payment function evidently presumes payment will be made at the end, not the beginning, of the period and does not give you an option for the latter.

Perhaps it's just semantic, but compounding i.e. paying interest on interest, is not part of amortization. In an amortization function, the interest is calculated based on the remaining balance at the end of the preceding period after principal is deducted. There is no interest charged nor paid against interest.

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.

[DougMcLean]

Re: Payment function for canada

For what it is worth this is the answer in excel:
(2,539.342743) This answer agrees with Alpha.

Excel help file explains how to use the formula.

[G Gabriel]

Re: Payment function for canada

Which version of Excel do yo have?
The one I have does not have Canadian calculation.