Daily interest calculation

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

  • South_coast
    South_coast Posts: 4,893 Forumite
    First Anniversary First Post Name Dropper Photogenic
    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!!!
  • Gem2889
    Gem2889 Posts: 88 Forumite
    First Anniversary First Post Name Dropper
    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
  • getmore4less
    getmore4less Posts: 46,882 Forumite
    Name Dropper First Anniversary First Post I've helped Parliament
    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.





  • caeler
    caeler Posts: 2,605 Forumite
    Mortgage-free Glee! Photogenic Name Dropper First Post
    I thought, I know the answer to this one.... @South_coast told me how.... but they have already posted!! 
  • jackieblack
    jackieblack Posts: 10,314 Forumite
    First Post Name Dropper Photogenic First Anniversary
    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
  • tacpot12
    tacpot12 Posts: 7,929 Forumite
    First Anniversary Name Dropper First Post
    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.
Meet your Ambassadors

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