Welcome!

By registering with us, you'll be able to discuss, share and private message with other members of our community.

SignUp Now!

Mortgage Calculations

FayWong

0
Registered
Joined
Aug 30, 2007
Messages
135
Hi, I hope someone out there can tell me. How are Canadian mortgages calculated? In Excel I found a template for mortgage calculations, but it is American and the figures are just a little out. I sure would like to find the formula for Canadian mortgages to use in my spread sheet. I have a mortgage calculator but using a calculator then manually inputting into the spread sheet is an extra step i would like to eliminate. Thanks
 

GarthChapman

0
Registered
Joined
Aug 30, 2007
Messages
1,821
QUOTE (FayWong @ Sep 10 2007, 03:51 PM) Hi, I hope someone out there can tell me. How are Canadian mortgages calculated? In Excel I found a template for mortgage calculations, but it is American and the figures are just a little out. I sure would like to find the formula for Canadian mortgages to use in my spread sheet. I have a mortgage calculator but using a calculator then manually inputting into the spread sheet is an extra step i would like to eliminate. Thanks

Hi Fay,
I have found a number of them but they all seem to be out a bit after you get past 10+ years. Suggest you use the Mortgage Calculator in REMA. It is bang on, allows US or Canadian calculations, and has several other features including interest only calculations, graphing and amortization table that you can print, pdf and email.
Regards,
Garth
 

BMironov

0
Registered
Joined
Aug 29, 2007
Messages
597
Hi Fay,

I use the following formula in Excel:

=-PMT((B9/2+1)^(2/12)-1;B8*12;B12;0)
where
B8 - years of amortization
B9 - annual interest rate
B12 - amount

It makes calculation for "semi-annual compound payments" pretty close to what banks present in schedule.
 

FayWong

0
Registered
Joined
Aug 30, 2007
Messages
135
QUOTE (BMironov @ Sep 12 2007, 09:26 PM) Hi Fay,

I use the following formula in Excel:

=-PMT((B9/2+1)^(2/12)-1;B8*12;B12;0)
where
B8 - years of amortization
B9 - annual interest rate
B12 - amount

It makes calculation for "semi-annual compound payments" pretty close to what banks present in schedule. Thanks. this is just what I needed. I know there are great mortgage calulators on the web and I have my own hand held calculator but I wanted it right in the spread sheet so I didn`t have to stop and go to a seperate calculator. this will work. Again. Thanks
 

pvilay

0
Registered
Joined
Sep 14, 2007
Messages
40
For anyone else, Randy Jamieson from the Mortgage Centre emailed an excel mortgage calculator to me.
Please msg me if you want me to email it to you.
 

lewatson

0
Registered
Joined
Aug 30, 2007
Messages
49
QUOTE (BMironov @ Sep 12 2007, 09:26 PM) Hi Fay,

I use the following formula in Excel:

=-PMT((B9/2+1)^(2/12)-1;B8*12;B12;0)
where
B8 - years of amortization
B9 - annual interest rate
B12 - amount

It makes calculation for "semi-annual compound payments" pretty close to what banks present in schedule.

I just added this to my spreadsheet and it works well. Only one calculation was off by $.02 in comparison with the mortgage calculations done with the calculator on www.peterkinch.com.

I did have to change the ";"s in the formula to ","s (semi-colons to commas) in order for the forumla to work.

Thanks!
 

BMironov

0
Registered
Joined
Aug 29, 2007
Messages
597
Hello lewatson,

QUOTE (lewatson @ Sep 24 2007, 05:14 PM) I just added this to my spreadsheet and it works well. Only one calculation was off by $.02 in comparison with the mortgage calculations done with the calculator on www.peterkinch.com.

I did have to change the ";"s in the formula to ","s (semi-colons to commas) in order for the forumla to work.

Thanks!

Thanks a lot for this correction. I use OpenOffice (free office software available at http://openoffice.org) and it uses semi-colons instead of commas (as Microsoft Excel does).

Another free office option could be Google Docs http://docs.google.com

Thanks again,
Boris
 

MikeMcCrae

0
Registered
Joined
Sep 3, 2007
Messages
489
For most things i use a calculator I got from Realdata.com

QUOTE (FayWong @ Sep 10 2007, 03:51 PM) Hi, I hope someone out there can tell me. How are Canadian mortgages calculated? In Excel I found a template for mortgage calculations, but it is American and the figures are just a little out. I sure would like to find the formula for Canadian mortgages to use in my spread sheet. I have a mortgage calculator but using a calculator then manually inputting into the spread sheet is an extra step i would like to eliminate. Thanks
 

Anonymous

0
Registered
Joined
Dec 16, 2008
Messages
1,005
QUOTE (FayWong @ Sep 10 2007, 05:51 PM) Hi, I hope someone out there can tell me. How are Canadian mortgages calculated? In Excel I found a template for mortgage calculations, but it is American and the figures are just a little out. I sure would like to find the formula for Canadian mortgages to use in my spread sheet. I have a mortgage calculator but using a calculator then manually inputting into the spread sheet is an extra step i would like to eliminate. Thanks

we use the same formula as Boris`s for initial analysis but have now moved over to REMA... I would also make sure you have a section for the adjustments caused by the actual adjustments at closing.

