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!
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!
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
You could apply the same concept to the daily rates.
Copy-of-GeFONSI-History--Experts-Exchang
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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...
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...
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...