mortgage with 3-monthly payments

Excel would be pretty good at that ! Although you would have to be handy with Excel !

There is an Excel Loan template which can do most (if not all you want). It's a bit clunky.

IIRC, mine came with Excel v5 (?), but it seems to have been dropped from later versions. Office 2000 does not have it. It's a 75kb zip file.E-mail me, if you want a copy.

I have a Mortgage/Loan calculator which has 9 options for frequency of payment. I think it cost me about US$10 a few years ago (although updates are free). Just checked the site, and it's now US$29.95.

You can use a monthly-based calculator. Just tell it the yearly interest rate is 3 times more than it really is, and the term is 1/3 as long. Then, when out pops the answer purporting to be the monthly payment, that will actually be your quarterly payment.

Or why not just use the general formula yourself:

P/A = r / (1 - (1+r)^-n)

P/A is the periodic payment divided by the initial loan amount r is the periodic interest rate (at 6% per year or 1.5% per qtr, r=0.015) n is the number of periods (for 25 years of quarters, n0) The up-arrow sign stands for "to the power" and yes it is followed by

So for a £100k loan at 6% per year over 25 years, we'd get:

P/A = 0.015 / (1 - 1.015^-100) The answer comes out as 0.0193706, which means that for A=£100k, the quarterly payment P would be £1937.06.

Doing it the other way, using a "monthly" calculator:

r = 1.5% as above (You tell it the interest rate is 18% per year and it converts that to 1.5% per month) n = 100 (You tell it (25/3) years and it converts that to 100 months

Surprise, surprise, the answer comes out the same because r and n are the same. Good, eh?

Easy. Just solve the above equation for n:

n = -log(1-r/(P/A))/log(1+r)

So, to test, let's plug in the payment we've worked out above: P/A = 0.0193706, r = 0.015, so we should get n0:

n = -log(1-0.015/0.0193706)/log(1.015) = -log(0.22563)/log(1.015) = -(-0.64660/0.0064660) = 100

You can work with the actual current loan balance instead of the original borrowing, which is useful if you've made substantial overpayments, and then provided the periodic payments remain P, you will use a different (bigger) value for P/A because A is now smaller. Then the n you get will be the number of periods still to go from now instead of from the beginning.

Well, if in addition to the quarterly payments you make unscheduled irregular payments, then you'd simply need to calculate, for each quarter, the closing balance. This will in simple cases equal the opening balance times (1+r) minus the scheduled paymnent. Of course the closing balance for one period becomes the opening balance for the next. If you make an unscheduled payment of X during the period, then:

Closing balance = (opening balance)*(1+r) - X - P

unless the rules of the loan provide for daily balances to be taken into account. In that case, if in a 91-day quarter, you make the X payment D days after the beginning of the quarter, the effect is that the X payment will "earn" interest for 91-D days:

CB = OB*(1+r) - X*(1+r*(91-D)/91) - P

Notice how this means that if you make the X payment at the end of the period, i.e. D‘, this becomes equivalent to the simple case method, because it treats the payment as being made at the end, together with P, irrespective of when X was actually paid:

CB = OB*(1+r) - X*(1+r*((91-91)/91=0)=1) - P = OB*(1+r) - X - P

If you pay at the beginning, i.e. D=0, it becomes:

CB = OB*(1+r) - X*(1+r*((91-0)/91=1)=(1+r)) = (OB-X)*(1+r) - P

Install the analysis toolpack in Excel, and you get some formulae to do it for you.

Look for things like

PMT(rate, nper, pv, fv) Gives you the periodic payment on a loan where "rate" is the rate per period "nper" is the number of periods "pv" is the present value of the loan, probably the amount you borrowed "fv" is the amount remaining due at the end of the loan, probably £0

IPMT(rate, per, nper, pv, fv) Gives you the interest charged for the period "per" where the other arguments are as above.

Hmm... I have to go through that all in time.

Well, yes and no.

Yes, because if you pay 3 grand for 100 months that's the same total as

No, because you can't directly compare the two, because if you're borrowing the same capital for the same total time at the same nominal yearly interest rate, then if it should work out to 1 grand a month, the quarterly figure would *not* be 3 grand, it'd be slightly more.

Work it out: 25 years at 6%:

Monthly: P/A = 0.005 / (1 - 1.005^-300) = 0.006443 That would be 1000$ if you had borrowed 155,207$.

Quarterly: P/A = 0.015 / (1 - 1.015^-100) = 0.019371 On 155,207$ that would be 3006.45$ per quarter.

In the monthly case, you'd be paying 1000$ for 300 months, a total of

In the quarterly case you're paying 3006.45$ for 100 quarters, which is total interest of 145,438$.

That's 645$ more, which we needn't have worked out by subtracting as above, but could be directly observed from the fact that it's costing you 6.45$ more per quarter.

Whether 645$ counts as "considerable" is for you to judge. It is nearly half a percent of the total borrowed, or of the total interest payable. I'd say significant, yes, considerable, perhaps not.