Weighted average annualized interest rate and weighted average balance

I have a spreadsheet that I track daily balances of a fund and also the interest earnings on the fund.  

I'd like to have data that I can report the weighted average balance for various time frames (see attached, column F is balance for the day) and also what the weighted average daily rate is for a period (also or instead would like this to be annualized).  

I think our daily rates are truncated after 10 decimals as I don't get the exact same interest calculation using daily balance * daily rate (F*H).  GeFONSI-History--Experts-Exchange-.xlsx

Can someone help me do this best (efficiently that's low maintenance to update as it's updated each month). Thank you!
Kevin BucklandInvestment OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Kevin,

I'm still not clear by your requirements about what you are looking for and where do you want the formula and what you want the formula to do..can you help me understand what you are looking forward here..

Saurabh...
0
Katie PierceCommented:
Well, it looks like your Weighted balance % in column M is pretty good--why not just apply those weights to the daily balances and sum them up? (See column O).

You could apply the same concept to the daily rates.
Copy-of-GeFONSI-History--Experts-Exchang
0
Kevin BucklandInvestment OfficerAuthor Commented:
Saurabh,
I'm trying to arrive at a weighted average cash balance for various time frames, eg FY2015 / FY15 based on the daily balance in column F.  I'm also trying to arrive at a weighted average daily rate or weighted average annualized rate for the same time periods -- either based on the truncated daily rate in column H or a recalculated average daily rate based on the daily earnings in column I (based on the daily balance in column F).  

Katie,

I started doing that but began to second guess my logic because it seemed my approach to weighted average cash balance was based on $ value only and not the number of days that the balance was there.  I'm still not sure I'm doing it right, can you confirm that $18,989,962 should be the weighted average balance for FY15?  

I tried to apply the same concept to calculate  a weighted average rate for FY15 (0.1838%, see cell P7) it doesn't seem reasonable to me when I look at the total interest earnings for FY15 ($64,274 -- see cell I7) relative to balance. When I divide total interest earnings  by the $18,989,962 weighted average balance I calculated, I come up with 0.33847% which seems reasonable but makes me question the other rate.  

Thank you!
GeFONSI-History--FU-34045----Experts-Exc
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Saurabh Singh TeotiaCommented:
Their you go..I believe this is what you are looking for..

Saurabh...
GeFONSI-History--FU-34045.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for K8E314's comment #a40913646
Assisted answer: 250 points for saurabh726's comment #a40914238

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Saurabh Singh TeotiaCommented:
Martin,

If you read the Kevin Comment..Katie Solution is not a working one..The one which i posted does what you are looking for and if you check the figures in the comment which he mentioned and in the file both of them matches exactly. So from a solution stand point of view the correct solution is mine which i posted after his comment.. :-)

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.