# Mortgage amortization schedule



## Mandy101 (Dec 7, 2009)

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!


----------



## Jungle (Feb 17, 2010)

I can't fix your excell problem, but I can suggest this amortization schedule, it works really good:

http://www.mackenziefinancial.com/calc/jsp/MortgLoanAmortScheduler/mortgloanscheduler.jsp

You just have to remember your mortgage rate, starting amount and years it was amortized.


----------



## Mandy101 (Dec 7, 2009)

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?


----------



## steve41 (Apr 18, 2009)

The 'rate' can be expressed as compounding monthly, semi-annually or annually. Each will determine a different shedule.


----------



## Mattamatics (Feb 10, 2010)

*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.


----------



## MoneyGal (Apr 24, 2009)

What steve41 said; it has to do with how the interest rate compounding is calculated. 

This short piece will give you everything you need to know to set up a (correct, Canadian) mortgage table in Excel, including examples. 

http://www.yorku.ca/amarshal/mortgage.htm


----------



## Mandy101 (Dec 7, 2009)

Thankyou everyone - I will try finding both the t-value and the amarshal calculators!


----------



## Mandy101 (Dec 7, 2009)

Thanks Moneygal - amarshal's calculator worked immediately to get the same payment as my bank had given me.


----------



## Mandy101 (Dec 7, 2009)

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?


----------



## marina628 (Dec 14, 2010)

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.


----------



## Dana (Nov 17, 2009)

You can find an excel based spreadsheet here that is customisable for Canadians.


----------



## Mandy101 (Dec 7, 2009)

marina628 said:


> If you are going to pay the money towards the mortgage anyway you are better to take a lower amortization term./QUOTE]
> 
> Hi Marina
> 
> Do you mean I should take the longer amortization i.e. 25 years so that I have money left over to apply towards the principal at end of each year?


----------



## Dana (Nov 17, 2009)

Mandy101 said:


> 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?


----------



## George (Apr 3, 2009)

Mandy101 said:


> 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).


----------



## Mandy101 (Dec 7, 2009)

Hey George

Thanks for the tip! The calculators on this site are pretty neat. I plan to go through them to see how I can handle our mortgage a bit better.


----------



## Arlen (Jan 4, 2011)

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.


----------

