Creating a formula for accumulating interest

Hi!
See, I've got quite a few deposits here and there. And I'd like to create a formula to keep track of the interest that accumulates as time passes. I figured it could somehow be done using DATE() and TODAY() functions.
Anyway. Say I deposit £100.000 for a year with a 5% yearly interest rate. Interest calculations are based on a 360-day year and the actual number of days.
I guess calculating interest would be easy enough using 100000*(1 + 0.05/360*365) - 100000 = £5069.44. However, my contracts actually state interest is calculated monthly, hence the end interest would be higher. Using FV formula doesn't work either, as the formula FV = 100000*(1+0,05/12)^12 uses a 365 day year.
So, for the smart people here, first of all, how would I calculate total acculumating interest correctly? And is there some way I could tie these calculations to automatically display interest earned by a certain date (something like the value updating itself daily)?

Do you want to keep track of interest earned on a single deposit or a series of deposits

I get an amount of £5,188.91 when interest is compounded per month with 365/360 day count basis

Using this Excel FV function, I have the following amounts for earned interest using 365/360 day count and various compounding periods

1. Annual
   =tadFV(5%, 1, 0, -100000, 1, 365/360, 365/360) - 100000
   £5,069.44
2. Semi-Annual
   =tadFV(5%, 1, 0, -100000, 1, 365/360*6/12, 365/360) - 100000
   £5,133.69
3. Quarterly
   =tadFV(5%, 1, 0, -100000, 1, 365/360*3/12, 365/360) - 100000
   £5,155.59
4. Monthly
   =tadFV(5%, 1, 0, -100000, 1, 365/360*1/12, 365/360) - 100000
   £5,188.91
5. Daily
   =tadFV(5%, 1, 0, -100000, 1, 365/360*1/365, 365/360) - 100000
   £5199.77
6. Continuous
   =tadFV(5%, 1, 0, -100000, 1, 0, 365/360) - 100000
   £5,200.14

Hey thanks! That's one very useful post you've made there. Cheers.

Wait, just one thing. What am I missing here: the tadFV function allows me to enter RATE, NPER, PMT, PV, TYPE and COMPOUNDING. You, however, seem to have entered more arguments.

Originally Posted by JacobA

Wait, just one thing. What am I missing here: the tadFV function allows me to enter RATE, NPER, PMT, PV, TYPE and COMPOUNDING. You, however, seem to have entered more arguments.

tadFV function that you have used is part of an older version of the financial functions library v1.0. The version of tadFV that I have illustrated in my example interest calculation is part of the new library v2.0 that is not released on the market yet. The upgrade is almost ready and will be available for download by Monday. You can pre-order it by making the purchase of the current version that will make you eligible for a 50% discount off the new version once it's released

Originally Posted by JacobA

Wait, just one thing. What am I missing here: the tadFV function allows me to enter RATE, NPER, PMT, PV, TYPE and COMPOUNDING. You, however, seem to have entered more arguments.

Here is the difference between old and newer tadFV (and other 4 Excel TVM functions of RATE, NPER, PV, and FV)

The older version of these functions accepts the values as you listed, which are same as those found in Excel's own time value of money functions with only an extra value of COMPOUNDING

For example

tadFV(RATE, NPER, PMT, PV, type, compounding)

is from the older version where compounding accepts a value of 0 or omitted for discrete compounding of interest and a value of 1 for continuous compounding of interest

And now the new TVM functions in v2.0 of the financial library add two new values as follows and changes the way values are treated for COMPOUNDING

tadFV(RATE, NPER, PMT, PV, type, compounding, period, distribution)

In the newer version of tadFV and other TVM functions compounding takes an unlimited variety of values for various types of compounding of interest such as

1 for annual or it may be entered as 12/12
1/2 for semi-annual
3/12 for quarterly
1/12 for monthly
1/365 for daily
and 0 for continuous

But these values for compounding are now intertwined with values for the next argument called PERIOD

Here PERIOD is used to specify the length of the individual time period and defaults to a value of 1 which may be construed differently based on the context it is used for such as

30/360 day count basis specifies a period,
365/365 day count basis specifies a period,
365/360 day count basis specifies a period,
366/360 day count basis specifies a period,

The next and the last argument called DISTRIBUTION specifies the concentration of cash flows. Federal Government mandates that it's departments and agencies use mid year discounting convention to analyze cost benefit of social, defense, environmental, health, labor and all other projects

Thus you can specify mid year discounting with a value of 1/2 or 0.5 for the last argument called DISTRIBUTION. It uses a default value of 1 for end of year discounting

I hope this will clear any confusion that may have been witnessed by you

You really are thorough, I'll give you that. Thanks for your time! Very informative indeed.