hi guys
i used to be able to do this on a calculator but i’m struggling.

new mortgage interest rate is 1.94%, how do i calculate the approximate monthly cost, ie 12 payments at what percent (or even daily interest rate)

______
my old rate was 1.74% [1.0174] and i’d calculated an approximate monthly interest charge on the mortgage balance as x1.001438563
[ which balances / works to the year charge. 1.001438563 to power 12 = 1.0174 ]
—–
ps. its the calculation i’m interested in not the answer, if you know what i mean.
please help its doing my head in. looking to set up an excel spreadsheet, as i love excel :0)

Not all that easy. You can do get a rough estimate by just working out the new interest charge over the year (balance x 0.002 for the 0.2%) increase in interest rate, and dividing to give extra interest per month.

The capital repayment portion of your monthly bill will change a small amount, but it should give you a first guess.

The only way I know how would be a spread sheet to calculate a monthly balance and some healthy guess the answer trail and error.

What would I actually do? If the quick estimate isn’t good enough. Use the nationwide mortgage calculator, or try on money saving expert. They’re both very useable and will be as accurate an answer as the figure you feed in allow.

Edit: looks like £16.66 per month extra interest per £100k you owe

yeah i think my exapmle was done by trial and error.
theirs definately a simple way mathematically to do it, but google isnt helping and its along time since college

It is pretty complicated – I actually built a spreadsheet that lets you work it out based on all the variables (initial rate, on going rate, period, fees etc). If you send me an email I will send you the spreadsheet (which is really easy to use). It’s about 98% accurate.

I assume you are asking interest only ? To be accurate it does depend how they state the interest rate (annual from monthly compounded etc) and their calculation method (eg day count method). If its the difference between the two rates above and you want something approximate then it’s just 0.2% (or 0.002) times mortgage balance / 12

For the spreadsheet, I’d use a monthly equivalent interest rate as you mentioned, and look at the capital owed at the end of a month ( balance plus interest minus your payment)*

Roll that down to the starting balance for next month, and repeat for enough lines to cover 12 x your mortgage length. Then guess away at a monthly cost, see where the balance goes to zero.**

*Better ways exits. This is fast.
**Yes, this isn’t how they calculate it. I know.

But seriously, use the Nationwide calculator, it’s good

100k over 25 years, monthly repayment is £10 more going from 1.74% to 1.94%

Not sure if this will work but I’ve dropped the spreadsheet into a Dropbox folder so anyone can access it. There is a generic loan calculaotr and then two other sheets for mortgages, one using daily componding and one using monthly componding (it makes a difference).

Mortgage & Loan Calculator

thanks guys sockpuppet, i have the same spreadsheet :0).
and thanks jambalaya i’ll pm you.

and thanks jambalaya i’ll pm you.

You might have meant me and if you did the link above works. You should be able to reverse engineer the equation from the Excel formula.

appologies gt72, yep thank you

Principle “P” would be $100,000.
For “r,” you would use your monthly interest rate, which would be 0.06 (6 percent) divided by 12, or 0.005 (0.5 percent).
For “n” you would use your total number of payments, one for each month in fifteen years, which would be 12*15, or 180.

think i found it, just need to rework the equation to get monthly interest, lots of examples of compound interest online for savings, but few for loans/mortgages

Normal practice is to state the rate of interest as an annual rate, with the assumption that it is paid monthly in arrears. (So not sure why you used a 12th root calculation in your first formula, unless you were paying interest annually in arrears and wanted to calculate the compounding interest owed at some point in that year)

Since you are not compounding unpaid interest monthly into an annual settlement, the interest cost is the nth divisor of the annual rate where n is the payment period.

So if you pay a 12% annual interest in 12 monthly instalments p.a. then your interest cost is 12%/12 per month, or 1% per month of the outstanding balance at month end (so adjust for amortisation).

Compounding rates are straightforward enough, but not common in retail mortgages. Since you are not settling the interest charge at each period end, you roll the interest into the loan amount and charge interest on the new loan balance. To determine the interest owed any point in time you use the nth power of the periodic interest rate (usually annual). Do determine the effective periodic compounding rate (say monthly from annual) you use the nth root.

