Canadian Mortgage Calulator - Interest Savings

[pacino]

Hello,

I'm attempting to build a Mortgage payment calculator which calculates interest semi-annually. The goal of the calculator is to determine how much interest you would save if your payments were set to Accelerated Bi-weekly, and Accerlerated Weekly vs. Monthly.

I've already calculated what the payments would be for each payment type, however I'm not able to determine what the interest savings would be.

Any suggestions would be greatly appreciated.

Thank you.

 

[Dave Patton]

Can you post an example of your sheet, formulas, and expected results?

 

[pacino]

Hi Dave,

http://www.filefactory.com/file/af8c41g/n/mortcalc_xls

I've uploaded my excel file above.

One the first sheet, you will find 2 calculators : how much can i afford and payment calculotor.

This question relates to the payment calculator. As you will notice, I able to determine the payments for monthly, acc-biweekly etc. My goal now is to determine what the interest savings would be if you changed from monthly to acc-biweekly etc.. I started to build a amortization table,
but I'm hoping there a solution to this without using the table.

thank you for your help.

 

[Dave Patton]

The clearer your information; the clearer the ideas, tips, or even possible solutions that may be suggested.

It will be useful if you provide:
- small example of your data
- examples of your formulas
- clear explanation of what you are calculating
- version of Excel and other pertinent information

You can prepare a very concise example that illustrates what you are doing, what you require, and expected results.
What you have tried and if something didn't work or you received an error message, please provide particulars.

It may not be necessary but the following is one way to put detailed information in a message.
Post a screenshot of your sheet(s) with Excel Jeanie HTML 4. http://www.excel-jeanie-html.de/html/hlp_schnell_en.php. See messages at the top of the list of messages.

Most problems can be explained with a clear description and/or small screen shots. Many (most) people will not download a file.

 

[pacino]

Thank you.

Here is the problem.

Mortgage of $250,000, Interest of 4.5%, Amortization of 25yrs

What I'm trying to do is calculate the total interest paid if your payments are monthly, acc-weekly, acc-bi weekly etc.

I have figured out the total interest paid if on monthly payments by doing the following.

=cumipmt(A1,300,250000,1,300,0)

A1 = (12 * ( ( (1+0.045/2)^(1/12/2) )-1) ) /12

This gives me $165,104.72 (which is correct according to major banks)

I can't figure out how to get the =cumipmt to work for accelerated bi-weekly.

I hope someone can help.

Dave - thank you for your advice.

 

[pacino]

Please note, I've made a correction to the above. My exponent needed an extra set of brackets.

A1 = (12 * ( ( (1+0.045/2)^(1/(12/2)) )-1) ) /12

 

[Dave Patton]

Note
=Pymt*Term*12-Prin = total interest

Pymt amt * number of payments = gross amount minus principal = interest

If one makes greater payments, the number of payments will be fewer and multiplied
by the payment gives the gross payment.

The difference from the original calculation is the decrease in interest payments.

=NPER(Cdn_Rate,-Pymt,Prin)*Pymt-NPER(Cdn_Rate,-(Pymt+31.54),Prin)*(Pymt+31.54)

1. Calculate total payments with method one
2. Caculate total payments with method two (> payments, accelerated or __
3. Calculate difference

 

[pacino]

Thanks Dave,

=NPER(Cdn_Rate,-Pymt,Prin)*Pymt-NPER(Cdn_Rate,-(Pymt+31.54),Prin)*(Pymt+31.54)

what exactly do i do with this formula and where is teh 31.54 coming from?

 

[Dave Patton]

I just illlustrated an example with extra payments of 31.54.

1. What are total payments with method one?
2. What are total payments with method two?
3. What is difference?

 

[pacino]

I've tried the following,

Same parameters as above, my Acc-Biweekly payment is $691.

Using your formula, =$691.84*25*26-$250000

I end up with $199,696.78. (This is interest)

From this point, how can I determine the Interest savings vs. Monthly payment?