on .02 I wouldn`t wory...
 

FayWong

0
Registered
Joined
Aug 30, 2007
Messages
135
QUOTE (MarkGarrett @ Sep 24 2007, 09:39 PM) we use the same formula as Boris`s for initial analysis but have now moved over to REMA... I would also make sure you have a section for the adjustments caused by the actual adjustments at closing.

on .02 I wouldn`t wory...all of you have been very helpful. The calculation fits into my spead sheet and works well. I also downloaded REMA, but haven`t had time to actually use it yet. I hope to start transferring data soon.Thanks again everyone
 

KimFranz

0
REIN Member
Joined
Sep 21, 2007
Messages
155
QUOTE (FayWong @ Sep 10 2007, 02:51 PM) Hi, I hope someone out there can tell me. How are Canadian mortgages calculated? In Excel I found a template for mortgage calculations, but it is American and the figures are just a little out. I sure would like to find the formula for Canadian mortgages to use in my spread sheet. I have a mortgage calculator but using a calculator then manually inputting into the spread sheet is an extra step i would like to eliminate. Thanks


Hi Fay,

This web page is amazing!!! http://www.albertacreditunions.com/public/...sp?cid=44-46-77

I use it all the time, it is to the penny to what the banks are doing - and it is Alberta based so you know that it is good.

Kim Franz
 

Anonymous

0
Registered
Joined
Dec 16, 2008
Messages
1,005
QUOTE (BMironov @ Sep 13 2007, 12:26 AM) Hi Fay,

I use the following formula in Excel:

=-PMT((B9/2+1)^(2/12)-1;B8*12;B12;0)
where
B8 - years of amortization
B9 - annual interest rate
B12 - amount

It makes calculation for "semi-annual compound payments" pretty close to what banks present in schedule.

Does anyone have the formula for calculating the principal paydown element vs. having to create an amm schedule?
 

BMironov

0
Registered
Joined
Aug 29, 2007
Messages
597
QUOTE (MarkGarrett @ Sep 29 2007, 05:31 PM) Does anyone have the formula for calculating the principal paydown element vs. having to create an amm schedule?
Sure! Here it comes:

=CUMPRINC((B9/2+1)^(2/12)-1;B8*12;B12;1;5*12;0)
where
B8 - years of amortization
B9 - annual interest rate
B12 - amount
parameter #3 (=1) is first period of payment
parameter #4 (=5*12=60) is last period of payment (end of 5 years = 5*12 = 60)
parameter #5 (=0) is to use calculations based on the end of period

To activate this function in Excel use menu: Tools -> Add-ins
and activate "Analysis ToolPack"

Note
calculations use monthly payment schedule. If you want to pay weekly or bi-weekly use different values instead of 12. For example, for weekly paments:
parameter 2: B8*52
parameter 5: 5*52

P.S. This formula is for OpenOffice. In MS Excel use comma instead of semi-colon.
 

BMironov

0
Registered
Joined
Aug 29, 2007
Messages
597
Hello,

To simplify calculations here are 2 more functions:

CUMIPMT - to calculate cumulative interest to be paid between 2 periods
IPMT - compound interest payment on principal
 

FayWong

0
Registered
Joined
Aug 30, 2007
Messages
135
QUOTE (BMironov @ Sep 29 2007, 07:29 PM) Hello,

To simplify calculations here are 2 more functions:

CUMIPMT - to calculate cumulative interest to be paid between 2 periods
IPMT - compound interest payment on principalI am printing your first set of calculations to keep as a reference until I get a chance to update my spread sheet. Now with these last 2, I don`t understand how they are used. Where do they fit in a formula and what information will they give you. I have no accounting back ground so please make the explanation very simple. Thanks very much. Also I make a point of reading your reasearch articles almost everyday, I am glad they set up a new thread for them.
 

ekisielewski

0
Registered
Joined
Aug 29, 2007
Messages
128
QUOTE (pvilay @ Sep 14 2007, 11:56 AM) For anyone else, Randy Jamieson from the Mortgage Centre emailed an excel mortgage calculator to me.
Please msg me if you want me to email it to you.


Hi Paul: I would love to have this calculator if you don`t mind.
Thanks
Elisabet K.
Oakville
 

BMironov

