Formula to calculate interest for partial year

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 E2 = Input day of year =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

HR

Use this in E2. You are subtracting 12/31/2013 - 12/30/2013 and getting 1 day. You need to subtract the input day minus 1/1 of that year. The DAYS function is a way to that without explicitly using a subtraction.

Formula:

Please Login or Register  to view this content.

By the way, the formula in G2 includes division by 1, which doesn't do anything. That formula is equivalent to

Formula:

Please Login or Register  to view this content.

Also, your formulas D21 are strictly for simple interest. If you need to use compounded interest, which is what most loans and investments use, you need different formulas.

Maybe DAYS is not available in your version. It's simple enough to do it with subtraction.

Formula:

Please Login or Register  to view this content.

(What you have described is annual compounding, which is fine if that's the way it's supposed to be calculated. I mentioned it only because continuous compounding is pretty common.)