Canadian Money Forum banner
1 - 16 of 16 Posts

·
Registered
Joined
·
44 Posts
Discussion Starter · #1 ·
I googled how to setup a mortgage amortization schedule in excel.

I wanted to replicate the mortgage schedule that my bank gave us when we entered into the mortgage based on the following facts:

Loan: $413,000
Rate: 5.79%
Amort period: 25 years
Locked in: 5 years
Start date: July 16, 2007
Monthly payment per bank:$2,591

I've tried a variety of excel calculators online but am getting to a different amount for monthly payment (ie $2,608.20, $2,604.49 etc).

Could anyone tell me what the problem might be?

Thanks guys!
 

·
Banned
Joined
·
16 Posts
Semi-annual

Legislation defines Canadian interest calculation. -The Act states that interest can only be compounded twice annually and that all payments must be in arrears. term used is: Semi Annually and not in advance.

This impacts the total interest calculation significantly over the term of a mortgage.

If you know any Accountants or Finance people you could ask them if they have a program. -I know we use a program called T-value.

Or when you search for excel make sure it's semi-annual, and if you want to be really accurate remember that depending on the close date & first payment date -your first payment may have been for a different amount (a lump sum of interest) I know that was the case for my mortgage.
 

·
Registered
Joined
·
44 Posts
Discussion Starter · #9 ·
I have another question.

For a rental property - renewal of mortgage - is it better to get a longer term (ie 25 year amortization), therefore smaller monthly payments and apply the savings (ie rent coming in - mortgage pmt) at end of year towards the principal (ie lump sum payments) or is it better to get a shorter term (ie 20 year amortization) and not have anything left over at end of year to apply towards principal?
 

·
Registered
Joined
·
3,647 Posts
If you are going to pay the money towards the mortgage anyway you are better to take a lower amortization term.We do rapid by weekly on our investment property and try to have expenses close to the income amount.We are cash positive about $4000 a year after our deductible expenses come out.But we are saving this cash to do future renovation instead of paying on mortgage.
 

·
Registered
Joined
·
420 Posts
I have another question.

For a rental property - renewal of mortgage - is it better to get a longer term (ie 25 year amortization), therefore smaller monthly payments and apply the savings (ie rent coming in - mortgage pmt) at end of year towards the principal (ie lump sum payments) or is it better to get a shorter term (ie 20 year amortization) and not have anything left over at end of year to apply towards principal?
This is an investment, not your home, so your goals for the mortgage are probably different.

Since the interest paid on the mortgage is deductible against the rental income, it makes sense to take the longest amortization you can and maximize your interest deduction. If the property cash flows, you can decide annually whether to use any excess rental income against the mortgage.

It also makes sense to take the longer amortization from a cash flow perspective. If you have a vacancy that lasts a few months, do you want be out of pocket for the large payments associated with a shorter am or the smaller payments associated with the longer am?
 

·
Registered
Joined
·
131 Posts
Thanks Jungle! If anyone could reccommend an excel calculator? I just want to know what I'm doing wrong in such a simple calculation. How is the bank getting to their amount?
The Canadian mortgage calculator from www.vertex42.com works really well for me. The amortization calculation was pretty much bang-on with what the bank calculated (within a dollar or so, which I'd attribute to rounding differences), and it allows you to instantly see the impact of extra payments, increased payments, etc.

It's the main tool we've been using to track our mortgage payoff, and if we stay on target we'll have the house paid for, in full, in a total of nine years in 2013 (we're just starting year seven).
 

·
Banned
Joined
·
2 Posts
I completely agree with your answer and after searching several hours i have come to the point that you have explained and i find this forum very helpful.I think that these forums provide us excellent opportunity to share our knowledge and help each other.
 
1 - 16 of 16 Posts
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top