Cumulative interest calculator

Hello new to the forum and first time poster

I am trying to build a calculator where I can work out how much interest is charged on a loan amount where interest is charged annually but could be worked out daily.

The interest would be charged for example

£10,000 loan after 1 year at 5%

then interest would be charged on this new amount

£10,500 after another year £11,025

but someone could come in at anytime to say they want to know what the interest to date would be and it is this part I am struggling with

does anyone know of a formula or function that can work this out for me?

I should point out at this point there are no repayments and the term of the loan could be anything

I'm not sure if this is what you want but you can see. Suppose the annual interest rate is 5% and the balance at the beginning of the year was £10,500. Also, suppose that you want to know the balance n days after the beginning of the year.

The balance after n days would be:

+10500*(1.05)^(n/365)

assuming exactly 365 days in the year. To allow for leap years, you could replace "365" with "365.25". But some people go so far as to assume 30-day months and 360-day years for accounting software for the sake of making the months even. In all likelihood, the above formula may be accurate enough for your purposes.

Steve

Thanks S_Andrews for your reply

How would this work for varying interest rates, loan amounts and loan periods?

For example one person could borrow 10000 then come back after 3 years 100 days at 5% interest but someone else could borrow 50000 then ask for the balance after 5 years 10 days at 7.45% interest

Not sure if this is possible with so many variants to have in just 1 cell

Newdoverman I don't see how amortisation tables would help in this situation as the interest is charged annually but to see the precise cost today it would need to work out the daily interest and like I have said above after 1 year it is the initial loan amount plus 1 years interest that the new interest figure will be calculated from

A one-cell solution will work if it is OK to be 99.99% accurate. If it is a legal-binding estimate that is needed, however, you would either need to revise my approach to get the exact number of years and days that have elapsed or go to some other source. For complete accuracy, you even have to know the bank's standards of calculation (i.e. in a leap year, is less interest charged per day so that the total over 366 days equates to what is normally charged over 365 days?) Aside from this, take a look at the following.

Try doing the following:

Assign D10 the API (e.g. 5.00%).
Assign D11 the original balance of the loan (e.g. 10,500).
Assign D12 the date the loan was made (e.g. 7/1/11).
Assign D13 the date of the balance inquiry (e.g. 4/23/14).
Assign D16 the balance formula : "+$D$11*(1+$D$10)^(($D$13-$D$12)/365.25)"

Do a few examples by hand and then compare them to the results of the above approach. I hope that this helps.

Steve

Sorry I haven't replied for a few days. I have tried the spreadsheet provided by s_andrews. It works for some but anything over 2 years is way out but I thank you for your help.

I'm trying to learn more about excel so can you explain the $ signs in your formula as I'm not sure what they do.

I will have a look at the amortization tables but as I said before not sure how this will work.

Thanks for all your help

Hi Scottie,

It would help if you provided a few examples of those that are off. Tell me the input data, the results of my formula, and what you would expect the results to be. As I said before, there are ways to tweak it with different assumptions.

As for the $ signs, they are merely indicators of relativity of the cells. If you removed all of those $ signs and copied cell D16 with the formula to, say, cell F16, the formula would become "+F11*(1+F10)^((F13-F12)/365.25)". That is because the reference to a cell would then be relative to where the formula was. But placing the $ signs in the formula keeps it fixed on the cells originally pointed to. Just as an exercise, focus on a forrmula and place the cursor over one of the references to a cell. Then press F4 several times. You will see the reference change the $ signs to the four possible combinations of relative reference.

Steve