Calculation of steady function

[ANJIBA]

([BOND_INCOME]![Amount])/([BONDS]![Amount])*(365/([BOND_INCOME]![Effective_Date]-[TRANCHE_MASTER]![Effective_Date]))

I have this formula and it is applied from January to March. The monthly bond income is constant (over the months ) and the bond amount is constant over those months. The second part of the formula is an attempt at annualizing the yield over the year. The TRANCHE_MASTER effective date is the date the funds used to purchase the bonds are borrowed. The BOND_INCOME effective date is the date is the date income is earned ( the last date ). This is what I get for the three months January, February and March. The yield should actually be a steady 5% considering that the income stream is constant. Your help will be appreciated.

Tranche_No	_BOND_INCOME_Amount	BONDS	Borrowing rate	Yield
0002	$833.33	$200,000.00	2.156%	1.69%
0002	$833.33	$200,000.00	2.156%	2.58%
0002	$833.33	$200,000.00	2.156%	4.91%

[ANJIBA]

More information, I suspect the grouping is contributing to my problems -

[Bob Fitz]

I don't really understand what you are doing, but could it be that you need to change the "GroupBy" in Yield column to "Expression".

[June7]

The Yield result looks like a running sum.

If the number of days between the two dates varies, then the calc result will vary. You don't show the date values used for each record.

[John_G]

I think the problem is the formula itself, and it is caused by the
([BOND_INCOME]![Effective_Date]-[TRANCHE_MASTER]![Effective_Date]) part.

With each passing month, the part of the expression
([BOND_INCOME]![Effective_Date]-[TRANCHE_MASTER]![Effective_Date]) gets larger (the date range is bigger)

Therefore the value of the whole expression for "yield" changes (gets smaller).

Can you post the original formula for "Yield" you used to develop your query? Something really is not right.

[ANJIBA]

So ... I tried changing the "groupby" to "Avg" and I get one line ( which is fine ) but it averages those three yields and ths comes to a yield of 3% which is wrong. It should be around 5%. When I use Sum ( which is a wrong concept anyway because the sum of yields is an incorrect concept ) I get 9%. Is there a way of attaching the DB here by the way?

[ANJIBA]

@June7,

So Bond income should be cummulative ( I don't know if this formula picks that up ) and as the income gets higher the factor ( second part of formula ) by which the raw yield ( Raw yield = Income / assets ) is multiplied gets smaller ( in December it should be 12 - 365 days / 365 days ). The second part of the formula is necessary.

Thanks.

[June7]

To provide db follow instructions at bottom of my post.

[ANJIBA]

Let me get the date values in there

[John_G]

Your calculation of the yield is incorrect. In this expression:

([BOND_INCOME]![Amount])/([BONDS]![Amount])*(365/([BOND_INCOME]![Effective_Date]-[TRANCHE_MASTER]![Effective_Date]))

The part in red is constant (833.33/200000) for each record, while the date range changes, so the resulting yield value changes. What you need is the red part to be the "Income to Date", i.e. a running sum - a query won't do that. You will need a simple report, I think.

John

[ANJIBA]

Tranche_No	FHLB_BOND_INCOME_Amount	FHLB_BONDS	Borrowing rate	Yield	Effective_Date
FHLB0002	$833.33	$200,000.00	2.156%	1.69%	3/31/2014
FHLB0002	$833.33	$200,000.00	2.156%	2.58%	2/28/2014
FHLB0002	$833.33	$200,000.00	2.156%	4.91%	1/31/2014

Ok .... so the problem here is that my income is not accumulating even though the factor is moving from 12 to 6 to 4 - over January, February and March, respectively. Novice here, any suggestions on adjusting my formula ?

[June7]

Running Sum in query is possible (http://support.microsoft.com/kb/290136) but not easy. Textbox in report has RunningSum property.

[ANJIBA]

Is the report simpler than creating a YTD field in my bond income table and then using that ?

[June7]

Probably. Did you review the referenced link?

[ANJIBA]

Just reviewed it. I think I can get that RunTot in there but I think it would have to be a separate query because I am not sure this expression can then be used in another expression in the same query ( Or i do not know how to ). Most of this detail is coming from a GL query that needs to be formated before uploading into the DB anyway ..so I can add this cummulative field .... but it would sure be great to not have to do that , will update y'all on what I did to make it work .. Thanks