0
Registered
Joined
Aug 29, 2007
Messages
597
QUOTE (FayWong @ Oct 1 2007, 08:32 AM) I am printing your first set of calculations to keep as a reference until I get a chance to update my spread sheet. Now with these last 2, I don`t understand how they are used. Where do they fit in a formula and what information will they give you. I have no accounting back ground so please make the explanation very simple.Hello Fay,
The idea behind these few functions is very simple. It is very common for many of us to create Excel spreadsheets to analyze properties from financial point of view. As an investor you want to know what to expect from property in nearest future after adding it to your portfolio. Will it cash flow? What about appreciation and possible refinancing in few years after...

Usual scenario to answer such questions will be to stablish multi-page spreadsheet that will have on first page some general numbers: property value, amount of mortgage, interest rate of the mortgage, expenses (eg, insurance, property management, property taxes, mortgage payments, ...)

To fill this page you need just basic numbers. Here you can use online mortgage calculators to get values for payments based on mortgage parameters (amount, years of amortization, interest rate, payment frequency). It is fine to use online calculators if you make such table once in "a long" while. But when you want to use the power of spreadsheet for number of "what-if" scenarios (eg, what if I will put 25% down?, what if I will put just 20% down? What if I will take 35 years amortization? What if it will be 40? What if I will pay weekly? What if ...?) Doing recalculations online will take more time than just changing the number in spreadsheet and getting answer in a split of a second.

As Piter Kinch say: "Are you with me?"

If so, then we know why we need Excel to do calculations for us. Here we see the need for our 1st function:
QUOTE PMT
- regular payment. Returns periodic payment of an annuity, based on regular payments and a fixed periodic interest rate.

This is our biggest helper. Thanks to this function we can calculate amount of our mortgage payment based on 3 numbers:
- Interest rate
- Number of periods
- Present Value (PV) of loan = Mortgage amount

Please note, that for semi-annual compound interest rates (that are common for Canadian mortgages) you have to use formula. Let`s say that value in cell B9 is 6% then formula will look like:

(B9/2+1)^(2/12)-1


Now all we need to do to calculate mortgage payment is to enter the formula:

QUOTE =-PMT((B9/2+1)^(2/12)-1,B8*12,B12,0)
where
B8 - years of amortization
B9 - annual interest rate
B12 - amount

Last parameter in formula is always "zero". It means that payment is due at the end of the period.

"Are you with me?"
style_emoticons


Now we can make our table better and calculate more numbers. Let`s say we want to know how much interest we pay with each mortgage payment. Here we can use another function:

QUOTE IPMT
- Compounded interest. Calculates the interest payment on the principal for an investment with regular payments and a constant interest rate for a given periodIPMT(($B$9/2+1)^(2/12)-1,1,$B$8*12,B12) whereB8 - years of amortization
B9 - annual interest rate
B12 - amount
Please note parameter #2. It is the number of period that we want to know about. In this case it is our very first mortgage payment.

If you will make substract value of the function IPMT from PMT then you can calculate mortgage principal reduction. To cimplify such task there is another function:

QUOTE PPMT
- Repayment. Calculates the repayment amount for a period for an investment whereby the payments are at regular intervals and the interest rate is constant

PPMT uses exactly the same parameters as IPMT. PPMT = PMT - IPMT

"Are you with me?"

Now we are ready for the bigger game. Now we can create payment schedule for the life of mortgage. Usually it is separate page in spreadsheet. It can consist of 9 columns:[list type=decimal][*]date of payment[*]order of payment (1, 2, 3, ...)[*]payment[*]interest in period (IPMT)[*]principal paydown (c-d)mortgage balance by the end of this period ($B$3-c+d)cumulative principal reduction ($B$3-f)cumulative interesttotal amount paid[/list type=decimal]...

But wait. If the only purpose of such table is to calculate mortgage balance by the end of 5 years you can simply use one function

QUOTE CUMPRINC
- Cumulative capital. Calculates the total amount of the repayment share in a period for an investment with constant interest rate

=CUMPRINC((B9/2+1)^(2/12)-1,B8*12,B12,1,5*12,0)
where
B8 - years of amortization
B9 - annual interest rate
B12 - amount
parameter #3 (=1) is first period of payment
parameter #4 (=5*12=60) is last period of payment (end of 5 years = 5*12 = 60)
parameter #5 (=0) is to use calculations based on the end of period

To activate this function in Excel use menu: Tools -> Add-ins
and activate "Analysis ToolPack"

Again, there is another function that helps with calculation of cumulative interest paid:

QUOTE CUMIPMT
- Cumulative compounded interest. Calculates the total amount of the interest share in a period for an investment with a constant interest rate.

It uses the same parameter as function CUMPRINC.

CUMIPMT = "Mortgage amount" - CUMPRINC

I see the need for separate page with mortgage payment schedule for cases when something changes during the term: interest rate for variable mortgage, payment amount, ...

And last but not least function:
QUOTE RRI
- Interest. Calculates the interest rate which represents the rate of return from an investment

=RRI(p, PV, FV)
where
p - number of periods
PV - present value
FV - future value

This function helps you calculare annual compound return on investment. If you analyzing $40,000 investment into the property for 5 years and expect to get back $80,000 then use:
=RRI(5,40000,80000)

tyle="vertical-align:middle" emoid=";)" border="0" alt="wink.gif" /> It is 14.87% compounded or 20% non-compounded [(80000-40000)/40000 / 5 * 100 = 20%]


P.S. It a pleasure to help with delivery of news to all investors into the "Economics discussion" forums.
 

FayWong

0
Registered
Joined
Aug 30, 2007
Messages
135
Wow. Thank you very much for both the formula and the calculations. The "Options" at the start of this topic has proven to be very useful. I was able to download a printable version of your explanatiion into `Word` to keep as a reference.
 
Top Bottom