Daily interest calculation

Gem2889

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

South_coast

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

Gem2889

Thank you so much!!

getmore4less

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

I thought, I know the answer to this one.... @South_coast told me how.... but they have already posted!! 

jackieblack

South_coast said:

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.

tacpot12

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)