MoneySavingExpert Chair, Martin Lewis · Editor, Marcus Herbert

# Daily interest calculation

Posts: 88 Forumite
Hi all,

I know you are all excel whizzes- does anyone have a formula to calculate daily interest paid on a mortgage?
My friend G00gle has confused the situation 😂😂
Thanks
x
Cleared 25k from mortgage in 4 years - now I need to do it again!

Dec 2020 Balance: £127,000
Dec 2021: Balance £121,500

## Comments

• Posts: 4,893 Forumite
You need to multiply your balance by your interest rate as a decimal. Using your balance of £127k as an example, if your interest rate were 1.5% you need to do:
127000 * 0.015 = £1,905 for annual interest
Then divide by 365 for daily interest or 12 for monthly interest
Mortgage start: £65,495 (March 2016)
Cleared 🧚‍♀️🧚‍♀️🧚‍♀️!!! In 5 years, 1 month and 29 days
Total amount repaid: £72,307.03. £1.10 repaid for every £1.00 borrowed

Finally earning interest instead of paying it!!!
• Posts: 88 Forumite
Thank you so much!!
Cleared 25k from mortgage in 4 years - now I need to do it again!

Dec 2020 Balance: £127,000
Dec 2021: Balance £121,500
• Posts: 46,882 Forumite
edited 23 January 2021 at 4:36PM
if you want a rough amount  balance*(rate/365) work for many lenders.
(edit to fix the  / to *)

The daily interest changes over time as the capital is paid off.
Read up a bit on amortization and then look at the financial excel functions.
here is the open office wiki
https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_Financial_functions

The main ones for mortgage spreadsheets are.
PMT  work out the payment
FV for calculating how much you owe at a point in the future.
CUMIPMT  calculate the interest for a period.

The functions assume an ordered year with equal months so don't track real mortgages exactly as the no of days in a month change and the payments dates change.

• Posts: 2,605 Forumite
I thought, I know the answer to this one.... @South_coast told me how.... but they have already posted!!
• Posts: 10,314 Forumite
edited 23 January 2021 at 4:17PM
You need to multiply your balance by your interest rate as a decimal. Using your balance of £127k as an example, if your interest rate were 1.5% you need to do:
127000 * 0.015 = £1,905 for annual interest
Then divide by 365 for daily interest or 12 for monthly interest
This will give you a rough estimate but will be most accurate at the start of the year and get less accurate as the year goes on.
To get a more (but not completely) accurate figure throughout the year, you need to repeat every month using the new reduced outstanding balance after your monthly payment has been made, remembering to add on the preceding month’s interest.

Nationwide give you an updated interest charged figure every day on their online banking/app, so you can see the daily figure by checking two days running and calculating the difference between the two figures. Other lenders may do the same.
2.22kWp Solar PV system installed Oct 2010, Fronius IG20 Inverter, south facing (-5 deg), 30 degree pitch, no shading
Everything will be alright in the end so, if it’s not yet alright, it means it’s not yet the end
MFW #4 OPs (offset): 2018 £866.89, 2019 £1322.33, 2020 £1337.07,
2021 £1250.00, 2022 £1500.00, 2023 £1500
Target for 2024 (offset) = £1200, YTD £345
Quidquid Latine dictum sit altum videtur
• Posts: 7,929 Forumite
I convert the annual interest rate e.g. 2% to a daily interest rate using the formula ((1+ 0.02)^(1/365.25))-1   (The .25 accounts for leap years)
so 2% pa = 0.0054218% per day

Now you calculate the interest due between two dates using the formula:
Capital * (1+0.000054218)^(no of days between the two dates)

The comments I post are my personal opinion. While I try to check everything is correct before posting, I can and do make mistakes, so always try to check official information sources before relying on my posts.

#### Categories

• All Categories
• 343K Banking & Borrowing
• 250K Reduce Debt & Boost Income
• 449.6K Spending & Discounts
• 235.1K Work, Benefits & Business
• 607.7K Mortgages, Homes & Bills
• 173K Life & Family
• 247.7K Travel & Transport
• 1.5M Hobbies & Leisure
• 15.9K Discuss & Feedback
• 15.1K Coronavirus Support Boards