Formula for calculating partial year interest

[hmr2662]

Good morning,

I have developed a financial calculator that asks the user for the "input date" which is used to record balances as of the input date. My interest calculation for the first year is based on the current date compared to the input date. For example, if the user is keying in a current balance of 10,000 @ 10% interest, and the "statement date" or "input date" is 12/30/2013, and the current date is today, 2/4/2014, then it should calculate interest for the entire year of 2014. It is not doing that. It calculates $3 interest. But if input date is 6/30/2013 with current date of 2/4/2014, it seems to work OK. It calculates interest of $504 in that case. It appears to get messed up with the year transition between current date and input date. The formulas I have listed below appear to work fine except when the input date is 2013 for the year and the current date is 2014. The formula does not "see" that input date was last year. There must be a minor tweak to formula I am overlooking.

Cell C2 = Today's Date =TODAY()
Cell C3 = Input date (user keys in date in mm/dd/yyyy format)
Cell E2 = "translate input date to year format" =DATE(YEAR(C3),12,31)-C3
Cell E3 = Investment Rate
Cell G2 = yr 1 interest rate adjusted =(E3/1)*($E$2/365)
Cell C21 = Current Balance
Cell D21 = Interest Yr1 = C21*G2

Please help. I need the interest calculation to account for partial year accrual. Thanks

[Kaper]

You dont use in your calculations C2.
for instance:
12/30/2013

=DATE(YEAR(12/30/2013),12,31)-12/30/2013

= 1

Probably would be better seen if you would post a workbook. But this time - just correct your formulas.

[hmr2662]

Kaper,

As you stated, I need to submit the workbook as an attachment which I have done in modify inputs view so protection is off. I took the liberty of adding some date formulas in columns R2-V3 to then convert to a serial number. The other issue that we must consider is that regardless of the date for Today Date, year 1 should calculate interest for 12 months from the input date, I think. I have attached the spreadsheet and made some formula changes.

I think it will be easier to see once you have the workbook. This is a very complex workbook and proprietary as I am in final proofing phases and knew this formula was not correct.

Thanks

HR

[Kaper]

Hi, There was no need to post whole workbook. Just a piece which makes problems.
Anyway: Take into account that I know a bit about excel - not a lot on financial formulas.
General statement - the functions in the worbook work as you "told them" (there are some minor exceptions, like rounding, etc. but these are exceptions, not rules).

Let me put it that way:
In C3 you have some start date. (say 6/30/2013) so the first year (to be included in row 21) is 2013.
In this year there was $1000 for 10% , but it was "working" only from the end of June (184 days), so the interest shall be not 10% but some 5.0411%
If it is like that, then there was no real point to introduce todays date (it can be usefull to asses todays value of collected assets). So the original formulas were right.
If it anyway earns 10% (despite of late June start) then really no need to calculate our 5.0411%. Just =C21*G3 would do.

If it does not work that way (as mentioned - I am not financial analyst and our retirement/pension system is also quite different) then in my opinion you shall think first of the way it shall work (may be with the piece of paper and calculator) and then try to work out the formulas which follow your paper-and-pen algorithm.