# Canadian Mortgage Calulator - Interest Savings

#### pacino

##### New Member
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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### Dave Patton

##### Well-known Member
Can you post an example of your sheet, formulas, and expected results?

#### pacino

##### New Member
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.

#### Dave Patton

##### Well-known Member
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
- 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

##### New Member
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.

#### pacino

##### New Member
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

##### Well-known Member
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

Last edited:

#### pacino

##### New Member
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

##### Well-known Member
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

##### New Member
I've tried the following,

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

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

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

Replies
1
Views
98
Replies
0
Views
228
Replies
1
Views
145
Replies
1
Views
1K
Replies
4
Views
451

1,190,657
Messages
5,982,137
Members
439,757
Latest member
85Sarah2005

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back