hi stoner, my mortgage charges a daily rate. hence i’m looking to calculate an aproximate monthly rate ..

excel sheet would be
starting balance, interest % ie 1.00141979 (to 8 dp), interest paid, less mortgage payment, less overpayment = closing balance (which is carried forward to the next month starting bal.)

i overwrite the interest charge each month (to balance it to the penny) and its always within a few % (longer 31 day or due on a weekend, shorter 28 day months).

as per example above dividing by 12 will get 1.01450000′ rather than the dp below
1.74% [1.0174] and i’d calculated an approximate monthly interest charge on the mortgage balance as x1.001438563

your mortgage may state a daily rate, but what is your payment period? Or is it daily and this is one of those offset mortgages with daily interest payment?

If offset, with a daily charge and payment, then you will be paying a 365th of the annual rate. If you are charged daily, but pay monthly then you will be paying a daily rate that compounds the 365th for 30 days.

its a daily charge interest rate tracker. hence if i do a lump sum, my interest £ paid will drop the next time i’m charged interest.

the simple method of annual interest / 12 is only 80 pence out on my balance so its probably a good enough approximate.

i just wish i had a better memory, to recall the equation.

the simple method of annual interest / 12

so you pay monthly?

yes stoner, i pay monthly, although i could pay daily,
i recall one of the smaller banks had miscalculated interest for x number of years, and refunded those involved a few years ago.

in theory my bank charges interest daily, but i dont get to see my balance changing daily, hence considering it on a monthly basis,

Its a wonder if these creaking old bank systems actually calculate the rate correctly, mortgages in the past (such as Abbey) would calculate full interest on the loan amount as at a set date yearly, whether you overpaid or not in that year. Far simpler, but weighted in the favour of the bank

Aha that is why your figure for the diary amount is out

Out and about, but will do a formula later for you

I created this years ago:

https://docs.google.com/spreadsheets/d/1cLH5NU8s4ghbiB7wlc_a1obAcjXdY59TZuW5jhStqjc/edit?usp=sharing

If you have Excel you can use the PMT function to work it out. As already stated, the actual maths behind it is a bit complicated.

Pmt is for amortising loans. Not interest only calculations

I endorse what Stoner says but its complicated by the detail and the approaches in practice differ by lender.

My back of the envelope calculation is a significant approximation but when you are asking for monthly payment difference between 1.94% and 1.74% its close enough. I worked on these sort of assumptions and accuracy vs speed at Uni and in my job fwiw

I personally would avoid creating a fancy spreadhseet unless you have read in detail how your specific lender does the calculation.

My spreadsheet works for daily calculations – matched my Nationwide payments.

I created it as nothing very useful out there at the time.

at my desk now so can do the formula for you.

As I alluded to above. It is when there is a difference between the period for which the interest is applied and the period between interest payments that you have to compound the interest.

So if you are given a “Daily rate” but pay interest monthly, you compound the daily rate for 30days. But the daily rate is still only 1/365 of the annual rate.

So, for 1.74% annual interest rate,

the daily rate is 1/365th = 0.0000476%
However, if you only pay the interest bill at the end of each month, you owe not only that 0.0000476% on the balance, but also on the accruing interest amount over that month.

So your monthly cost is (1+0.000476%)^30-1 = 0.145% per month.
And you’ll note that 12*0.1451% comes out at 1.74122% pa.
That little difference between the annual rate and the effective annual rate is the difference in your numbers.

So with the above, you should be able to do the calculation for your 1.94%

Thanks stoner appreciated, I’ll check the interest charging rules at HSBC, I’m gonna have part fix, part tracker, just to complicate matters, but looking at my app, they have created them as seperate sortcode/accounts nos.

I miss knowing this kind of maths, age and a bad memory don’t help

Do you figures match my spreadsheet? Kinda curious….

If i read your original post right you want to figure out what monthly % gives you a specific annual %?

Very simply and as (in)acurately as your previous calculation if that’s the case:

=[Newrate]^(1/12)