View Full Version : Excel loan templates

20-03-2007, 07:47 PM
Does anyone know a decent mortgage calculator that allows extra payments etc AND lets you make weekly calculations. All the ones I tried work by the month only. Cheers

20-03-2007, 09:32 PM
For web based try one of the banks link http://homebuyerscentre.nationalbank.co.nz/calculators/calculator.aspx?PostingID=42

But, you can do this in Excel yourself using the PMT function.

For example, a loan of $196,000 over 8 years at 7.99% is $638.02 per week

Formula broken down as follows...
** ABS... used to turn negative result into positive
** 7.99%/52 - because you are making weekly payments so divide annual interest rate by 52
** 8*52 - number of payments so thats 52 weeks x 8 years or 416 payments
** 196000 - amount of the loan


20-03-2007, 11:13 PM
Dividing your annual interest rate by 52 does not give you your weekly interest rate. This is the result of compound interest - the weekly interest rate will be smaller.

7.99% / 52 = 0.1537% per week by your formula

If you start the year with loan balance x then you will end the year with x + i*x where i is the yearly interest rate (e.g. 0.0799). This is the same as x * (1 + i). To get the weekly interest rate you must take into account compounding of interest. If w is the weekly interest rate then:

1 + i = (1 + w) * (1 + w) * (1 + w) * ... 52 times ... * (1 + w)

(1 + w) is the 52nd root of (1 + i)

w is:

((i + 1) ^ (1.0/52)) - 1

In this case our weekly interest is 0.1479% The difference is in this case relatively small (your weekly interest rate would give an annual interest rate of 8.3%) but it is important to note that you cannot safely ignore compound interest.

21-03-2007, 10:30 AM
Dividing your annual interest rate by 52 does not give you your weekly interest rate. This is the result of compound interest - the weekly interest rate will be smaller.

I don't mind people correcting me IF they are correct. Unfortunately you are not. If you bothered to look at the PMT function and the help thereof you will see the following text...

"Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper."

This function takes compound interest into account.

22-03-2007, 12:10 AM
I was worried for a moment that Excel must be doing particularly strange and unorthodox things there, because I cannot see any way in which it could possibly know about the units being used. You do not tell it anywhere that the 12 represents the number of months and the function is not even aware that a division is taking place. For all the program knows you could be having 11/12ths of the interest paid on your behalf. It is not possible for the function to take compound interest into account automatically. The function expects the number of periods and the interest per period.

Fortunately a quick experiment with an Excel spreadsheet has revealed the truth: the Microsoft documentation authors are simply telling everybody to calculate the interest per period incorrectly, whether out of a lack of understanding themselves or a desire to use a simpler approximation (This would be misleading at best).

I have whipped up a spreadsheet which calculates the effect of compound interest. It has two sheets. Each sheet runs a simulation of the balance of a loan over the year. The sheets each run a simulation of 52 payments. You give it an annual interest rate (in my sheet 7.9%) and it calculates the weekly rate using the documented method (getting 0.1537%) and with my formula (0.1479%). It then starts with an initial balance of $100.

The first sheet does not include any payments at all and simply shows the compounding of interest. Each week's balance is calculated by multiplying the previous week's balance by the interest rate and adding it to the previous week's balance to get a new one (as is correct for a weekly interest rate). This is repeated 52 times to get the balance at the end of each of the 52 weeks. Using my method the end result is $107.90 but using theirs it is $108.21. As you can see, my method led to an accumulation of 7.9% interest over the year. Theirs came out to 8.21% (I must've been a bit off on this front in my first post when I said 8.3%, sorry about the mistake). My method clearly matches the annual interest of 7.9%, while theirs does not.

The second sheet additionally makes a payment each month using the Pmt function. It is again done twice for their documented method and my (demonstrated in the first sheet to be correct) method. Both balances end the period with a balance of $0.00 as they are supposed to, demonstrating that there is no magical adjustment going on behind the scenes. Pmt expects the interest per period and Microsoft's method of getting it is simple but unfortunately wrong.

In this case the balances never differ by more than a few cents with a total of only 30c difference in payments over the year, but the difference will be greater if you increase the number of payments per year (e.g. daily payments) or measure interest over a larger period. You can still use the documented method if accuracy is not critical (i.e. if IRD aren't watching you) but you should at least be aware that it is only an approximation.

I have included a copy of my sheet at http://www.goddard.net.nz/non-site/Evidence%20of%20MS%20idiocy.xls. If anybody can find any mistakes in my work, please don't hesitate to point them out. If anybody can demonstrate that my method is incorrect, I will be more than happy to accept the correction as a valuable lesson. I would bet quite a lot on it being correct though :)

22-03-2007, 11:39 AM
Hi, I dont want to get into a big debate over this but I agree with you TGoddard that the way MS do their calculation is not 100% accurate and I admire your efforts to explain why its not. In USA they used to calculate loans based on a 360 day year (why, I dont know) but Im not sure if this is still the case. This may in some way impact how MS do it but I dont think so as I cant see it matching. :rolleyes:

I actually get a different figure than both you and MS. I used to work in the bank and so Im familiar with how interest is calculated - or at least how it was calculated 10 years ago :D.

Remaining Balance * ((Annual Interest Rate / 365) * # Days Interest)

or using your spreadsheet the first one would be =B1*(($B$2/365)* 7) which equates to interest of 0.151506 in week 1 and ends up at 0.163612 in week 52, presuming no payments are made and interest is compounding once per week - and I havent ballsed it up.

Im not sure whether banks still do it this way but table loans used to have interest compounded once per month, even if the actual repayments were weekly or fortnightly. So interest would accrue for the number of days in the month and be calculated for the first day to the last day of the month then added to the principal on the 1st day of the following month.

Usually for table loans it never works out exactly the same amount for the last repayment amount. Theres normally a residue so the last regular payment is slightly more/less than the other payments.

So to get a true value for a loan, which should equate to what a bank would tell you, I believe you would need to know these factors...
1) Amount of the loan
2) Annual interest rate
3) Start date of loan
4) End date of Loan
5) 1st date interest is compounded (merely to get a starting point and used in conjuction with the frequency)
6) Frequency interest is compounded (eg 1st of the month)

If I have time I could make a custom function that does exactly that but I would need to think about how it could be done without frying my brain.

24-03-2007, 02:24 PM
thank you all.......don't argue :)