We'd like to remind Forumites to please avoid political debate on the Forum. This is to keep it a safe and useful space for MoneySaving discussions. Threads that are - or become - political in nature may be removed in line with the Forum’s rules. Thank you for your understanding.

Savings Calculations - Excel

Anyone have any formulas to work out exactly how much interest you should be getting on your savings?

I've a couple of accounts that keep dropping and some that say they have dropped, but then the bank confirm not dropped yet and I wanted a way to put in my balance into a formula or something and then work out how much interest I should get - as I really don't trust the banks to be accurate in their calculations.

Comments

  • SnowMan
    SnowMan Posts: 3,531 Forumite
    Part of the Furniture 1,000 Posts Name Dropper Photogenic
    SparciaM wrote: »
    Anyone have any formulas to work out exactly how much interest you should be getting on your savings?

    I've a couple of accounts that keep dropping and some that say they have dropped, but then the bank confirm not dropped yet and I wanted a way to put in my balance into a formula or something and then work out how much interest I should get - as I really don't trust the banks to be accurate in their calculations.


    To calculate how much interest on an amount from one date to another date (in a spreadsheet):

    Cell
    A1 = amount
    A2 = interest rate (format as percentage), enter the AER here rather than the gross rate as this is the true annual rate (usually)
    A3 = date interest from
    A4 = date interest to

    Then gross interest is calculated by the formula

    =A1*(1+A2)^((A4-A3)/365.25)-A1

    And net interest is 0.8 times this figure

    The 365.25 is to allow for leap years (which is more than close enough)

    When you have typed in the formula try it with some obvious dates exactly a year apart and say an amount of £1,000 and interest rate of 10%. Is gross interest £100 (to be precise within some pennies of £100 due to the leap year approximation)?


    This is the easiest formula to use and can be widely applied. In practice banks work out interest on a daily basis but no need to worry about that complication as the above formula is arguably the "correct" formula and should come very close.

    Does that make sense, and can you see how that can be extended where you have a table of amounts and dates and interest rate changes?
    I came, I saw, I melted
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 348.3K Banking & Borrowing
  • 252.1K Reduce Debt & Boost Income
  • 452.4K Spending & Discounts
  • 240.8K Work, Benefits & Business
  • 617.1K Mortgages, Homes & Bills
  • 175.6K Life & Family
  • 254K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16K Discuss & Feedback
  • 15.1K Coronavirus Support Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.